Pipeline to add Parent record from child import
I have an App where we are importing standardized data from another source regularly and I have 3 parent tables where the key field is a text field that matches fields in the child table. I am trying to streamline where the User just has import a spreadsheet once and it will look to see if the values in the 3 fields match the key fields are in the Parent table and if not create a Parent Record and since they Key field is that text field they will link if there is a matching Parent Record. The imports will always have values so it will never have an issue where there isn't a value specified for either 3 of those fields.
I created a Pipeline in the child table triggered whenever records are added, I added a query to step A where in my child table I added lookup fields for the actual Record ID numbers for all 3 fields from the parent tables and set the query to be triggered when records are added and the Record ID is not set, because the actual key field is that text field that already has values so if there is already a matching value for the key it will return that Record ID, but if a Record ID is not set it then fires and searches those Parent records and looks to see if the values in those 3 fields are records in any of the parent tables and if not it will add a Parent record.
Everything works, but the only thing bugging me is it throws the errors because it's going through my child list and there might be instances where multiple records have that same Key so it tries to add it multiple times to the Parent table, but because it's the key it only adds the one record which is what I wanted, but with it always throwing an error I'm just worried that if something else fails I won't catch it because there will always be an error message. Any thoughts?
------------------------------
Ryan Locke
------------------------------
- One solution is to import to a temporary scratch table. Then have a relationship back to the parents who may or may not exist. But then also have a formula query to detect duplicates and only flag the first duplicate.
Here is one Blog on that.
https://community.quickbase.com/blogs/ashley-banning/2021/07/26/finding-duplicates-in-qb-just-got-easier
The "extension" to your use case would be to hive off the first result form the Formula Query and only import where the [Record ID#] matches the duplicate finder.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------