Setting ParentId for child rows

[shaked]

I have a CSV table that contains both parent and child records.
I need to identify if a row is a child based on a specific value in the Name field.
The parent is also identified by a specific value in the Name field.

For each child record, I need to:

• Find the relevant parent record within the same table

• Update the child row’s ParentId field with the value of the corresponding parent

I am looking for an efficient and clear way to implement this logic in Workato.

[rajeshjanapati]

Hi @shaked,

you can implement with the help of SQL Transformations by Workato application by passing CSV File contents or a file from FileStorage and implement the logic in SQL Query, where you can get the required output rows in a single step. Hope you got my point?

[shaked]

Thank you for your response,
I’m relatively new to Workato and wanted to make sure I’m following best practices.
In your opinion, would using a Lookup Table be the right approach here for mapping parent and child accounts, instead of  using SQL?

[francbaviello]

Hi, the solution @rajeshjanapati 's is ok because:
Lookup table: Not suitable for large volumes, No complex queries, No relationships
SQL Collection: You have many records, Advanced SQL queries, You need to make complex queries

[shaked]

Thank you for the explanation.
Since I’m still new to Workato, I wanted to get your guidance.
In my case, the CSV file is relatively small and contains a limited number of records.

Given this context, would a Lookup Table be a reasonable approach here, or would you still recommend using SQL Collections as a best practice?

I’d appreciate your perspective.