Linking Related Records by Different Fields

[Drvt6713]

Hello All,

I need some help in linking related records that are not located in proximate vicinity to each other.

Initially, I thought I could use the Multi-field Formula tool but the distance between the records needing linked, varies.  The records to link are in the same TranId and use the value from one field (master record, Warr_Lk_LineId) to determine the ItemLine_no that it should be linked to (slave record).  Once the master record has been identified, I need to pull field value from ItemLine_item field in the slave record to the master linked record field Requires SK.

I am including a sample data file and desired output file.

Thanks for your help in advance!

[RolandSchubert · Accepted solution]

Hi @Drvt6713 ,

if I got you right, you can solve the problem using a self-join.

Joining the dataset to itself by using TranId and Line - Warr_Line sould return the corresponding lines (if there are any). Based on the join, you can fill the field [Requires SK] and add this to the data. I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

[Drvt6713]

Thanks for the help.  I added a unique record ID at the front so that I could eliminate the duplication on the last join, but your flow got me to the final solution.