11 Replies Latest reply on Jun 3, 2013 10:01 AM by DavidJondreau

    Missing Fundamental Issue

    Jason_Farnsworth

      I have a simple table DataBase Relationship attached to show an example:

      Capture.PNG

       

      If I create a record within the _Sub_Table (and I have the option checked in the relationship to allow a record creation in the main table via this relationship) should it not create a record in the main table and put the _kf_MainID into the Sub_Table field of _kf_MAINID?

       

      What I am trying to so is a bit more complexed that the example but I hope it gets the point across. I have (3) tables in my database, a JOB table, a DataLog_Main Table, and a DataLog_LineItems Table. The Job table is the main table, The Datalog_Main is a table that I store root details of a data log ( Much like the main details of an invoice), and finally the Lineitems table is all the different data points. I could have many DataLog_Main tables within one JOB table, and many Lineitems per a DataLog_Main table.

       

      Where I am getting stuck is I am Importing the LineItems from and excel spreadsheet, and during the import cycle I am missing the link of lineitems to the Main Table.

       

      I have something very fundamental that I am missing or overlooking that I am sure is a simple stupid thing, but nonetheless I digress.

       

      Any help would be great, and thanks in advance

       

      Jason Farnsworth

      Midland, TX

        • 1. Re: Missing Fundamental Issue
          mikebeargie

          If I create a record within the _Sub_Table  (and I have the option checked in the relationship to allow a record creation in the main table via this relationship) should it not create a record in the main table and put the _kf_MainID into the Sub_Table field of _kf_MAINID?

          Correct. That feature only allows you to add records inside of portals. That will never automatically create a related record though.

           

          What you need to do is loop through your child table records after import and create the related parent records via a script.

          • 2. Re: Missing Fundamental Issue
            Jason_Farnsworth

            Mike,

             

            That's kind of what I was backing into, it seems a bit drab but I guess it is what it is. I was hoping to take advance so automated functions in the process. But since I am importing the file I miss some opportunities to add things, I guess I will have to script it.

             

            Thanks for the pull back into reality.

             

            Jason

            • 3. Re: Missing Fundamental Issue
              mikebeargie

              If it was (in reality) a one-to-one relationship, you could *technically* export from the child table and reimport that to the parent table.

               

              Unfortunately, until ExecuteSQL() gets opened up to allow for more than just SELECT, there's really no great way of automatically creating related records.

              • 4. Re: Missing Fundamental Issue
                Jason_Farnsworth

                Mike,

                 

                I think that rather than do a loop cycle, I will save the ID to a variable and then embed in into an autocomplete filled as the record is being created.

                 

                This shoud save a little effort I would think.

                 

                It seems like an over sight on Filmaker part.

                 

                Jason

                • 5. Re: Missing Fundamental Issue
                  mikebeargie

                  I wouldn't place the blame on filemaker. There's no RDBMS that makes it THAT easy to create related records without writing some code out (IE SQL you can do it, but it's not beginner level INSERT statements.)

                   

                  The setting distinctly states "Allow Creation" not "Automatically Create". This single setting DOES make portals wildly useful. Otherwise you'd have to always script to create a record in a related table, setting the parent ID to variable, going to a different layout, creating a record, returning, etc.. But I can just tell users to start typing in a blank row in a portal in filemaker.

                  • 6. Re: Missing Fundamental Issue
                    DrewTenenholz

                    Mike & Jason --

                     

                    There's also something that bothers me about the description Jason gave and how I see creating related records.  If I understood Jason's request, he wants the child record to be able to create the parent (and possibly the grandparent).  He shows a key relationship similar to

                     

                    parentID_in Child <--> parentID (in parent record)

                     

                    and then asked if, when creating the child record, wouldn't the parent record both be created and the parent's ID copied into the child record. 

                     

                    Even if you had tried to do this manually with a portal it would not work.  The 'parentID_in Child' is going to be blank initially, and thus there is no value to put into the parent record upon auto-creation.  (Auto-creation is a what I'd call a 'push' process, not a 'pull' process.)  While I suppose FileMaker is likely to try and work with you here, either the auto-entered parent ID is going to be filled in and the child record (with the empty value) is not going to be linked to the correct parent record, or the parent ID will be blank (and thus linked), but so will every other record thus created, and you still have no proper links.

                     

                     

                    In this situation, I'd go with a separate utility file (and maybe not just a table) containing the basic child records using the straightforward FileMaker import, that uses a loop to:

                     

                    1) Create a grandparent record

                    2) Bring that grandparent ID back into the import utility file

                    3) Create a parent record  (and use the grandparent ID when creating it)

                    4) Bring the parent ID back into the import utility file

                    5) Create the child record (using the appropriate parent and grandparent IDs)

                     

                    While looping through the child records figure out a way to know when it is required to create new parent and grandparent record.

                     

                    While this is slower than the straight FileMaker import, you get all the control you need, and this will play nice in a multi-user situation as a bonus.  I've done this as far back as FileMaker 5/6, and it gets the job done.

                     

                    And for gosh sake, use UUIDs for internal linking and such.  Once you get this right, you'll want to be able to keep everything linked up without relying on some "ID" that was in Excel....

                     

                    -- Drew Tenenholz

                    • 7. Re: Missing Fundamental Issue
                      mikebeargie

                      His graphic above only showed a one-to-many relationship between two tables, which is why I suggested a loop after import to create the MAIN_TABLE records after importing SUB_TABLE

                       

                      That loop would look something like this, run from the SUB table after import:

                       

                      Go To Record (first)

                      LOOP

                          Go To Layout (MAIN_TABLE)

                          New Record

                          Set Variable $id = MAIN_TABLE::idKey (auto enter serial)

                          Go To Layout (original)

                          Set Field (SUB_TABLE::MAIN_TABLE_idKey) = $id

                         Exit Loop if ( get (foundCount) = get (recordNumber) )

                         Go To Record (Next)

                      END LOOP

                       

                      This would only really work for one-to-one relationships without more robust code. Drew's method is a lot more robust though to account for more variations of the data, as well as the three tiers.

                      • 8. Re: Missing Fundamental Issue
                        Jason_Farnsworth

                        Mike / Drew

                         

                        Rather than do the loop routine, I thought to create the parent record a head of time (the import) and set a variable with the Parent ID, then in the Linking ID (child record) I set up a Autoenter Value that is previously saved variable. Then it just fills in the Parent ID during the import process, it seems to work ok.

                         

                        What do you think about this method? The only time a Parent Record will be created is during the import cycle so I do not need to worry about if one is created outside of the import.

                         

                        Jason Farnsworth

                        Midland, TX

                        • 9. Re: Missing Fundamental Issue
                          mikebeargie

                          The only time a Parent Record will be created is during the import cycle so I do not need to worry about if one is created outside of the import.

                          That's a luxury most developers don't have to work with, and makes your method acceptable in my eyes.

                           

                          I would caution to lock out user privileges to the parent table to block creation,,, just to be safe.

                          • 10. Re: Missing Fundamental Issue
                            Jason_Farnsworth

                            Ok point of all the imported records is to use them within a chart as I have multiple charts per job. Thus the Grandparent (JOB), Parent (CHART ID), and Child (CHART LINEITEMS), which I think is the correct set up in this instance.

                             

                            The results of the chart seem to be using all the record for the Grandparent record and not by the Parent records. See below..

                            tube.PNG

                            Shell.PNG

                            Within that chart I have it being filtered by a unique identifier, The blank spaces tells me it is using the Grandparent ID as a base and not the Parent.

                             

                            Thanks again for your thoughts,

                             

                            Jason Farnsworth

                            Midland, TX

                            • 11. Re: Missing Fundamental Issue
                              DavidJondreau

                              "Even if you had tried to do this manually with a portal it would not work.  The 'parentID_in Child' is going to be blank initially, and thus there is no value to put into the parent record upon auto-creation.  (Auto-creation is a what I'd call a 'push' process, not a 'pull' process.)  While I suppose FileMaker is likely to try and work with you here, either the auto-entered parent ID is going to be filled in and the child record (with the empty value) is not going to be linked to the correct parent record, or the parent ID will be blank (and thus linked), but so will every other record thus created, and you still have no proper links."

                               

                              This is inaccurate. FileMaker can handle this situation just fine. _fMainID will populate with the key of the newly created record in the Main Table.