hatchethead

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. Then I've failed to describe my situation properly. I apologize. 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.

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?

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.

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” ]

Thank you for your post. It's helping me solve a problem, but I was hoping you could help me understand how to adapt the solutions to my situation. I have a budget database and I was to create a script that will generate a sub-summary report for a found set determined by conditions set by a user. I have a settings/preference table with global fields for the settings, and I have those fields+values displayed on a layout along with the 'generate report' buttons. What I want to know how to do is to pull the global values into my script as parameters. (I bet this is an embarrassingly easy question to answer.) Thanks for whatever pointer you can share.