We identified how to resolve this issue, and I wanted to post the method we implemented.

I welcome any suggestions or ideas that may improve our logic so that we may gain further understanding of the entire process.

I’ll give a high-level explanation of what we did, and then I’ll include the method we used.

  1. Add parent-child relationship properties to the 2 class definitions.

This will leave the parent class untouched, but it will immediately clear out any records that exist in the child class from displaying in a SQL query window.

 

Start of UpdateRelationship() method:

  1. Loop through the class definition to identify all the properties that exist including their sequence in the storage definition.
  2. Create a new child object
  3. Loop through the data global and populate the child properties from data within this global entry.
  4. Open a parent object and set the relationship by calling the Insert() function from the parent passing in the child object.
  5. Save the parent object.

End of UpdateRelationship() method.

 

  1. Run the UpdateRelationship() method manually from the Output panel within Studio.

There are a few more checks to verify the objects are valid, and the child has no existing parent object.

We are also deleting the old Index Global entry, as well as logging various information to a global.

The method we used to populate the relationship is outlined below.

Hopefully this will help if you are facing the same issue we were, or at least give you some additional ideas to try.

ClassMethod UpdateRelationshipOrders() As %Status

{

               

                #define GW(%w)             Set ^UpdateRelationship($$$CurrentMethod,$I(^UpdateRelationship($$$CurrentMethod)),%w)=$ZDT($H,3,1)

                Try{

                                s tempcount=0

                                s tSC=1

                                s tSuccessfulRows =0,tFailedRows = 0, tParentExist=0, tNoParent=0

                                s tKey="", tPropertyName="", tPropertyListLocation=""

                                s tDel=",", tDelSub=":"

                               

                                s tStorageName="Default"

                                //Pull oddCOM storage order information

                                s tStorageNameKey="", tStorage=""

                                //Grab the First Storage Definition Name (Usually 'Default')

                                //Grab DefaultData identifier

                                set tStorageNameKey = $O(^oddCOM($classname(),$$$cCLASSstorage,tStorageNameKey))

                                set:tStorageNameKey'="" tStorageName = tStorageNameKey

                                set tStorage = ^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdatalocation)

                                set tStorageDefaultData = ^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdefaultdata)

                               

                                //Create list of Property names and their correlated storage subscript location

                                For {

                                                set tKey = $O(^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdata,tStorageDefaultData,$$$cSDEFDATAvalue,tKey))

                                                Quit:tKey=""

                                                set tPropertyName = ^oddCOM($classname(),$$$cCLASSstorage,tStorageName,$$$cSDEFdata,tStorageDefaultData,$$$cSDEFDATAvalue,tKey,$$$cSDEFDATAVALvalue)

                                               

                                                //Add to Delimited List

                                                set tPropertyListLocation = $S(tPropertyListLocation="":tPropertyName_tDelSub_tKey,1:tPropertyListLocation_tDel_tPropertyName_tDelSub_tKey)

                                }

                                //Debug

                                 $$$GW(tPropertyListLocation)

                                //w $L(tPropertyListLocation,tDel)_" <-- How many items in this list",!

                               

                                s tKey="",tValue="",j=0

                                For {

                                                                //Quit:tempcount=5

                                                s tKey= $O(@tStorage@(tKey),1,tValue)

                                                Quit:tKey=""

                                                s j=j+1

                                                //W i_": "_tValue

                                                //Confirm this Global is not already a Child of another Parent

                                                s tChildKey="", tChildValue=""

                                                s tChildValue = $O(@tStorage@(tKey,tChildKey))

                                                If tChildValue'=""

                                                {

                                                                s tParentExist=tParentExist+1

                                                                Continue            

                                                }

                                                s tData = $LISTTOSTRING(tValue)

                                                //w tData,!

                                               

                                               

                                                s tOBJ = $CLASSMETHOD($$$CurrentClass,"%New")

                                                //Loops through the List of Properties of the Object

                                                For i=1:1:$L(tPropertyListLocation,tDel) {

                                                                s tFieldProperty = $P(tPropertyListLocation,tDel,i)

                                                                s tProperty = $P(tFieldProperty,tDelSub,1)

                                                                s tLocation = $P(tFieldProperty,tDelSub,2)

                                                                                //first %%CLASSNAME we can ignore

                                                                                Continue:tLocation=1

                                                                s tLocationValue = $P(tData,tDel,tLocation)

                                                                //w tProperty_" = "_tLocationValue_" "_tLocation,!

                                                               

                                                                //Set the Objects Property from the stored Global

                                                                s $PROPERTY(tOBJ,tProperty)=tLocationValue

                                               

                                                }

                               

                                                //Debug

                                                //zw tOBJ

                               

                               

                                //Open Parent

                                set tParentOBJ = ##class(Package.Class for Parent).%OpenId(tOBJ.ParentID)

                                $$$GW(j_": "_$ISOBJECT(tParentOBJ)_": "_tOBJ.PatientRecordID_" <-- "_tKey)

                                If '$ISOBJECT(tParentOBJ)

                                {

                                                s tNoParent=tNoParent+1

                                                Continue            

                                }

                                S tCheckID=""

                                &sql(SELECT ID into :tCheckID FROM Schema_Table for Child WHERE PatientRecordID = :tOBJ.PatientRecordID

                                AND SiteCode = :tOBJ.SiteCode

                                AND OrderID = :tOBJ.OrderID)

                                $$$GW(tCheckID_" "_SQLCODE)

                                If '(tCheckID="")

                                {

                                                //W "Exists already: "_tCheckID,!

                                                //Kill Existing Data Global, it's already in the parent child relationship

                                                CONTINUE

                                }

               

                                //Follow Parent-Child process, Insert Child into Parent object and save parent

                                Do tParentOBJ.[Relationship Property].Insert(tOBJ)

                                //W $D(^Global for Child Index("PatOrdersIdx"," "_tOBJ.PatientRecordID," "_tOBJ.SiteCode," "_tOBJ.OrderID))

                                //W " <--Data Results",!

                                Kill ^Global for Child Index("PatOrdersIdx"," "_tOBJ.PatientRecordID," "_tOBJ.SiteCode," "_tOBJ.OrderID)

                                s tSC = tParentOBJ.%Save()

                                s tempcount=tempcount+1

                                If tSC {

                                                s tSuccessfulRows=tSuccessfulRows+1  

                                } else {

                                                                $$$GW("Failed on Patient Record ID of ID: "_tOBJ.PatientRecordID_"|"_tOBJ.SiteCode_"|"_tOBJ.OrderID_" | "_tSC)

                                                                s tFailedRows=tFailedRows+1                   

                                               

                                }

                               

                                }

                }

                Catch e

                {

                                s tSC = e.AsStatus()

                                W "Not Right: "_tSC,!    

                }

                W "Encounter Completed",!

                                W "Success: "_tSuccessfulRows_" | Failed: "_tFailedRows_" | ParentExist: "_tParentExist_" | NoParent: "_tNoParent,!

                                $$$GW("Success: "_tSuccessfulRows_" | Failed: "_tFailedRows_" | ParentExist: "_tParentExist_" | NoParent: "_tNoParent)

                               

                               

                                Quit tSC

}

