8 Replies Latest reply on Dec 26, 2014 12:33 PM by lelandv

    FMP13(Adv): Import data from one table into another (with conditionals)

    lelandv

      Hi All,

       

      I was wondering if someone could possibly provide some advice/tips/pointers on how to accomplish an import from one table to another within a single FMP13 application.  The data to be imported needs to be validated against a couple of checks, both from the source table, and also in the destination table.

       

      Here's a brief description of what I'm attempting to do:

       

      I have two tables in my application.  The first one, called "Yudansha" contains general overview for active members of our association (name, current grade, etc.).  The other one, called "dan_history", contains a chronological record of all grade examination/promotions over the years for all people within the organisation.

       

      The RELEVANT fields for each table are:

       

      Yudansha:

      LastName   :   (person's last name)

      FirstName  :  (person's first name)

      JKAGrade  :  (person's CURRENT grade - basically just a numeric value from 1..10)

      GradeReg  :  (The registration serial number for the person's current grade)

      GradeObtained :  (the date that the current grade was awarded)

       

      dan_history:

      Date :   (date of promotion/grade awarded)

      Grade :   (grade awarded, numerical value from 1..10)

      Reg :  (Registration number for the grade)

      LastName :  (person's last name)

      FirstName :  (person's first name)

       

       

       

      I am trying to come up with a function/script that I run periodically (manually, of course) whereby the data in the dan_history table updates or appends the Yudansha table (but not all fields -- just the RELEVANT ones outlined above).

       

      Because the dan_history table contains a full history of all promotions for all people, I want to first match only the HIGHEST grade for each person listed in the table.  I then want to map the relevant fields in the dan_history table for the selected entry and insert them into the Yudansha table with their relevant mapping.  (LastName to LastName, FirstName to FirstName, Grade to JKAGrade, Reg to GradeReg, Date to GradeObtained).

       

      Basically, I need, for each "person" in the dan_history table, determine from the total history the HIGHEST grade and use that as the information to be imported to Yudansha.  I then need to check in Yudansha if the person (LastName/FirstName) already exists in the table or not.  If not, then I need to simply append a new record with the data.  (LastName and FirstName are also used to import into the Yudansha table in this case).

      If the person DOES already exist in Yudansha, I need to determine if the grade information in Yudansha is the same as the imported data selected from dan_history.  If the data is DIFFERENT, then I need to replace the data in Yudansha with the new information from dan_history.  If the data is the SAME, then it indicates that Yudansha is already up to date for that person, and do nothing with the record.  (In this case, LastName and FirstName are no longer used, just the date, reg, and grade fields are used for import/update of the Yudansha table)

       

      I can't seem to find a graceful way of doing this, so any advice would be greatly appreciated.

       

      Many thanks in advance.

       

      Leland

        • 1. Re: FMP13(Adv): Import data from one table into another (with conditionals)
          erolst

          lelandv wrote:

          I can't seem to find a graceful way of doing this

           

          I'm not sure why you need to do this at all.

           

          Why not simply use the two related tables (to use more generic names) People and Promotions as is? You can sort and filter the related records by any criteria you want.

           

          If necessary (e.g. for display purposes), maintain a record of the respective current grade by using a foreign key to reference the Promotion record in question (if it is different form the latest Promotion record; if not, you could use a calculated key, or maybe even a filtered portal).

          • 2. Re: FMP13(Adv): Import data from one table into another (with conditionals)
            lelandv

            hi erolst,

             

            I could if the two were synchronised from the start with all the "People" concerned.  The promotions history grew after the original Yudansha database.  (originally the Yudansha was only to track active instructors).  On the input form for Yudansha, I have a button which brings up a pop-up listing of the promotions history of the individual concerned, using the name as the key and selecting all the promotions for that individual from the promotions history database.

             

            Basically, the two tables were originally going to be managed separately, but more and more I am needing to link/integrate them.

             

            Over time, I decided that I would expand Yudansha to include ALL dan grades in the association, so am looking for a way to "back-fill" these into Yudansha from the promotions history.  But given that they're separate tables and I need to be able to run reports on them independently as well.  The current Yudansha table has 97 entries, whereas the promotions history table has over 1000 entries. 

             

            It turns out that the table that is updated more frequently now is actually the promotions history table (regular promotion reports received from Japan that I have to input into the table, so these need back-filled into Yudansha if the person doesn't exist there already).  Also, I still have about another 500 entries to manually input into the promotions history table from the old paper records, and these too will need to back-populate.

             

            Leland

            • 3. Re: FMP13(Adv): Import data from one table into another (with conditionals)
              lelandv

              I forgot to mention, that once I find a way to back-fill, then I can certainly use a relational linking from there on since they'll be synchronised... but will still need something to automatically create the new Yudansha record if there is a new promotion entered for a person that doesn't yet exist there.

              • 4. Re: FMP13(Adv): Import data from one table into another (with conditionals)
                erolst

                In the end this comes down to checking if the person named in one or more “new” promotion records exists; if it doesn't, create a new one based on the biographical data contained in the promo data, then link the new Person record back to these promotion records, using a unique serial ID.

                 

                (New as in “not already related via a foreignID”)

                 

                You could create a relationship based on the full name and maybe address data (since you have no other data to do this anyway), and for every promotion record that doesn't already have a foreign personID, check the number of related People records:

                 

                1. no related record = create new person, link back personID

                2. one or more related records = either could be the right one; check manually, select ID of the correct one, or, if neither is correct, use 1.

                 

                Both tasks can easily be performed by a script.

                lelandv wrote:

                but will still need something to automatically create the new Yudansha record

                 

                I'm not sure you can automate this fully and be confident that all entries will be correct (as you can see from the safeguards I outlined above); but semi-automatically is definitely a possibility, i.e. reduce your own efforts to a number of mouse clicks to start a script …

                 

                So there is not really a need to import entire records; you simply must set a foreign key of a record that you may have to create; obviously, the better the unique marker is you can come up with, the more reliable your results …

                • 5. Re: FMP13(Adv): Import data from one table into another (with conditionals)
                  muddylanding

                  I think the first thing to do is to be sure each record in the Yudansha table has a unique primary key. Then add a foreign key field to the Dan table.

                  Don't try to append data from one table to the other because one table should have information about a person and the other about a qualification. If you use a relationship between the primary and foreign keys in the two tables you won't have to move any data.

                  When you want to see a list of people with their highest qualification, just insert a portal in the Yudansha layout and sort the portal from highest to lowest qualification.

                  IF you have trouble with this, send me an empty (clone) copy of your database and I will add the appropriate relationship and script for you as an example.

                  • 6. Re: FMP13(Adv): Import data from one table into another (with conditionals)
                    lelandv

                    Hi "muddylanding".

                     

                    Thanks for the comments.

                    Currently I don't really have a problem with obtaining the qualification history of an individual from within "Yudansha".  The problem really is that since the qualification reports from Japan are entered into the dan_history table directly, there may or may not be a corresponding "person" in Yudansha.  (the two tables originally started separate and independent lives, and originally started out simply as excel spreadsheets which were imported into Filemaker to facilitate use -- and to be able to

                    generate certain reports from the data).

                     

                    Basically, every couple of months, I receive a report from Japan that has just a long list of qualifications issued in the past period, which I manually type into the dan_history database.  The format of this report is simply:

                     

                    Date | Grade | Registration | Last Name | First Name | Country | Nationality | Chief Examiner

                     

                    There is no specific order to this report, but each report contains anywhere from 10 to 50 lines.

                     

                    I have a specific Layout for "Yudansha" which allows me, for any given person in Yudansha, to view their qualification history, linked from the dan_history table.  (see attached screenshot, for example).  In this sense, I don't have a problem with linking *from* Yudansha *to* dan_history to obtain the history of a specific individual's qualifications.

                     

                    The real problem is that currently if I enter a new qualification report, and an individual is not in the Yudansha table, I have to also enter the person manually in Yudansha... very long-winded.  Also given that the current dan_history has several hundred more people in it than the Yudansha table, back-porting this information into Yudansha would take a very long time to do manually.  So ideally, I would like a way to be able to input the dan_history as usual, but with an automatic check to see if the person is listed in Yudansha.  If not, then a new entry with just this basic information is created, and if so, then just the items in "JKA Grade" of the attached layout is updated.

                     

                    Hope that helps clarify what I'm trying to accomplish, and of course I need to be able to do this in the least "disruptive/destructive" way possible for the tables.

                     

                    yudansha1.png

                     

                    yudansha2.png

                     

                    Regards,

                     

                    Leland

                    • 7. Re: FMP13(Adv): Import data from one table into another (with conditionals)
                      DavidJondreau

                      >In the end this comes down to checking if the person named in one or more “new” promotion records exists; if it doesn't, create a new one based on the biographical data contained in the promo data, then link the new Person record back to these promotion records, using a unique serial ID.

                       

                      Erolst has it right. Do this and the rest will fall into place.

                      • 8. Re: FMP13(Adv): Import data from one table into another (with conditionals)
                        lelandv

                        Well.. that's pretty much what I was indicating in the original post... the only thing is that I'm not quite sure HOW to do that (in real-time when entering into the promo data, or to backfill from it)...