5 Replies Latest reply on Dec 26, 2014 6:10 AM by lelandv

    Import data from one table to another (with conditionals)?

    lelandv

      Title

      Import data from one table to another (with conditionals)?

      Post

      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)

       

      (both tables also have additional fields, but are not relevant to the data import/transfer between them)

       

      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, to determine from the total history the HIGHEST grade for that "person" 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: Import data from one table to another (with conditionals)?
          philmodjunk

          I see no need for any importing at all. And I see serious potential issues with what you are doing with names.

          What you can do is define a relationship between the two tables by an ID field that is unique to each person in the Yudansha table. That relationship can then enable you to display the most recent dan_history record for that person without having to use an import to copy over the data at all.

          You could match records by first and last names as you do not, but what happens if you get two people with exactly the same names? It's better to define an auto-entered serial number field in Yudansha and link it to a number field in dan_history:

          Yudansha::__pkYudanshaID = dan_history::_fkYudanshaID

          If you sort dan_history in this relationship by it's date field in descending order, you can put fields from dan_history on your Yudansha layout and they'll show data from the most recent record in dan_history that is linked to that Yudansha record.

          • 2. Re: Import data from one table to another (with conditionals)?
            lelandv

            Hi Phil, 

             

            All things being equal, I would tend to agree with you.  The problem is that the two tables evolved separately.  The Yudansha table was originally intended to be only for active instructors, where as the dan_history contains records of EVERYONE.  Over time, it has proven useful to try to back-populate the Yudansha table with the information from dan_history.  (Yudansha also has also information concerning instructor, examiner and judge certifications, contact details, etc., whereas the dan_history table is just a chronological table of promotion exam results that I insert from reports received from Japan).

            Yudansha is already setup such that the promotion history of the individual is available via button that brings up a pop-up window.  The relationship FROM Yudansha TO dan_history isn't the problem.  The real problem is that the dan_history table gets updated quite frequently and I need to back-fill into Yudansha if the individual doesn't already exist there.  Also, if I receive a promotion report from Japan, I don't want to have to manually check if the individual exists in Yudansha, but rather, I simply want to enter it into dan_history and the application will update the "current grade" in Yudansha if the person exists, or simply insert the person into Yudansha if it's a new one.

             

            Hope that makes things a little clearer..

             

            Leland

             

             

            • 3. Re: Import data from one table to another (with conditionals)?
              lelandv

              (oh yes, and I need a way to do a one-time bulk back-fill from dan_history to Yudansha ... currently Yudansha has 97 records whereas dan_history has over 1000 with another 500 or so still to manually input from old paper records...)

               

              • 4. Re: Import data from one table to another (with conditionals)?
                philmodjunk

                A relationship that matches a record in Yudansha to many records in the history table can be used to match a record in the history table to a record in Yudansha.

                It can be used for that purpose. From a layout based on the history table, you can perform a find for all records that do not have a matching record in the Yudansha by entering find mode, putting an * in a never empty field from Yudansha such as a name field, selecting the Omit option and performing the find. This can be scripted as part of an automated update. You can then do an import records to import this found set from History into Yudansha.

                But I would only use this process to add new records from History into Yudansha. I would still use the method that I specified for showing the most recent data from the history table for each record in Yudansha.

                • 5. Re: Import data from one table to another (with conditionals)?
                  lelandv

                  Hi Phil, 

                   

                  That's exactly what I'm after.. thanks for the tip on the logic to do it.

                  I don't have a problem with working the other way (i.e. showing the grade history for a specific entry in Yudansha).

                  It's just that I have a LOT more people listed in the history than are actually in Yudansha, and given that the data-entry on a regular basis goes into dan_history, this is why I was looking for a way to auto-import/update Yudansha from this information.

                   

                  Cheers, 

                   

                  L.