1 2 Previous Next 25 Replies Latest reply on Jun 10, 2010 1:55 PM by Modulo

    Influencing Checkboxes from other Checkboxes

    Modulo

      Title

      Influencing Checkboxes from other Checkboxes

      Post

      I have three tables in a database for a biomedical research lab. One hold compounds, one holds information on batches of each compound, and one is for tests run on samples from each of those batches. In case of contamination or outliers, I have a check box to exclude the results of individual tests from the averages of those tests calculated for the compound. However, I also want to add a check box to exclude an entire batch and all tests run on samples taken from it.

       

      Is it possible to check a box on the Batch table and have it check all the boxes for tests run on that batch? If so, how?

       

      A potential problem is with the relationship structure: Both the Batch and Test tables are linked to the Compound table, so creating an easy relationship between the Batch and Test tables has been giving me a lot of trouble.

       

      Thanks!

        • 1. Re: Influencing Checkboxes from other Checkboxes
          philmodjunk

          Is it possible to check a box on the Batch table and have it check all the boxes for tests run on that batch? If so, how?

          If this field has a singe check box value that is either checked or unchecked, you can set up the field as a button and write a script that checks this field's check box and then finds the test records for this batch and checks them as well.

           

          A potential problem is with the relationship structure: Both the Batch and Test tables are linked to the Compound table, so creating an easy relationship between the Batch and Test tables has been giving me a lot of trouble.

          That is the key problem here. I can't describe the script for your check box field unless I know how you've related your tables. I specifically need to know which field matches to which  field in another table in order to define the relationship.

          • 2. Re: Influencing Checkboxes from other Checkboxes
            Modulo

            Thank you for your help. The Compound table has a "Compound ID" field, which is matched to the "Compound ID" field in both other tables. As well, a "Compound Identification" field, which is a concatenation of the Compound ID and the Compound Name is linked between the Compounds and the Tests table (possibly redundant, but it fixed a problem I was having earlier). 

             

            The Batches and Tests tables both have "Batch Codes" which I think ought to be linked. But of course, there is no place for a batch code in the Compounds table.

            • 3. Re: Influencing Checkboxes from other Checkboxes
              philmodjunk

              Is this what you see when you go to Manage | Database | Relationships and double click the lines linking your Table Occurrence boxes?

               

              Compound::CompoundID = Batch::CompoundID

               

              Compound::CompoundID = Tests::CompoundID AND

              Compound::Compound Identification = Tests::Compound Identification

               

              If so, then the relationship clause that refers to Compound Identification IS both redundant and is unlikely to have correctly "solved a problem" for you.

              I think using Batch ID is going to be part of the solution here. What is the purpose of each of your three tables?

              • 4. Re: Influencing Checkboxes from other Checkboxes
                Modulo

                Yes, those relationships are correct. 

                 

                As for the tables, the Compound table stores properties like the compound name, structure, molecular weight, etc for each compound. The Batch table is populated with the Batch ID, notes, who synthesized it, etc for each batch. The Tests table has the results of several standard tests, as well graphs and charts, as well as basic information like when the test was performed and comments.

                 

                The Compound table averages the results from the tests and Batches can be added and edited through a portal. On the Batches and Compound tables there are also portals to add and edit tests.

                 

                I've tried adding Batch ID to the Compound table to facilitate creating a relationship, but there must be multiple Batch IDs for each compound so it does not work, at least the way I've tried it.

                 

                Does this answer your question?

                • 5. Re: Influencing Checkboxes from other Checkboxes
                  philmodjunk

                  Thanks, I knew I'd helped you with this system before, but wasn't sure I was remembering all the pertinent details.

                   

                  A given "Batch" consists of exactly one compound or does it consist of several compounds?

                   

                  If it consists of only one compound, then you should link your tables like this:

                   

                  Batches::BatchID = Tests::BatchID

                   

                  Batches::CompoundID = Compounds::CompoundID

                   

                  Does that work for you?

                   

                  • 6. Re: Influencing Checkboxes from other Checkboxes
                    Modulo

                    Thanks, I think that's helping. Everything seems to work except for one thing. It broke what I think was working through that redundant relationship (it broke when I removed the relationship. I still don't understand what it was doing).

                     

                    The new problem is this: On the "Tests" layout I have a pop up menu to select the compound name (for adding new tests through the layout itself). I had this working so that it would automatically update the layout with the chosen compound's ID and an image of the structure (all stored in the Compounds table). However, now the relationship is gone and those two fields do not update when I change the name.

                     

                    I'm also having trouble with the script for the checkbox problem I described earlier. It's probably my inexperience with writing scripts that is the problem, but right now clicking the button on the "Batch" table will only update the first test from that batch.

                    • 7. Re: Influencing Checkboxes from other Checkboxes
                      philmodjunk

                      We've got two problems and I haven't really helped you with either yet. I need to get a clear understanding of your relationships first so I could walk you through the changes needed to make things work for you.

                       

                      First, getting your "broken" features in the Test layout fixed. In filemaker, you can make different relationships between the same two tables by making extra table occurrences that all link to the same data-source tables and then setting up different "links" for each. Here's how:

                       

                      Open Manage | Database | Relationships and click on Compounds to select it.

                      Click the button with two green plus signs to make a copy of this table occurrence box. (Filemaker really needs to label these buttons!)

                      Link this new table occurrence box to tests:

                      Compounds 2:: CompoundID = Tests::CompoundID

                       

                      Update your layous to refer to fields from Compounds 2 instead of compounds.

                      • 8. Re: Influencing Checkboxes from other Checkboxes
                        philmodjunk

                        Second, making the click of your check box in Batches also exclude all the test records in that batch by updating a field in each of those records so that their check box fields also show as checked.

                         

                        I'm going to assume that the "checked" value in both the Batches and Tests records is 1. If you have a different value, modify this script to use the values you're using.

                         

                        #Check the Batch record's check box

                        Set Field [ Batch::Exclude ; 1 ]

                        #Find the test records for this Batch and mark them also

                        Set Variable [$Batch ; Value: Batch:: BatchID]

                        Freeze Window

                        Go to Layout [ Tests ]

                        Enter Find Mode []

                        Set Field [ Tests:: BatchID ; $Batch ]

                        Set Error Capture [on]

                        Perform Find []

                        Replace Field Contents [ No dialog; Tests::Exclude; 1 ]

                        Go to Layout [original layout]

                        • 9. Re: Influencing Checkboxes from other Checkboxes
                          Modulo

                          The script works beautifully. Thank you!

                           

                          As for the relationships issue, I've set up the relationships like you said and changed all the references to "Compounds 2," yet it still won't update anything when I change the compound.

                          • 10. Re: Influencing Checkboxes from other Checkboxes
                            philmodjunk

                            How do you "change the compound" and is the field you are editing a part of the test table or the compound table?

                            • 11. Re: Influencing Checkboxes from other Checkboxes
                              Modulo

                              The compound is changed by choosing an option from a pop up menu that takes values from Compounds::Compound Name. The field is part of the test table.

                              • 12. Re: Influencing Checkboxes from other Checkboxes
                                philmodjunk

                                That explains it.

                                 

                                Since your records are linked by Compound ID, it's still linked to the wrong Compound record.

                                 

                                Update your value list so that it is a two column value list. Put compound ID in column 1, Compound name in column 2. Hide column 1.

                                 

                                Update your existing test records to reselect compounds by ID instead of name.

                                 

                                The alternative would be to simply redefine the relationship to link the tables by compound name instead of Compound ID. I don't recommend that as it causes problems should you ever have a typographical error when first inputting a new compound name if you generate some related test records before you discover the error.

                                • 13. Re: Influencing Checkboxes from other Checkboxes
                                  Modulo

                                  That has fixed almost everything. Now the only problem has to do with a value list for the Batch ID.

                                   

                                  On the Test table, after the Compound is selected, a second pop up menu should populate with values from Batch ID that are of the same compound. This was working before, but now when I create a new test and choose the compound, the Batch ID pop up menu has "<index missing>" as the only value. When I create a Test in the portal on either the Batches or Compounds layout, the Batch ID field on the Test layout has the correct batch but no options for changing it. Also, when I change the compound with the first pop up menu, the batch code values remain unchangeable.

                                   

                                  Could the problem be that the batch ID is not strictly numbers and I should make separate Batch ID and Batch Name fields?

                                  • 14. Re: Influencing Checkboxes from other Checkboxes
                                    philmodjunk

                                    I do recommend you set up an auto-entered serial number field for Batch ID and put any other text such as a name in a separate field, but that's because it makes for better database design not for the questions you've asked in your last post.

                                     

                                    I assume that this is what you are trying to do.

                                    1) create a new record in a layout based on tests.

                                    2) Select a value in Batch ID to link that record to a specific Batch record

                                     

                                    Open Manage | Value lists and check your settings for this value list. It should refer to the Batch ID field in the Batch table and this field should be set as an indexed field when you check its field definition in Manage | Database | Fields.

                                     

                                     

                                     

                                     

                                    1 2 Previous Next