How to find the Primary & Foreign Keys & How to access tables with String as Parameter
Trackcare 2021.2
Please may I know how to access SQL parameter with string as datatype with NO reference to SQL Table and SQL Column.
Hi Experts,
I have these two clarifications to make:
- I am struggling to understand the process and method of accessing the SQL table with no reference to SQL parameter or Table but string
This is what I tried: Tired to create ERD diagrams and try to extract Primary and Foreign keys to associate, but I cannot in this case
I would like to do something like this
Select * FROM
SQLUser.SS_User SSU
LEFT JOIN SQLUser.CT_Loc CTL ON CTL.CTLOC_Desc = ?? < Cannot find Info here>
WHERE SSUSR_Initials = 'xxxx"
Example:
- Similarly CT_Loc table has
CTLOC_Code
CT_LOC_Desc,
CT_LOC_Hospital_DR->Hosp_Desc
etc – This is a working code
Same as
SELECT * from SQLUser.CT_Hospital CTH
LEFT JOIN SQLUser.PAC_FacilityType PFT ON CTH.HOSP_Desc = PFT.FACIL_Desc
But please may I know how am I supposed to know when table’s primary key / foreign key do not match and doesn’t show any relationship in ERD as below.
But when I extracted the ERD diagram of CT_Loc and CT_ Hospital they do not have any relationship and when I used the rowID, I got wrong answers.
Any detailed explanation will help, with reference to documentation will be appreciated.
We may need more clarity to answer the specific question you have but here are some answers to things I could pull from your post:
- The reason relationships are not showing in ERD is because adding an object reference from one class to another doesn't generate a ForeignKey (only relationships do this automatically) thus no relationship is defined in the generated ERD. If a FK is required then it should be defined manually.
- The primary keys for the tables in the ERD diagram are listed in your posted image. The fields listed there are the ones that correspond to the primary key of each of the tables displayed. If you're looking for a programmatically way to see the primary (assuming you don't have access to the code) then you could check information_schema.key_column_usage for constraint_type = "PRIMARY KEY". You may need to look deeper depending on whether table correspond to a global mapped (legacy) table or a "pure" SQL table.
- As for the different results, it may be you're passing the wrong value to the query (eg. ID for one table being used to query another) or indices are out of sync or corrupted (rebuilding indices should fix this).
Hi @Warlin Garcia
Firstly Thank you for the response.
Please may I know what more details you need for both 1 and 2 questions above.
1. For question 1 is most common scenario in Trackcare, example "Other logon locations" highlighted has no SQL table or SQL table, it is a String datatype (Link). Such instances are common and if you can help me how to approach towards solution to this specific issue and any reference examples, that would be so useful. Also let me know what specific information you need to get help.
2. The solution is known "CT_Hospital" is linked to "PAC_Hospital" and I am keen to know how as user, should know if I am working on different table, ERD did show the relationship between two tables automatically but not for other tables, I know are related.
3. How do we know how System Parameters are related and how to know from the tables Like "ParamRef"
Hi
I started writing a response to your questions yesterday afternoon and I wrote a lengthy explanation using LabTrak as the basis for my response. I wrote about 400 lines of explanation and then my laptop rebooted to install some updates and I lost everything I wrote. So I am going to answer but I am going to abbreviate the explanation as I don't have enough time to rewrite the whole document I wrote yesterday.
1) Different database Storage Models
2) Links between Tables
3) Relationships
There are two types of relationships most commonly used in Ensemble/IRIS Classes/Tables: Parent-Child and One-Many. Many-Many relationships can be defined. Refer to the documentation on Relationships to see how Many-Many relationships are defined. I can't think of any examples of Many-Many relationships in LabTrak so instead, I will just focus on the two main relationship Types.
Parent-Child Relationships
If you look at the TEPI global in LabTrak which is the global which stores Episodes. You will see something like this:
^TEPI({Episode_Number})="{Episode Fields}"
^TEPI({Episode_Number},1,{TestSetCode})="{Test_Set_Fields}"
^TEPI({Episode_Number},1,{Test_Set_Code},"DATA",{Test_Item_Code})="{Test_Item_Fields}"
for example:
^TEPI("AA00000401")=""SALM\NIGEL\M\44453\\\S2\61839\646\61838\480\61839\0DR\\R\\\1039444\\41C028W000\\\\\40\\\F\\fred.smith\\61839\646\N\\AA\,690425\\\\\\N\0\\\\N\H\38768\\\\41C028W000\\\\\\\\\\\\\\\Z03.9\52ZETH000003" - This is the Episode Record
^TEPI("AA00000401",1,"H065",1)="61842\594\jane.doe\61842\597\jane.doe\\N\\\\\\\\\\\\\61839\646\\\N\\\Y\\\A\~~~\ \N\\fred.smith\\\\\\N\\61838\28800\\\1\\\\\Y\\\\61839\38760\\\N\\\\\\\N\61843\8122\\\P\Z03.9\" - This is a Test Set Record within the Episode
^TEPI("AA00000401",1,"H065",1,"DATA","H0440")="4.14\\\\\AAH02\\\\\\\\\" - This is a Test Item Record within the Test Set.
As you can see this global is a Multidimensional Array. It has many subscript levels. The ability to create Arrays like this is one of the features that differentiates Cache/Ensemble/IRIS from any other Database Technology. It is one of the reasons why Ensemble/IRIS creates databases that are, on average, half the size of other database technologies on a Like for Like basis. Partly because the data records do not use fixed-length fields. By using Delimiters or $List we do not need to pad out field values with leading '0s or trailing spaces or NULL characters. Secondly, by storing the Test Sets within the Episode men's that when we access the Episod/IRIS loads the data page that contains that episode into memory it brings with it the Test Sets and Test Items. Where you walk through the Test Sets the Test Set Records are already in memory. We haven't had to go to another data structure in the Database to fetch the Test Set Records from a Test Set Table as you would need to do in other Relational Database Technologies.
If you delete an Episode the Test Sets and Test Items are deleted as well. You do not need to delete the Test Items first, then delete the Test Sets and then, finally, delete the Episode.
Nor do you need to maintain Indices to link Test Items to Test Sets or Test Sets to Episode.
In LabTrak the property in the Parent Table that references the Child rows in the Chile Table is prefixed with the word "Child" and the corresponding property in the Child Table that points to the row in the Parent Table is suffixed with the word "ParRef"
One-Many Relationships
In the One to Many relationship, there will be two tables, a Row in the "One" table is linked to Many rows in the "Many" table. From an Objects point of view, there is a Property in the "One" class that points to the related rows in the "Many" table. In LabTrak the property name is prefixed with the word "child". There is a corresponding property in the "Many" Table and that property name is suffixed with the word "ParRef".
Your Question
When I read your question and I looked at the Schema Diagram I was a bit confused as there is no SQLUser.CT_Loc table in LabTrak. There is however a table called SQLUser.CT_UserLocation.
If you use the Management Portal -> System Explorer -> SQL in the LabTrak namespace and you select the SQLUser schema in the Schema drop-down list on the left of the SQL form. You will be presented with a list of all Tables in the SQLUser schema. If you scroll down and find the table "CT_UserLocation" and you drag it into the "Execute SQL" text area on the form it will display the following SQL Statement:
SELECT
CTUSL_RowId, CTUSL_Code, CTUSL_Desc, CTUSL_Destination_DR, CTUSL_UniqueSite, CTUSL_MoveDirectory, CTUSL_AccreditationNumber, CTUSL_WebReportFooter, CTUSL_DisplaySequence, CTUSL_RegionCode, CTUSL_Address1, CTUSL_Address2, CTUSL_Address3_Suburb, CTUSL_Address4_State_DR, CTUSL_Address5_PostCode, CTUSL_Phone, CTUSL_Fax, CTUSL_email, CTUSL_StructuredCode, CTUSL_StructuredCodeLength, CTUSL_StructuredCodeLevel, CTUSL_Address, CTUSL_DocCourierRun_DR, CTUSL_LocCourierRun_DR, CTUSL_ActiveFlag, CTUSL_DefaultNID, CTUSL_UnixMoveDirectory, CTUSL_PFforBatchEntry
FROM SQLUser.CT_UserLocation
Note the Columns CTUSL_Address4_State_DR, CTUSL_DocCourierRun_DR, CTUSL_LocCourierRun_DR. These are all Designative References to rows in other Tables.
The EP_VisitTestSet table contains the following Columns:
SELECT
VISTS_ParRef, VISTS_RowId, VISTS_TestSetCounter, VISTS_TestSet_DR, VISTS_DateOfEntry, VISTS_TimeOfEntry, VISTS_UserEntered_DR, VISTS_DateOfAuthorisation, VISTS_TimeOfAuthorisation, VISTS_UserAuthorised_DR, VISTS_PathologistID_DR, VISTS_ExcludeFromCCR, VISTS_Rule3Exempt_Sequence, VISTS_Priority_DR, VISTS_Rule3Exempt_Max, VISTS_TherapeutDosage, VISTS_TimeOfDosage, VISTS_24HUVolume, VISTS_24HUTimePeriod, VISTS_BB_TransfEvents_DR, VISTS_Rule3Exempt_Date, VISTS_DateOfPathologistAtt, VISTS_TimeOfPathologistAtt, VISTS_StaffNotes, VISTS_DateOfCreation, VISTS_TimeOfCreation, VISTS_StandardLettersChecked, VISTS_BB_DateRequired, VISTS_ExcludeFromPatientMean, VISTS_UserSite_DR, VISTS_Machine, VISTS_Printed, VISTS_SuperSet_DR, VISTS_StatusResult, VISTS_DFT_TimeOfFirstCollection, VISTS_HISTO_Extra, VISTS_HISTO_BillingItem, VISTS_SupressBilling, VISTS_HospitalRefNumber, VISTS_UserCreated_DR, VISTS_MoveToReferralLab_DR, VISTS_MoveToUserSite_DR, VISTS_DFT_DR, VISTS_DFT_Position, VISTS_DFT_DateOfFirstCollection, VISTS_StatusEntry, VISTS_Confidential, VISTS_SpecimenType_DR, VISTS_SpecimenNo, VISTS_DateOfCollection, VISTS_TimeOfCollection, VISTS_CollectedBy_DR, VISTS_DisplaySequence, VISTS_SupressReason, VISTS_UserSupress_DR, VISTS_DateOfSupressBilling, VISTS_Rule3Exempt_Comments, VISTS_AnatomicalSite_DR, VISTS_Reason_DR, VISTS_DateOfReason, VISTS_TimeOfReason, VISTS_UserReason_DR, VISTS_DateOfReceive, VISTS_TimeOfReceive, VISTS_PaymentCode_DR, VISTS_SpecimenGroup_DR, VISTS_Document_DR, VISTS_BB_Neonatal, VISTS_RR_Date, VISTS_RR_Time, VISTS_RR_User_DR, VISTS_RV_Date, VISTS_RV_Time, VISTS_RV_User_DR, VISTS_BB_DoNotFile, VISTS_DateOfLastChange, VISTS_TimeOfLastChange, VISTS_UserOfLastChange_DR, VISTS_DateOfSentSTM, VISTS_TimeOfSentSTM, VISTS_DateOfReceivedSTM, VISTS_TimeOfReceivedSTM, VISTS_MovementStatus, VISTS_AddedByAction, VISTS_PricingStatus, VISTS_ICD10List, VISTS_LongTerm, VISTS_RequestBy_DR, VISTS_LongTermReason, VISTS_PairedSeraQueue, VISTS_DoctorDR, VISTS_BB_TimeRequired, VISTS_BB_Tags, VISTS_DTOfResultChange, VISTS_DateOfFirstAuthorisation, VISTS_TimeOfFirstAuthorisation, VISTS_AnatomicalSiteFT, VISTS_ReasonReportedTo, VISTS_ReasonTelephone, VISTS_ReasonObservations, VISTS_ManualPCEntry, VISTS_ReasonClearResults, VISTS_ReasonClearSN, VISTS_ReasonResultsNR
FROM SQLUser.EP_VisitTestSet
If I add a WHERE Clause: WHERE VISTS_ParRef %STARTSWITH 'AA' it will return records from the site that "AA"
If you run this query you will see rows of data that look like this
The ParRef is the Episode Number. The RowId for the Test Set is AA00000005||H065||1, {Episode}||{TestSetCode}||{TestSetCounter}.
I created an ODBC connection to a LabTrak Namespace on our UAT server. I unspecified the Server IP address, the Super Server Port Number, The Namespace and my Credentials. I tested the connection which was successful. Te ODBC connection can be used by Excel and other reporting tools to create Reports and Charts.
I then use an application called DBBeaver (available on the Open Exchange site) to create ERD diagrams of the LabTrak classes. DBeaver uses the InterSystems JDBC driver and you set up the connection in DBeaver itself.
I
@Nigel.Salm5021
Thank you very much. It is unfortunate that I lost the precious detailed explanation and I am greatly appreciative and thankful for these details. This information should be really useful
So as I apply your knowledge to
Question 1: From SS_User Table, As I need to fetch the details "OtherUserLoc" which has no SQL table or SQL column defined as extracted from the Trackcare - > Component - >Items
1. There is no indication prefix, postfix about the storage type
2. As "OtherUserLoc" is not a SQL table/column, I am not able to establish any relationship. So please can you help me, how can I know if "OtherUserLoc" data is in ParamRef?
For example; In the CT_Loc table, there is a no reference to ParamRef but when writing query, Join is made between CT_loc & ParamRef on CT_loc.code = ParamRef.code (No Idea, how we can figure it out, that will help me to extract information about other tables).
2. Yes, I a using Dbeaver, as per the screenshot above, Dbeaver is showing a relationship between CT_Loc and CT_LocPType but not PAC_FacilityType and other tables? Can you help me?
Hi
I have had a quick look at the SS_User Table in L2016. The problem with the SS_Useer table is that it tends to be customised to the needs of the customer and so in my version I have no Column "OtherUserLoc". I checked the class to see if it exists in the class definition and is not projected to SQL but it is not in the class either.
The SS_User table is not a child table. It does have some child Relationships. The Episode and Test Set Tables will reference the SS_User table for the user who registers the Episode and the Laboratory Technician who performs the Tests. They will point to the SS_User Table like this:
Let me investigate and I will come back to you. As a matter of Interest what exactly do you mean by TrakCare - Components - Items?
Do you mean Episode -> TestSet -> TestItem?
Nigel
@Nigel.Salm5021
Thank you very much and much appreciated. I will wait and if not specific to SS_User, is there a standard check list approach to take when referring to tables with data type String. a link in the code table, where it gets bold, enabled only when table data is filled in.
Note: From existing code, CT_Loc joined with "websys.StandardTypeItem on( ParRef = Locationtype)
So I checked the ParRef, they are many and there are many types of websys tables some tables some not tables. but unsure, when to refer to what ? if that is the linkage to be done.
If I know the standard approach, It would be easy and if that is documented with different types of these
User epr web websys lab Custom Report. Is there a document which defines, what these are and how to use them to link what code table, that would I think help.
Now how I get these relationships from the Tackcare application , I do not have access to actual code, tables etc. I only have backend access and I have to pull reports, table relationships etc. How I am doing now is is via the "Components" (That should also answer your question).
attached picture would give complete details.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue