Forum Discussion

ScottPugh's avatar
ScottPugh
Qrew Cadet
6 years ago

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)
Quickbase Automations
Relationships
Tables and fields
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    6 years ago
    From reading through it, the question I have is more to the actual intent: 

    Are you trying to edit Table B in a way that Table B becomes the proper parent record to Table A? So when you look at Table B - the unique combo of Product/Lot that all Table A records for that same Table B parent?

    In a nutshell - you need to auto-associate the parent by creation of a child record

    The cleaner solution if possible is to make Table B records have a key field of something like:
    [Product]-[Lot]. This way - you don't have to do anything special - just make your reference field in Table A that same combo - and Quick Base will auto-link them for you. If this is a possibility - stop here and go that way.

    If the intent you're trying to achieve is correct and the above won't work- its actually 2 automations you have to do in order to accomplish it. Basically - you need the value from Table B to show in in Table A - while entering Table A data. Its not the prettiest - but you could play a bounce back game. Where:

    Record added in Table A - have an automation match records in Table B with that same Product and Lot. That's a built in a 'filter' feature as part of modifying existing records in Automations. In that edit - you log the record ID# as you mentioned. 

    Step 2 then - is you 'bounce back' from Table B back to Table A. Whenever the 'Table A RID' field you edit with the first automation changes - you basically 'send back' the Record ID# of Table B into the Related field. Basically the first automation is finding the 'Master B record' and telling it something is new that needs it as a parent. Then - automation 2 recognizes there is an unlinked child, and takes that RID, filters specifically that RID in Table A - and updates the related value to link it up.


    Chayce Duncan | Director of Strategic Solutions
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base