DO you have FIleMaker Pro Advanced? The best thing to do is run this with the debugger on ( Tools / Script Debugger) and watch the errors that come up.
At first glance, it seems a relationship problem. Is there any relationship between TO02_People and TO02i_Nominations.Import? If not, the Set Field step won't know what to use.
I guess I'd restructure this by doing the following:
1. import records into your import layout.
2. Show all those records. Go to the first record.
3. Load the data from each field in that import layout (first record) into a variable–one variable per field.
4. Go to the Lo02_people layout.
5. Create a new record.
6. Set all the fields with the correct variables.
7. Go back to the import layout (you will still be on record 1 in that layout)
8. Go to next record.
Repeat the process, 3-8.
Here it is again:
show all records
go to record 1
set variables with data from fields
go to people layout
put all data from variables into correct fields
go back to Import layout
go to Record Next / exit after last
I'd just use local variables, but some would say, i think, to use Globals. I don't think it matters here.
You could do some other things, but that is the basics of it. You've gotta get the data from each record over into the table.
You probably could set a relationship between these two tables to say ImportTable::ID = PeopleTable::ID. Make sure that the IDs match: the ID in each record in import table should match its corresponding record in the people (first to first, 2nd to 2nd) and so on. Then you can use the relationship to match. There are some things to be careful about in this method. Try the first idea and see if that could work.
You are correct about it being a context issue, but that's only part of the problem. The New Record step will add the new record to the end of the found set. It will be the last record, so go to next record will simply exit the loop. Try this:
Swap steps 16 and 17 around so the go to layout is inside the loop.
Copy step 7 and paste it between steps 29 and 30
(gotta love line numbering)
If the set field steps still aren't working, check the relationships.
Thanks for replying guys
Marc I tried what you have said (that was very clear to understand - thanks! Your right the numbering helps :-) ) and the records are being added in the People layout but none of the fields are populating.
My relationship between the two TO's is between the First and Last Name fields in each table (at this stage) and the relationship is set to allow creation of records in the table via relationship into the People table and not into the Import table.
Any other suggestions would be greatly appreciated.
Jeremy I will try your suggestion as well re setting variables and let you know how I go. I am using FMP Adv and using the debugger but it is not showing any errors
Do you see the flaw in your logic? You are trying to set your key fields via the relationship, but the key fields haven't been populated to establish the relationship. Classic Catch-22.
Apart from the fact that using first and last name fields as key fields in a relationship is a really bad idea (more on that later), you should be able to make it work by setting just a couple of variables.
Between steps 16 and 17, add two Set Variable steps such as $first = First Name and $last = Last Name
Change step 20 to set field First Name to $first
Change step 21 to set field Last name to $last
Add a new step after 21 to Commit Records
Move step 19 down to after the commit step.
That should do the trick with minimal effort.
As for your key fields. You should never use the first and last name fields as key fields in a relationship for the following reasons:
1. People often misspell names during data entry
2. People change names all the time for various reasons including marriage, hiding from police etc.
3. Names aren't unique. It's quite possible you could end up with two John Smith's and they would share each others related data.
Instead, use non-modifiable auto enter serial numbers as a minimum. I'm sure that when the US wakes up, they'll offer you lots of fabulous, technically brilliant and insightful reasons for other types of key fields, but this will at least solve your immediate problem.
I really appreciate your help here, that has worked!
I am only using First and Last names as a match field as I don't have another unique field in the records (yet). I am only trying to get the info in there and then when I set it up for future imports I'll use a real unique match field.
Given the relationship you have described (auto-create based on name match) the following will work.
Add a Commit statement after line 31
21, 20, 18, 16