Automatically Append IDs in Child Table from Parent Table

[ChristianM]

Hi All,

I have a table in Caspio "IDS_Export_Web_Inventory" that is automatically imported and replaced nightly. This table has a unique ID field [stock_number]. This is my parent table.

I have another table "Inventory_Inspections" with a unique ID field [Stock]. This is my child table.

How can I automatically append new records from "IDS_Export_Web_Inventory".[stock_number] that are not already in "Inventory_Inspections".[Stock] to "Inventory_Inspections".[Stock]?

I've created a task which seems to accomplish this goal but I'd like to confirm if this workflow will work and/or is the best recommended practice.

Thanks,
Christian

[NiceDuck]

What you will need here is a not in/not exist function. Heres an example of a Not exist function.

 

In this case, let's say that the 'Inventory_Inspections' is the 'cascade_b' table and the 'IDS_Export_Web_Inventory' is the 'cascade_a' table.

cascade_b.parent is the Inventory_Inspections.stock

and the cascade_a.ID is the IDS_Export_Web_Inventory".[stock_number] 

[NiceDuck]

Base on your initial setup, you may want to try this setup too:
 

 

This example is on trigger, but the same concept, basically, instead of the full outer that you initially used, what you need is a left join instead.