This seems like a fairly straight forward task to do in FileMaker Pro. What part of this process don't you know how to set up?
Do you know how to import the data?
Can you write scripts that loop through the imported data in the temp table and update the related records in your main table?
Thanks for the reply...
Although I am just now setting up the tables and defining the releationships, I'm fairly confident that I can use what I have read so far to create a recurring import to the temp file - hopefully this can be done via a button as people other than me will be doing it.
At this point I do not know how to write a script that would loop through the temp table and update the main table. I'm employed as a programmer so I just need to learn the syntax.
From your answer I'm asuming this can be done - good news!
"Recurring Import" usuallyrefers to a FileMaker 11 feature that has its limitations. You'll probably want to use Import Records within a script to do what you want and it can definitely be done from a button click or a button click that opens up a a file dialog to select the file from which to import the data.
Just to get you started, a typical script to loop through a found set of records follows this structure:
Go To Record/Request/Page [First]
//Do the stuff you need inside the loop here
Go To Record/Request/Page [next ; Exit after last]
The exit after last parameter is what exits the loop after the script reaches the last record in the found set.
OK that seems pretty straightforward. As for inside the loop, my file is keyed by email address. Do I have to loop through the membership or main file to find the correct record, or is there some kind of find operation?
If I don't find the record, I will want to add a new one. Is there an operation for update found record vs. add new record? And is moving the data as simple as membership_table.field1 = temp_file.field1?
The key is in the relationship you set up.
Relate your two tables like this:
MainTable::EmailAddress = TempTable::EmailAddress
enable "Allow creation of records via this relationship" for TempTable.
Inside your loop:
IsEmpty(MainTable::EmailAddress) will be true if no matching record exists in your main table.
Set Field [ MainTable::DataField1 ; TempTable::DataField1 ] will update DataField1 with newly imported data from the temp table
If no matching record exists, Set Field [MainTable::Anyfield ; Anyfield or value ] will create a new record in MainTable and automatically enter the email address from the temp table. This only works if you've enabled the "Allow creation..." option specified earlier in Manage | Database | Relationships.
Ok - I'll give it a shot... On thing I didn't understand was " enable Allow creation of records via this relationship" for TempTable" Wouldn't it be the other way around - create records in the Main table?
Yes, it would be for the Main table.