Forum Discussion

EdgarRamos_Estr's avatar
EdgarRamos_Estr
Qrew Member
3 months ago
Solved

Many parent records on a single child record

Hi everyone, I'm having some issues building the following: I need to build a table where a laboratory analyst can record analytical standards that they have prepared. I have one table that records ...
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    3 months ago

    np, This is a classic Many to Many relationship.

    One Analytical Standards uses many Regeant Inventories

    but also

    One Regeant Inventory is used in Many Analytical Standards

     

    so just create that middle table called perhaps "Analytical Standard Regeants".  Initially no fields.

    Make those two relationships I described

    One Analytical Standards has many Analytical Standard Regeants

    One Regeatnt Inventory has many Analytical Standard Regeants

     

    and lookup appropriate fields from the two respective Parent tables down to that join table.  Set the Proxy fields for the two related fields for Related Inventory and Related Analytical Standards. (that s a field proierty of those to "Related Parents fields".

    The go to that Join table and add in any extra fields you need, like presumably Qty, unit of Measure and another "baking" instructions for the recipe. 

    Then on the form for Analytical Standards, you will put an embedded report of the Analytical Standard Reagents so you can see the recipe when you view a Analytical Standard record.    Then similarly on the Reagent Inventory Form it's probably useful to put the report link for Analytical Standard Reagents as an embedded report so you can see which recipes (ie Analytical Standards) make use of that Reagent Inventory.

MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 months ago

np, This is a classic Many to Many relationship.

One Analytical Standards uses many Regeant Inventories

but also

One Regeant Inventory is used in Many Analytical Standards

 

so just create that middle table called perhaps "Analytical Standard Regeants".  Initially no fields.

Make those two relationships I described

One Analytical Standards has many Analytical Standard Regeants

One Regeatnt Inventory has many Analytical Standard Regeants

 

and lookup appropriate fields from the two respective Parent tables down to that join table.  Set the Proxy fields for the two related fields for Related Inventory and Related Analytical Standards. (that s a field proierty of those to "Related Parents fields".

The go to that Join table and add in any extra fields you need, like presumably Qty, unit of Measure and another "baking" instructions for the recipe. 

Then on the form for Analytical Standards, you will put an embedded report of the Analytical Standard Reagents so you can see the recipe when you view a Analytical Standard record.    Then similarly on the Reagent Inventory Form it's probably useful to put the report link for Analytical Standard Reagents as an embedded report so you can see which recipes (ie Analytical Standards) make use of that Reagent Inventory.

  • EdgarRamos_Estr's avatar
    EdgarRamos_Estr
    Qrew Member
    24 days ago

    My next question is this: Over time, reagent inventory will run out or expire, so lot numbers change. If I have an embedded report on a form showing me the "Recipe", then how do I ensure data accuracy. That is, how do I ensure that updating the joint table does not also update analytical standards made a few months ago with the newest lot information? 

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      24 days ago

      .. so two choices.  Either make a new parent record with the new Regent Inventory "batch", and mark the old one as Inactive (new field for Active/ Inactive").

       

      Or else have some concept of a batch # on the Parent Reagent record and look that up down to the usage table and then make a snapshot field to freeze in that value in indelible ink when the record is saved.

      https://helpv2.quickbase.com/hc/en-us/articles/4570403519508-Setting-Up-Snapshots-of-Lookup-Fields

       

      • EdgarRamos_Estr's avatar
        EdgarRamos_Estr
        Qrew Member
        18 days ago

        Hi mark, I have already an "Active/Inactive" field, a "Lot in Use" date field, and a "Lot Retired" date in in my parent table that houses "Reagent Inventory." The joint child table looks up and pulls in these three values from the Reagent Inventory parent table. What I have been trying to do is filter the embedded report on the Analytical Standards form so that joint records meet two criteria from the Analytical Standards from. For example, in my joint table i have that Sulfanilamide, Sodium Hydroxide, and N-(1-Naphthyl) belong in the recipe for the analytical standard "Sulfanilamide-NEDD"

        On my Analytical Standards table, I have a drop down field where the user can select "Sulfanilamide-NEDD" as the standard that they have prepped. At that moment the embedded report works just fine because i'm asking quick base to match the value "Sulfaniliamide-NEDD" in both the Analytical Standards Table and the joint table. Where i'm stuck now is that i want the date the the analytical standard was prepared to be used in the filtering of that embedded report. something like "Date Prepared" is on or after the "Lot In Use" date AND "Date Prepared" is on or before the "Lot Retired" date.

        Any help would be appreciated!