AnsweredAssumed Answered

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

Question asked by lelandv on Dec 24, 2014
Latest reply on Dec 26, 2014 by 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

Outcomes