6 Replies Latest reply on Sep 17, 2012 11:32 AM by MorkAfur

    Question on SET FIELD BY NAME

    MorkAfur

      Title

      Question on SET FIELD BY NAME

      Post

           Hello,

           I have a simple script where I'm trying to copy a UUID-generated script value from a field in one table to it's currently-blank related field in another table. (The reason the field is blank in the related table is that I'm using an CSV file to populate the first table with 50,000 records).

           When I try to run the script below, the target field does not get populated. The Script Debugger isn't helpful in seeing what's going on. The $ variable is being set correctly in each row of the initial table.

           Here's the script. Any suggestions welcome! :)

           Go To Layout Table1

           Go to Record/Request/Page [First]

           Loop

              Set Variable [$var1; Value:Table1:id]

              Set Field By Name[Table2::idFromTable1; $var1]

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

           End Loop

           Why doesn't that work?

           Thanks in advance.

           -m

        • 1. Re: Question on SET FIELD BY NAME
          philmodjunk

               It looks like you should be using set field, not set field by name.

               Set field by name evaluates the first parameter, Table2::idFromTable1 to get the table occurrence and field names of the field to which to set the value from the second parameter, $var1. It expects text in the format of "TableOccurrencename::fieldname".

          • 2. Re: Question on SET FIELD BY NAME
            MorkAfur

                 Hi Phil,

                 I tried that, but it didn't work either.

                 Regarding the Set Field By Name comment you had above, I used the wizard to select everything so it should have worked too, right? I didn't see anything in my example books that said otherwise (though you may know something they didn't have...I would not doubt that. :))

                 Thanks,

                 -m

            • 3. Re: Question on SET FIELD BY NAME
              philmodjunk

                   What "wizard" are you talking about? They can be great time savers but you can't let them do all the thinking for you as they aren't flexible enough to handle all situations.

                   It looks like you need to change back and forth between layouts, but I don't see all the details to know if that is all you need here or not:

                   Go To Layout Table1
                   Go to Record/Request/Page [First]
                   Loop
                          Set Variable [$var1; Value:Table1:id]
                     Go to layout [Table 2 (Table 2)]
                          New Record/Request

                          Set Field[Table2::idFromTable1; $var1]
                          Go to Layout [Table 1 (table 1 ) ]
                          Go To Record/Request/Page [Next; Exit after last]
                   End Loop

              • 4. Re: Question on SET FIELD BY NAME
                MorkAfur

                     The "wizard" I was referring to was just using the FMP interface to pick the target field and calculated value (IOW, I didn't type these values in by hand).

                     I had already tried the switching layout idea before posting, but read that the nice thing about SET FIELD BY NAME (and SET FIELD, presumably) was that you didn't need to change layouts. In any case, that had no effect either.

                     The related table has the ability to modify data so the problem isn't in the relationship per se, but I'm thinking it has something to do with my database since I can get a simpler example to work.

                     I'll keep experimenting and post back with the answer I find.

                     Thanks,

                     -m

                • 5. Re: Question on SET FIELD BY NAME
                  philmodjunk
                       

                            I had already tried the switching layout idea before posting, but read that the nice thing about SET FIELD BY NAME (and SET FIELD, presumably) was that you didn't need to change layouts.

                       That's not fully accurate. In order for set field or set field by name to modify data in a record in a table different from that referenced directly in Layout setup for the current layout, there must be a valid relationship in place for that to happen. Given that you are trying to set an ID value typical to what is needed to link records in a relationship, it would seem unlikely that you have a valid relationship that you can use.

                       I suggest that you describe what you are trying to do in much greater detail. What is unclear to me is how you would determine which record in table 2 should get the value being copied via the variable from table 1. I made a guess that you are creating new records and the script I suggested will do exactly that, create a series of new records in table 2 using the data in the variable from table 1, but I was merely guessing that this was the result that you wanted.

                        

                  • 6. Re: Question on SET FIELD BY NAME
                    MorkAfur

                         I got it working. Your suggestions did the trick. I had forgotten to increment the table pointer on the target table and found that, as you said, setting the layout was necessary too.

                         It's very slow now for 50,000 records (about 5 minutes), but it's working.

                         Thanks Phil!

                         -m