How to show Parent information when Child table does not have a column indicating Parent key?

[Ricky_Chua]

I have 2 tables: Parent table has the following columns.

• Parent Key
• Parent Name
• List of Child Keys (comma separated list)

The child table has the following columns

• Child Key
• Child Name
• Parent Key
• Parent Name (virtual column [Parent Key].[Parent Name])

When I view the Child Table details, I will be able to see the Parent Name via the virtual column's formula. The problem with this setup is due to my company's sequence of operations.

• The child information is entered first before the parent is created.
• Thus the parent key column is left blank.
• The parent will then be created a few days later
• I use a bot to fill the corresponding child table row with the parent key.

(The actual scenario is the Child Table contains rows of quotations to client, and the Parent Table is the Purchase Order received which can encompass multiple quotations).

This has mostly worked for many months. But recently there were some Child Table rows that do not show the Parent Key (ie: sometimes the bot does not work properly).

My Proposed solution: Rebuild the Child Table without the Parent Key column. Use a virtual column to display the Parent information. Thus, no more bots.

However, this would mean I need to use a resource expensive formula for the virtual column such as ANY(SELECT(Parent Table[Parent Key],CONTAINS([LIST OF CHILD KEYS],[_THISROW].[CHILD KEY])). My app is already very slow due to large amounts to data in Google Spreadsheet. 

Any suggestions for how I can accomplish my goal while using less resources?

[baba_sawane]

Did you find why the bot failed sometimes ? Is it actual failure, or did it just not trigger ?

[Ricky_Chua]

I have not managed to find why the bot sometimes fails. It is every 1 out of 30 times that the Parent Key is not copied over to the Child Table. 

[baba_sawane]

How about creating an automation that will trigger when the one that (sometimes) fail, that will copy values of the last row if the child row does not contains the value ? (it's just theory but if it can be accomplished without using formula, worth the try)