2 Replies Latest reply on Dec 15, 2014 7:23 PM by FentonJones

    database set up



      database set up


      Is there a way to auto generate records from a "mother" record.  

      I am asking for reagent tracking and quality control where a stock of a specific reagent is aliquoted into many (say 20 - 30) so that I can have multiple unique records - that retain information from that original "mother" record.

      Not a programmer - apologize for inventing my own terminology

        • 1. Re: database set up

          Hi Pamela,

          I am not a professional so if my reply is not helpful or innacurate I hope another will step in. 

          It sounds like you have a Parent(reagent)>Child(aliquots) table structure. It seems likely this is going to be a one to many relationship where one reagent record will be related to many aliquot (records).

          Your Parent table should have an ID field (a professional has recomended a calc field (get(uuid)) ** to me as this will add robustness if you need to go mobile /offline/ synchronisation  etc later. **(remember to check the calc is set to output text on the bottom left of the specify dialogue box)

          Your Child table should have its own ID field as above but also a field (text) that will contain the ID of the parent above (you could call it fIDReagent (for foreign ID from the Reagent table).

          Create your relationship such that Reagent::ID = Aliquot::fIDReagent. 

          Depending on your needs you may like to create your aliquot records via a portal. If so it may be suitable for you to tick the "create records in this table via the relationship" box for the Aliquot(child) table.

          Again, depending on your needs you could create fields in the aliquot table that are optioned for 'lookup" and direct the lookup to fields in the parent table. Alternatively, consider that context is very important. Assuming your relationships are correct, if you have a layout from your child table with some fields (or merge fields) from your parent table these will only display data from related records from 'Reagent' for any given record of 'Aliquot'.

          Hope that is helpful.


          • 2. Re: database set up

            I don't know much about a "reagent", but the database needed is not complex (yet). You have 1 table for "reagents", and another table for the several "reagent aliquoted" records. 

            "Reagent" gets an auto-entered Serial ID (an option); so the first records gets an ID of 1, the next one 2, then 3, forever. Name as you please, "ReagentID", or "ID_Reagent" (whatever floats your boat). [It does not have to be just numbers; it could be R001, R002, etc., if you prefer, with a result of "Text" (option).]

            "Aliquoteds" (if that is even a word) is the other table. It will also have the above ("ReagentID"); but it will NOT be an "auto-entered Serial ID"; it will be simple, as it will be its "parent's" value, either created by an "Auto-entered" in the "relationship" itself, of via a short script to do the same. Each of the 20-30 records of a particular Reagent record will all have the SAME id in this field AS their same "parent" Reagent.

            Whether you use the option on the Relationship Graph, to auto-enter the id, or whether you use a script, you will use a Portal to show them. This portal's relationship to will be the own above. It will be visible on a Reagent's table "form view" layout. You'd set it up to show 30 rows (records).

            So, the basic method is: 2 tables, linked on the Relationship Graph via an id from the "Reagent" table to the "Aliquoted"(s) table. You can see the "Aliquoted"(s) rows/records in a Portal, on a form view of a "Reagent" record. [ You could also see the "Aliquoted"(s) in a layout based on their own table; likely in a List view, so you could see a bunch at once.]

            I hope this makes some sense. I cannot either read or write as well as I can work on databases (strange but true).