1 2 Previous Next 15 Replies Latest reply on Jul 27, 2015 11:39 AM by erolst

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

    lombardi3g

      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

      WORM000000002

       

      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

        • 1. Re: one-to-one grandchild table for date/time sorting?
          erolst

          lombardi3g wrote:

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

          WORM000000002

          I suggest you look at the following data model, which is based on the "Attribute-Value" model, and offers much more flexibility.

           

          Unless I'm mistaken, this has already been suggested to you in other threads (certainly by me), because “several child tables - each for a different type of inspection” usually suggests that you can (should!) combine them into one. (I think what you described in your post is trying to do that “after the fact” – so rather then try to patch several pieces into one, wha not start out with one?)

           

          The idea is to put all of your test fields from the misc. tables into one “definition” table, flag the tests by inspection type (or use a join table), then use this as blueprint to populate (via script) an InspectionTest table – where each of your former fields now is represented by a record that denotes a specific test and its result (that's the attribute and its value).


          This approach requires a fair bit of scripting, but is (as mentioned) much more flexible in usage, display and report creation, since everything is generated from one table, not several.

           

          Also consider that whenever you add a new InspectionType (or modify one), you just have to add a few new records (which could be done by a user, if the developer provides a UI) – and the scripted logic / reports will remain unchanged.

           

          Compare this to having to create new tables, and modify any number of other parts – which usually can (should!) only be done by a developer.

           

          This could look like so; make sure to read the comments that explain a bit more about the details of this model.

           

          Screen Shot 2015-07-26 at 11.26.34.png

          • 2. Re: one-to-one grandchild table for date/time sorting?
            electon

            It's quite tricky this one.

            FM forces you to create separate table occurrences because:

            you can link only one table occurrence with the same name of the same table to the whole relationship group,

            This is mainly to avoid circular references.

            Anyway your data model is not the best for this situation.

             

            I think it shouldn't be a grand parent table but a one-to-many table linked to Orchards and Inspections.

             

            Orchards > Orchard Inspections > Inspections

             

            Orchard Inspections will be the table to run reports on with fields like:

            OrchardID, InspectionType, InspectionDate, CompletionDate, InspectedBy etc...

             

            This way you will have a narrow table ( one table occurrence ) to look at and will help performance on wireless / WAN / iPhone.

            Also you can chose which inspections belong to an Orchard, remove or add new inspection types. See below...

             

            Ideally, you would also make the Inspections into one table. This will make reporting much easier, otherwise you'll need to resort to virtual list techniques.

            Also if you need to add a new type of inspection you will have to hard-code it. Create a new table / rework the layouts etc.

             

            So the way to create inspections for orchards is from an Orchard, by adding Inspection Types to the Orchard Inspections table.

             

            It's not easy and as intuitive as what you're doing now but that will save you a lot of pain down the road.

             

            erolst beat me to it.

            • 3. Re: one-to-one grandchild table for date/time sorting?
              erolst

              electon wrote:

               

              It's quite tricky this one.

              FM forces you to create separate table occurrences because:

               

              …  every field reference must be unambiguous; if from your current context you could “go” to another TO via more than one “route” (which presumably implement different predicates, and thus “filters” differently) – which one would you expect to be taken?

              • 4. Re: one-to-one grandchild table for date/time sorting?
                beverly

                e, do  you mean EAV (entity-attribute-value)?

                     <https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model>

                 

                or "Attribute-value system" (flat data, spreadsheets, association lists, etc.)?

                     <https://en.wikipedia.org/wiki/Attribute-value_system>

                 

                EAV may be popular with WordPress modules (PHP & MySQL).

                 

                Just fishing for further explanation on your method.

                 

                Would selector-connector help Allan here?

                     <https://www.geistinteractive.com/2014/11/21/filemaker-selector-connector-video/>

                 

                OR if not terrible complex, would ExecuteSQL be helpful in 'gathering' from separate tables (with UNION) into a set of data that may be sufficient (or pushed into virtual table - as demonstrated in the FileMaker Training Series)?

                 

                beverly

                • 5. Re: one-to-one grandchild table for date/time sorting?
                  lombardi3g

                  thank you very much eeriest and electon.  My vacillation is because I want to be sure from the start and avoid costly re-doings.  Usage here is in-house only.  It is a small farm my brother and I operate.  Only 2 laptops and 4 iPhones.  I am a user and the developer (or at least pretending to be).

                   

                  In a previous incarnation, I did have over 500 fields in one inspection table and, after a few years, I switched to several tables with fewer fields - which made developing much easier because it was no longer so difficult to find a particular field when coding and developing layouts for entry & reports - due to shorter and better organized tables.  Performance was very good and storage space was conserved (perhaps FM14 minimizes the penalty for so many empty fields in any given record?).  However, that incarnation had a handy alternative to deal with the 'sort by date and time' which looks to be unavailable here.

                   

                  Nevertheless, I am carefully studying your thoughtful recommendations because you have the better long-term view especially since this solution must include iPhones.  But please allow me to offer an improvement to my original data model - the grandchild Table could have several foreign key fields - one for each inspection type, i.e. inspection table.  Each grandchild record would have a value in only one of the foreign key fields.  And each of these foreign key fields would have a single relation line to the corresponding parent inspection table.  And each of the parent inspection tables would have a corresponding primary key for fk matching.  I suspect this would eliminate multiple TOs.   (BTW, a particular 'test' resides with only 1 inspection type)

                   

                  When a report proceeds to the next record, only the fetching of one inspection parent will be successful.  After FM sorts by the date and time fields of the grandchild records, each line of the report is formatted based on the inspection type that was not a failed fetch.

                   

                  Again, sorry for being overly circumspect - measure twice cut once?

                  • 6. Re: one-to-one grandchild table for date/time sorting?
                    erolst

                    I suggest you have a look at the attached sample file, where you can

                     

                    • define InspectionTypes

                    • add Inspections for Orchards

                    • have automatically created Inspection tests to fill in

                    • create reports by date range

                     

                    Note how the report is based on the InspectionTest tables and pulls data from a host of related tables.

                     

                    All this works with a total of 6 tables, regardless of the number of InspectionTypes, or how an InspectionType is defined in terms of Tests.

                     

                    You need to add the fields that specify whatever constitutes a status of “complete” for an Inspection – but that is not really a matter of the data model. Also, if the tests use different units or have Yes/No rather than a numerical answer, a few additional fields and a bit of UI adaptation is necessary – which in FM>13 can quite easily be done. 

                     

                    @ Beverly: yes, EAV is what I meant.

                    • 7. Re: one-to-one grandchild table for date/time sorting?
                      electon

                      The only problem would be that in a true EAV model you'd need to have a table for each data type.

                      Number, Text, Date, Container etc...

                       

                      At the moment the result field is a number field which may not apply to all tests.

                      I think that EAV is doable in the graph but presenting user with the fields to fill into is tricky.

                      Maybe having stacked objects with hide conditions would do, but it's much easier if the interface can be generated by html, and not be hardcoded into the layout.

                       

                      Even if EAV is the model, test data would need to be collected into one table via a virtual list for reporting.

                       

                      Or am I complicating things too much?

                       

                      Just my 2ct.

                      • 8. Re: one-to-one grandchild table for date/time sorting?
                        beverly

                        I wouldn't have separate data types in a table. Different fields perhaps? Or store all as text and convert (CAST) as the proper type if needed. Another column with data type certainly.

                         

                        Much to think about with EAV. I use it very carefully. I may even have "un-normalized" table just for sake of report such as OP needs.

                         

                         

                        -- sent from myPhone --

                        Beverly Voth

                        --

                        • 9. Re: one-to-one grandchild table for date/time sorting?
                          erolst

                          electon wrote:

                           

                          The only problem would be that in a true EAV model you'd need to have a table for each data type.

                           

                          You mean one field for each data type?!

                           

                          I'd say one field for each data type that's actually used – and if you need to record container data, I'd suggest another table (or file). People who are given the capability of recording images usually discover quite rapidly the need to record many images

                          electon wrote:

                          Even if EAV is the model, test data would need to be collected into one table via a virtual list for reporting.

                           

                          It is already being collected in a single table.

                           

                          It wouldn't make sense to consolidate a multitude of Type tables into one, only to have to deal with multiple Result tables!

                           

                          electon wrote:

                          but it's much easier if the interface can be generated by html, and not be hardcoded into the layout.

                          I guess that depends on who you ask … or who has to do the generating.

                           

                          But then, these are mere details

                          • 10. Re: one-to-one grandchild table for date/time sorting?
                            electon

                            1: I mean one table for each data type.

                            As Beverly sugested, it may be easier if the table holds all possible data types, with a respective column name field.

                            Or have one result value field as text and deal with it later. Indexing is pointless since the values can be anything.

                            Suppose test result is "Red" and not 2,45. A number field will not do.

                             

                            2: Container data only for a particular test result or a single document. Whatever that needs to be unambiguous.

                                the test > the result.

                                If there's a need for many pictures than sure, a separate table with only pictures, linked by test type.

                             

                            3: Layouts created dynamically are easier in html than filemaker in general just because you can't create dynamic layouts in filemaker. For one, body part or a portal row have fixed sizes and objects don't slide like in preview mode. You can only remove objects by hiding them, not add when needed.

                             

                            Whoever does the generating, in general it still is easier in html just because it's almost impossible in filemaker.

                            I'm not saying one is better than another.

                            • 11. Re: one-to-one grandchild table for date/time sorting?
                              keywords

                              "thank you very much eeriest" Don't you just LOVE autocorrect! I trust erolst enjoys this one too.

                              • 12. Re: one-to-one grandchild table for date/time sorting?
                                lombardi3g

                                Wow!! - I am speechless (almost).  A million thank-you's.  It will take me some time to digest this.  I still have not wrapped my head around FM relationships beyond the many-to-many and the simpler ones.  A look at examples will do it, though.

                                 

                                More to the point, I know now with a high degree of confidence that FM can produce the solution I am after and should scale to include the iPhones just fine.  Tomorrow I will purchase FileMaker Pro 14 Advanced before my trial period expires so I can review these more complex relationships (i.e. combo of relationships) in depth.  Hopefully, I can find an economical way to include a single iPhone for now so I can evaluate mobile performance.  Then in November, I will have a bigger block of time available to bring the solution to a reasonable level of effectiveness for the farm in 2016.


                                Until then I will continue to monitor this forum to slowly acquire a better feel for FM.


                                Many thanks!

                                • 13. Re: one-to-one grandchild table for date/time sorting?
                                  erolst

                                  keywords wrote:

                                   

                                  "thank you very much eeriest" … I trust erolst enjoys this one too.

                                  I have simple tastes and can be easily amused …

                                  • 14. Re: one-to-one grandchild table for date/time sorting?
                                    lombardi3g

                                    The file you posted for me is very helpful but clarification is needed.  The "inspections" are no more than a visit to the field to make observations or grab a sample.  Even though the sample goes off to a lab, the only date we want to track and sort by is the date of the visit.  The key reason here is that we want a history of each Orchard - we infer by the leaf sample taken on July 6 that it had a 2.6% level of nitrogen as later reported by the lab so the date of the analysis or inspection report date is irrelevant - only the block visit date matters on the block history report.

                                     

                                    So I think I need to add a date of visit field to one of the child tables but I'm not sure which one (there is never a need to combine different tests - each visit is for one particular set of observations which I believe we have referred to as a 'type'.   For example one type is the sampling of leaves for leaf analysis but this would never be combined into a pest assessment et al.

                                     

                                    Sorry for the confusion I have caused.

                                    1 2 Previous Next