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.
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.
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.
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!