I thought this would be simple and it probably is, but I've yet to come up with the answer, so I hope to find someone smarter than me to provide some guidance. 🤔 I've stripped down our actual requirements for simplicity sake.
Table A has the following fields: Company Name (display value, string), Company ID (string)
Table B has the following fields: Company ID (reference to matching Company ID record on Table A), Identifier (string)
Table A coalesce is on Company ID.
Issue: Using a Robust Transform, what is needed is that when a new record is being inserted into Table A, that reference is also established in the appropriate record in Table B.
We've had success in establishing the reference when the Table A record already exists, but not when the record is being inserted. The success came from querying Table A using a script (RTE Entity Operations, staging entity). The 'glide look up' option never seemed to return anything.
The simple path would be to go back to using standard transforms for this data, however we are trying to minimize the database operations as much as possible for performance reasons as the real-world implementation of this will be handling an extremely high volume, over 100 fields with data being applied to about 10 tables, all with varying references to each other.
If someone has been able to establish a reference between tables using a robust transform for inserted records, the community (and I, of course) would appreciate your wisdom! 😁
