6 Replies Latest reply on Nov 4, 2015 10:10 PM by BruceRobertson

    Set Fields Confusion


      Hi all

      Calling on some help with a script I am writing.


      What I am trying to achieve is importing records into a table then comparing and adding non matching records in a related table


      It is all working well until I get to the loop where I am requesting a new record be created in the target table and then setting the fields I want to populate with the imported data.


      The new record is created but no fields are populated and it ends the script. I am pretty sure this is a context issue where I am probably not stating something simple but I can't see it.


      I have attached a screen shot of the script, any help will be greatly appreciated.

      Kind Regards


        • 1. Re: Set Fields Confusion


          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

               new record

               put all data from variables into correct fields

               go back to Import layout

               go to Record Next / exit after last

          END LOOP


          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.

          • 2. Re: Set Fields Confusion

            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.



            • 3. Re: Set Fields Confusion

              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

              • 4. Re: Set Fields Confusion

                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.



                • 5. Re: Set Fields Confusion

                  Thanks Marc

                  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.


                  Thanks again

                  Kind Regards


                  • 6. Re: Set Fields Confusion

                    Given the relationship you have described (auto-create based on name match) the following will work.


                    Add a Commit statement after line 31


                    Delete lines:

                    21, 20, 18, 16