6 Replies Latest reply on Feb 19, 2016 3:11 PM by dpwrussell

    Entering data into multiple tables with a single portal

    dpwrussell

      Hi,

       

      I am extremely new to Filemaker (literally, a few hours today and that's it), but I have a lot of experience with SQL databases. I have been asked to help on a Filemaker project.

       

      The actual problem is in the final paragraphs on this page!

       

      The project is to build a FileMaker database to manage Reagents in our lab along with interfaces to allow data entry, review and create reports. I have design a database structure like this.

       

      Screen Shot 2016-02-11 at 15.50.36.png

      There are 5 types of entity that we keep track of and we also track individual batches of these because there can be variation and they can be located in different places, etc. There are a lot of fields that are shared between all 5 types (Antibody, Protein, Small Molecule, Cell and Other) so I have normalized this into the Entity table. We also wish to be able to have a layout which shows our entire inventory (i.e. all 5 types) and just show the generic (i.e. in Entity table) information and as I understand it, UNION is not possible so that's another reason to have the Entity table.

       

      For each specific Entity (e.g. a particular Antibody) we will have zero, 1 or n batches. Each of those batches has a small amount of information which is specific to the Entity type upon which it is based (e.g. Cell Batch has a field 'Transient Modifications' which is not relevant to any of the others). So there is the generic batch information (e.g. date acquired, location, etc) in the Batch table and specific information about that batch type in the 5 Batch tables.

       

      As you can mostly see above, there are auto-generated IDs on Entity (This is what I would consider to be the Foreign Key Constraint in the 5 entity tables, but it seems that in Filemaker the 'relationship' is not identical to this constraint). There are also auto-generated IDs on the Batch table, again this is the relationship to the 5 batch tables.

       

      Layouts seem to work fine for visualising data, but I am having serious problems with data entry. Here is the layout I am working with for the Antibody entity.

       

      Screen Shot 2016-02-11 at 16.05.15.png

       

      When a user comes to this page, they can create a new record with the 'New Record' button in Filemaker. Because the (not exposed in the layout) field 'ID' is required, but not auto-generated in the Antibody table, it is impossible to leave the layout until some data has been entered into one of the displayed fields which are from the Entity table (e.g. Name). Once this happens, the record seems to be correctly created in both the Entity and Antibody tables, even if I entered no Antibody specific data at all.

       

      Problem

      However, if I start entering data into the Batch Information portal, the same does not hold true. The portal is set to use the Antibody Batch table. Some of the fields in the portal are from Batch table and some from the Antibody Batch table. If I enter some data into the fields which are from Batch, but do not populate any of the fields from Antibody Batch, then a record will be created in the Batch table for which no corresponding record in the Antibody Batch tables exists. The Antibody Batch record will only appear if I populate one of the fields which are from that table. I tried altering the portal to have Batch instead of Antibody Batch, but that doesn't really make any difference other than I don't ever seem to be able to get another row if I do that.

       

      Basically I need the row of the portal to create entries in the Batch and Antibody table in the same transaction regardless of whether any of the Antibody fields are populated.

       

      Possibly related to this. If I populate the Purity field of the Antibody Batch table in the portal it will accept this and create both the Batch and Antibody Batch records, but subsequently, the second row of the table when edited, will actually update the first row for the Batch fields. I have absolutely no idea what is happening there.

       

      Any help would be much appreciated!

       

      Cheers

        • 1. Re: Entering data into multiple tables with a single portal
          BillisSaved

          Good afternoon dpwrussell,

           

          I hope your day is going well. Please backup your file if you decide to try my suggestion, as I may have misunderstood your ultimate goal. If the specific Batch tables in your solution (e.g., Antibody Batch) only contain variations of the information contained the entities in the specific entity tables (e.g., Antibody), then you might try having your Entity table occurrence as the anchor - the leftmost table occurrence. Maintain your existing relationships with the specific entity table occurrences (e.g., Antibody), then create a one-to-many relationship between each of the specific entity table occurrences and their corresponding batch table occurrences. Ensure your relationships are configured to allow the creation of records in the child tables. Leave your portal set to the Entity table.

           

          I hope this makes some sense. Again, be sure to save a copy of your existing design be for you try this...just in case it makes things worse. Good luck and have a great evening!

           

          God bless,

           

           

          Bill

          • 2. Re: Entering data into multiple tables with a single portal
            DavidJondreau

            I think you're trying to get relationships to do too much work here.

             

            I would base the layout off Entity and the portal off Batch. Force the creation of the related Antibody records using scripts.

            • 3. Re: Entering data into multiple tables with a single portal
              dpwrussell

              Unless I am misunderstanding your suggestion, I don't think this will work as it would lead to multiple routes between the entity and batch tables. E.g. Entity->Antibody->AntibodyBatch->Batch and Entity->Cell->CellBatch->Batch.

               

              We need the generic Batch table for reporting purposes.

              • 4. Re: Entering data into multiple tables with a single portal
                dpwrussell

                Thanks for the suggestion, on what event would you suggest triggering the script to create the new records. Should it create the AntibodyBatch record or the Batch record or both? Would this still make it possible that another record could not be created until the required fields in the previous one had been populated.

                 

                Apologies if my Filemaker nomenclature is all wrong, I am struggling with there not being much of a distinction between record creation and record commit like there would be in a SQL database.

                • 5. Re: Entering data into multiple tables with a single portal
                  BillisSaved

                  Good morning dpwrussell,

                   

                  I hope your day is going well. Actually, I was think of something like this:

                   

                  Entity --->     Antibody     --->     Antibody_Batch

                     |      --->     Protein        --->     Protein_Batch

                     |      --->     Cell             --->     Cell_Batch

                   

                  The Entity table would serve as your anchor and the rest of the tables would be buoys. Then build your batch report on a layout based on the Entity table or create a Virtual List table and populate the report information with an ExecuteSQL statement. Good luck. Have a great day!

                   

                  God bless,

                   

                   

                  Bill

                  • 6. Re: Entering data into multiple tables with a single portal
                    dpwrussell

                    Thank you for your suggestions, but in the end, I just don't think FileMaker is designed to handle layouts for database design like this.

                     

                    If anyone is interested, I denormalized the Batches so that each of the 5 batch types contained all the basic batch information. This is annoying, but the most important thing was that the entities were properly split and globally numbered so it was a compromise worth making to get this finished.