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.
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.
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?
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?
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?
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.
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.
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]
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]
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.
How do you "change the compound" and is the field you are editing a part of the test table or the compound table?
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.
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.
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?
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.