ScottPugh
6 years agoQrew Cadet
Query Parent Table based on fields from Child Record
I have 2 tables.. Table A and B... Table B is a parent of Table A
Table B contains the fields Product, Lot and active status
Table A contains the fields Product, Lot, and related Table B
When a new record is added in Table A I'd like to query through all records in Table B and if the Product and Lot fields from Table A record match exactly the Product and Lot fields from Table B record then I'd like to populate the record ID of the Table B record into the related Table B field in the Table A record.
I assumed this must be some type of automation but struggling to figure out how I essentially do the 'query' part so that every time a record is added in Table A it goes out to look and see if there is a matching record in Table B (Assume that I am already enforcing that Table B can have 1 and only 1 record with the unique combination of Product and Lot values)
Table B contains the fields Product, Lot and active status
Table A contains the fields Product, Lot, and related Table B
When a new record is added in Table A I'd like to query through all records in Table B and if the Product and Lot fields from Table A record match exactly the Product and Lot fields from Table B record then I'd like to populate the record ID of the Table B record into the related Table B field in the Table A record.
I assumed this must be some type of automation but struggling to figure out how I essentially do the 'query' part so that every time a record is added in Table A it goes out to look and see if there is a matching record in Table B (Assume that I am already enforcing that Table B can have 1 and only 1 record with the unique combination of Product and Lot values)