AnsweredAssumed Answered

one-to-one grandchild table for date/time sorting?

Question asked by lombardi3g on Jul 25, 2015
Latest reply on Jul 27, 2015 by erolst

Hi All,  I am working on the data model for my FM solution.  In the field work of our farming, we do various inspections of different orchards.  So I have a parent table for Orchard records and several child tables - each for a different type of inspection (eg leaf Nutritional sampling, Pest infestation assessment, Soil moisture, etc - each inspection type has a very different set of Number and Text fields and quite a few of each for every type of inspection table).


I will need a report that for each Orchard will list all the inspections that have been completed.  They are to be sorted by data and time (NOT inspection type) within each Orchard section of the report.


I am experimenting with a grandchild table that would have the fields: InspectionDate, InspectionTime, and InspectionType_fk.  The InspectionType_fk would start with 4-characters that would be particular to the inspection type and then 9 spaces for a unique identifier number - the resulting fk for a partiuclar grandchild record could look like any of the following examples:


LEAF000000001 or

LEAF000000002 or

WORM000000001 or



Each InspectionType table would have a corresponding Key field.  The Leaf Analysis Table would have a InspectionType-Key that could hold the value LEAF000000001, for example.  Thus inspection records from all inspections types could be linked to common date and time fields for report sorting purposes.


I have set Relationships as the Orchard Table being the parent of the several Inspection Tables.  Then each Inspection Table is the parent of the same 'T_When' (Inspection) Table.


FM forces me to add additional Table Occurences but I’m a little fuzzy on this.  Anyway, the result is a grandchild Table (called 'T_When') with each record having only 1 inspection record as a parent but that inspection record could be from one of several inspection Tables.


Will this really work?  I am testing it but it seems a bit flaky and buggy.  One-on-One relationships going from one Table to several.  It seems a bit out there.


Will this be too performance intensive for running on an iPhone?


TIA, Allan