Hello Vitaliy,

Thank you for the advice you provided. You're explanation is pretty much where we are at. After we added the relationships the data is no longer visible in the SQL table. We had previously discussed the possibility of cloning the db prior to adding the relationships, and using it to import the data back in. We still may go that route, but we are trying to get this working another way first.

I mentioned that the data for this class is still visible in the Data Global. So, we're trying to capture the data from the global, create a new object, copy the data into the class properties, set the relationship, and then save the object.

This is almost all working now, but one part is still causing problems. We are looping through a list of the class properties to capture the name and sequence number. The idea to do this came from this article, 

https://community.intersystems.com/post/listing-all-properties-class-and...

We are doing this so that we know where to store the data from the global in the correct class property. The problem is that for a couple of the properties the Sequence Number listed is not correct. One option is to hard-code the set commands to save the data correctly. However, we will be using this code on several different environments and it needs to be dynamic so that it will work for each one. As soon as we identify how to return the correct sequence number we should be good.

Thanks again for your help!

Hello Pravin,

Thank you for posting this article. It was easy to read, and I think it will be very helpful.

I have a question that I'm wondering if you would be able to help me with. I am trying to add relationships between tables with existing data. The relationships are required. Once we add them, we are no longer able to see data in the child tables which I believe is to be expected. I was hoping your suggestion to use %NOINDEX in the Select statement would display the data so we could then add the required relationships. Unfortunately, %NOINDEX doesn't seem to be working in this instance.

I was just wondering if you had any other suggestions on how to return data that will not display even though it is present?

Thank you!