Hi, My client requirement is to insert and update rows from staging record to a transaction records PS_LOCATION_TBL. I am getting issues when a row is already exists in a record and I CI need to update a row.
For example: With Location ID 001 there are two rows in location table with effective dates 01/01/1900 and 01/01/2001. There is a row in a staging record with effdt 01/01/1900 that need to be updated.

Below is a snippet of a code and I am getting issue where I am comparing location collection effective date with the date in a staging record to tell CI only that row need to be updated (If &oLocationTblCollection.EFFDT = &currLocEffdt Then).

If I do not enter above IF statement then I get an error Instance must exist to set property {LOCATION.EFFDT} as it's trying to update 01/01/2001 and it's not in staging record. If I enter If &oLocationTblCollection.EFFDT = &currLocEffdt then I get First operand is .NULL error because collection has not been loaded. But it's important to compare collection effective date with staging effective date to update the row correctly without errors.

rem ***** Set the Component Interface Mode *****;

&oLocationTable.InteractiveMode = True;
&oLocationTable.GetHistoryItems = True;
&oLocationTable.EditHistoryItems = True;

&getsetid = &REC.SETID.Value;
&getlocation = &REC.LOCATION.Value;
&geteffdt = &REC.EFFDT.Value;
&geteffstatus = &REC.EFF_STATUS.Value;

&fileLog.WriteLine(" ");
&fileLog.WriteLine("Data being processed " | "Location ID = " | &REC.LOCATION.Value | " , EffDt = " | &REC.EFFDT.Value | " Effective status = " | &REC.EFF_STATUS.Value);

SQLExec("SELECT DISTINCT D.LOCATION FROM PS_LOCATION_TBL D WHERE D.SETID = 'ARSHA' AND D.LOCATION = :1", &REC.LOCATION.Value, &currLocID);

rem ***** Set Component Interface Get/Create Keys *****;
If All(&currLocID) Then
REM DEPT EXISTS FOR THE EFFDT;
SQLExec("SELECT D.EFFDT FROM PS_LOCATION_TBL D WHERE D.SETID = 'ARSHA' AND D.LOCATION = :1 AND D.EFFDT = :2", &REC.LOCATION.Value, &REC.EFFDT.Value, &currLocEffdt);

If All(&currLocEffdt) Then

REM DEPT AND EFFDT ROW EXISTS FOR THE DEPT BEING PROCESSESD;
REM UPDATE THE CURRENT ROW;
&fileLog.WriteLine("Update existing Location row with already existing effective dated row.");

&oLocationTable.SETID = &REC.SETID.Value;
&oLocationTable.LOCATION = &REC.LOCATION.Value;
&oLocationTable.EFFDT = &currLocEffdt;
&recType = "U";
Else

REM CREATE NEW EFFECTIVE DATED ROW;
&fileLog.WriteLine("Create new effective dated row for already existing Location.");
&oLocationTable.SETID = &REC.SETID.Value;
&oLocationTable.LOCATION = &REC.LOCATION.Value;
&recType = "I";
End-If;

rem ***** Execute Get *****;
If Not &oLocationTable.Get() Then
rem ***** No rows exist for the specified keys.*****;
&fileLog.WriteLine("Unable to get the location row.");
&cErrmsg = MsgGetText(21005, 3, "Unable to get the location row.");
errorHandler();
throw CreateException(0, 0, "Get failed");
End-If;
Else

&oLocationTable.SETID = &REC.SETID.Value;
&oLocationTable.LOCATION = &REC.LOCATION.Value;

rem ***** Execute Create ******;
&fileLog.WriteLine("Create new row with new location and new effective date.");
&recType = "N";

If Not &oLocationTable.Create() Then;
rem ***** Unable to Create Component Interface for the Add keys provided. *****;
errorHandler();
throw CreateException(0, 0, "Create failed");
End-If;
End-If;

rem ***** Begin: Get/Set Component Interface Properties *****;
rem ***** Set/Get LOCATION_TBL Collection Field Properties -- Parent: PS_ROOT Collection *****;

&oLocationTblCollection = &oLocationTable.LOCATION_TBL;

Local integer &i19;
&fileLog.WriteLine("&oLocationTblCollection.Count = " | &oLocationTblCollection.Count);

For &i19 = 1 To &oLocationTblCollection.Count;
REM MessageBox(0, "", 99, 99, "Location &collectionEFFDT -> " | &collectionEFFDT);

If &recType = "I" Then
&oLocationTbl = &oLocationTblCollection.InsertItem(&i19);
Else
If &recType = "U" Then

If &oLocationTblCollection.EFFDT = &currLocEffdt Then;
/*ERROR-SM - If I do not enter above statement Instance must exist to set property {LOCATION.EFFDT} and If I enter If &oLocationTblCollection.EFFDT = &currLocEffdt then I get First operand is .NULL error because collection has not been loaded*/
rem If 1 = 1 Then;
&oLocationTbl = &oLocationTblCollection.Item(&i19);
Else
&oLocationTblCollection.next();
End-If;
Else
&oLocationTbl = &oLocationTblCollection.Item(&i19);
End-If;
End-If;

rem &fileLog.WriteLine("&oLocationTbl.EFFDT = " | &oLocationTbl.EFFDT);
&oLocationTbl.EFFDT = &REC.EFFDT.Value;
rem &fileLog.WriteLine("&oLocationTbl.EFF_STATUS = " | &oLocationTbl.EFF_STATUS);
&oLocationTbl.EFF_STATUS = &REC.EFF_STATUS.Value;