Patch or Update?

[rolivier]

I have been spending hours trying to figure something out and can't.   Seems like it should be easy, but I'm struggling!

I have 2 tables.  "DailyPaperwork" and "HazardAssessment".   Both have been created using an offline collection and then the data will be sent to SQL Server.   While offline, the tables are connected by a "FieldKey".   

I send the "DailyPaperwork" table to SQL and get a UniqueID from the server.   Now I need to Update?/Patch? that unique ID from the DailyPaperwork table over to all the other tables (all currently linked with a "FieldKey"). 

I have tried about 1000 versions of "ForAll(...Update()).  ForAll(...Patch()).  UpdateIf...  Patch..."  I just can't get anything to work.   Any help would be appreciated.  Thanks in advance.  

DailyPaperwork:
ID: 123 (obtained from server)
FieldKey: 789789789

HazardAssessment
ForignID: (Null)
FieldKey: 789789789

I want to Patch/Update the ID from DailyPaperwork into the ForignID field in HazardAssessment (using the FieldKey as a common reference).  

Note:   There may be many records so I need a “bulk” method.  Also, not all of the foreign keys will be null.  So the logic I envision is. 

ForAll(Hazardassessment, Forignid = blank(),

Update(...)

);

[WarrenBelz · Accepted solution]

Hi @rolivier ,

You can use either 

ForAll(
   DailyPaperwork As aData,
   Patch(
      HazardAssessment,
      {FieldKey:aData.FieldKey},
      {ForeignID:aData.ForeignID}
    )
)

or

ForAll(
   DailyPaperwork As aData,
   UpdateIf(
      HazardAssessment,
      FieldKey=aData.FieldKey,
      {ForeignID:aData.ForeignID}
    )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[rolivier]

You have no idea how helpful this was!!!!  I had the logic all correct, but I think it was the identical field names that were causing me problems.   I didn't realize you could "AS" your way out of it!   

I was struggling with ambiguous field names in another situation and ended up with an ugly workaround.  This is so clean.   

THANK YOU!!!!

[WarrenBelz]

Happy to help @rolivier ,

As is a handy tool for disambuiguation.