scripting table comparison and merging

[hatchethead]

Dear FMP ninjas,

I need some advice. I'm book-learned in FMP and I hit the boundary of my knowledge pretty quickly. But I'm the main expertise provider in my office, and we recognize FMP as a powerful tool for collecting project data. So I'm trying to make our DB as easy to use as possible for my colleagues using scripts, layouts, etc. Any advice, examples, pointers to online resources, etc. would be most humbly appreciated.

What's got me stuck right now is scripting a comparison between two tables in my database and then merging data in a nondestructive way.

To put my question in context, we are running a multiyear project for which we recruit 20+ participants a year from a field of 1000+ prospective participants. Our prospectives come from multiple sources, so importing is a bit of a pain - it has to be done on a case-by-base basis. Furthermore, each new set of prospects comes from a different place, so it might have prospects that are new to us and some that have come from other sources.

I've decided that it might be easiest for my people if our workflow started with importing a new set of prospect information into a new table in our database and then writing a script that would compare the new data with data we already have, thereby identifying what needs to be added to out dataset in whole form and what might need to be more carefully merged with our dataset. (Importing using FMP's update record overwrites field values, and this would not be Good for us.)

Here's a script that seems to achieve the first of these goals: comparing two tables to find rows in one table that do not appear in the other table. It puts in annotation in a dummy field to indicate if, based on an ID that we'd have, the individual exists in our database or not. (For this script I'm using a dummy database for testing.)

#FUNCTION: take a 'imported data' table and compare its contents, row by row 

#to the contents of another table, 'Original Data' 

#Write comment in field to indicate if row is common to or not in 'Original' 

Go to Layout [ “Imported Data” (Imported Data) ]

Show All Records 

Sort Records [ Specified Sort Order: Imported Data::ID; ascending ][ Restore; No dialog ]

Go to Record/Request/Page[ First ] 

Set Error Capture [ On ] 

Loop

     Go to Related Record [ From table: “Original Data”; Using layout: “Original Data” (Original Data) ] [ Show only related records ]

     If [ Get ( LastError ) = 101 ]

     Go to Layout [ “Imported Data” (Imported Data) ]

     Insert Text [ Imported Data::ErrorMsgOnFind; “to be added” ] [ Select ]

Else

#Without the following 'go to layout' command, I get a 102 Error (field not found). 

#Since my two tables are identical with the exception of the 'ErrorMsgOnFind' 

#field in Import Data, I figure this must be a 'which field is in focus' error 

     Go to Layout [ “Imported Data” (Imported Data) ]

     Insert Text [ Imported Data::ErrorMsgOnFind; “to be merged” ] [ Select ]

End If

Go to Layout [ “Imported Data” (Imported Data) ]

Go to Record/Request/Page[ Next; Exit after last ]

End Loop 

Set Error Capture [ Off ] 

Show Custom Dialog [ Title: "Record cross-check Done"; Message: "DONE"; Buttons: “OK”, “Cancel” ]

When I try to add in steps that will copy field values from the Import table to the Original Table, things go terribly wrong in unpredictable ways.  For instance, the script has overwritten my home Original Data table with data from a 'new' imported row.  I think it's also overwritten on row and added it again twice.



Here's the script that reveals my ignorance about FMP scripting:

#FUNCTION: take a 'imported data' table and compare its contents, row by row 

#to the contents of another table, 'Original Data' 

#Write comment in field to indicate if row is common to or not in 'Original' 

Go to Layout [ “Imported Data” (Imported Data) ]

Show All Records Sort Records [ Specified Sort Order: Imported Data::ID; ascending ][ Restore; No dialog ]

Go to Record/Request/Page[ First ] 

Set Error Capture [ On ] 

Loop

     Set Variable [ $valueID; Value:Imported Data::ID ]

     Set Variable [ $value01; Value:Imported Data::field A ]

     Set Variable [ $value02; Value:Imported Data::field B ]

     Set Variable [ $value03; Value:Imported Data::field C ]

     Set Variable [ $value04; Value:Imported Data::field D ]

     Show Custom Dialog [ Title: "Show ID number used in search"; Message: $valueID & " and " & $value01 & " and " & $value02 & " and " & $value03 & " and " & $value04; Buttons: “OK”, “Cancel” ]

     Go to Related Record [ From table: “Original Data”; Using layout: “Original Data” (Original Data) ] 

     If [ Get ( LastError ) = 101 ]

          Go to Layout [ “Original Data” (Original Data) ]

          New Record/Request

          Replace Field Contents [ Original Data::ID; Replace with calculation: $valueID ] [ No dialog ]

          Replace Field Contents [ Original Data::field01; Replace with calculation: $value01 ] [ No dialog ]

          Replace Field Contents [ Original Data::field02; Replace with calculation: $value02 ] [ No dialog ]

          Replace Field Contents [ Original Data::field03; Replace with calculation: $value03 ] [ No dialog ]

          Replace Field Contents [ Original Data::field04; Replace with calculation: $value04 ] [ No dialog ]

       Else

#Without the following 'go to layout' command, I get a 102 Error (field not found). 

#Since my two tables are identical with the exception of the 'ErrorMsgOnFind' 

#field in Import Data, I figure this must be a 'which field is in focus' error 

          Go to Layout [ “Imported Data” (Imported Data) ]

          Insert Text [ Imported Data::ErrorMsgOnFind; “to be merged” ] [ Select ]

     End If

     Go to Layout [ “Imported Data” (Imported Data) ]

     Go to Record/Request/Page[ Next; Exit after last ]

End Loop 

Set Error Capture [ Off ] 

Show Custom Dialog [ Title: "Record cross-check Done"; Message: "DONE"; Buttons: “OK”, “Cancel” ]

[bcooney]

"we recruit 20+ participants a year from a field of 1000+ prospective participants"

Can a previous participant participate again?

[hatchethead]

Yep. This would create an instance where the participant is in our Original table and also in a newly Imported table, and we would need to carefully merge her data into the Original table.

[bcooney]

Let's take that scenario. What do you mean by "merge" her data? What data is new in the Propect record that isn't in her existing People record?

She's a past participant, and now you'd like to promote her to a current participant. She is also present in your Prospects table.

So rereading again, you have a Project record that has related participants, some of which participate for more than one time period. So, I see Projects->Participants<-People, with each participant record having a DateStart and DateEnd. Additionally, you have a table of Prospects. You need a mechanism to add a Prospects to the People table, and then add them as a Participant to a Project, correct? Also, you want to avoid duplicate People. Please confirm that I'm on the right track.

[hatchethead]

OK, @bcooney, consider this for the case where we need a careful merge. On the one hand, there will be information in the Import table that will be static (e.g., First Name, Last Name, Home Phone) and mirror that which is in the Original table.

On the other hand, there will be new information. For example, there are several points of entry into our participant pool (e.g., website, email, campus visit). A contact in our Original table will have entered one way, and if she appears again in our Import table, she might have entered via another means. That new means needs to be added to the Original table without destroying the existing information about the way she previously entered the database. We want to have a record that she entered our pool in two different ways.

If we used the 'Update matching records', data in the Original table would be overwritten by data in the Imports table. We just need to be more careful about things.

But there's also the task of copying information for a new participant from an Import table to the Original table. This is something that I'm not able to do via scripting.

Thanks for expressing interest and taking time to ask good questions about the challenge facing me.

Does this help?

[bcooney]

"We want to have a record that she entered our pool in two different ways."

What I see is that you need to create child records to the People record, "Point of Entry" records, if you will.

If a person exists in your database as a Person, why are you even looking for them in a table of imported potentials? Why not just select reassign them to the project or extend their participation dates? I don't follow the business process.

I don't see the need to import here at all.

Edited March 12, 2010 by Guest

[hatchethead]

If a person exists in your database as a past participant in a project, why are you even looking for them in a table of imported potentials? Why not just select reassign them to the project or extend their participation dates? I don't follow the business process.

But I need to know whether or not they exist in my Original table before I do anything else. If they don't, I add them. Case closed. If they do, I proceed with caution about how I 'update' exiting information about them.

I don't see the need to import here at all.

Then I've failed to describe my situation properly. I apologize.

What I see is that you need to create child records to the participant "Point of Entry" records, if you will.

There are no 'point of entry' records. Rather, there is a point of entry indicator variable in the Original table. Perhaps I should have structured my database to have different information for different points of entry. Hadn't thought about that.

[bcooney]

"But I need to know whether or not they exist in my Original table before I do anything else."

So, build an interface that forces a Find before letting the user add a new person.

I don't see what info you are updating about an existing person. Their address? This "Point of Entry" attribute?

Basically, don't overwrite anything. Record new info in a child table, with a date created.