3 Replies Latest reply on Jun 14, 2012 3:19 PM by philmodjunk

    Set Field Not Pulling Data?

    RodrigoPerez

      Title

      Set Field Not Pulling Data?

      Post

      Help, please!

      I'm probably missing something very fundamental, but have gotten stuck while creating a simple project tracking database.  Any insight as to what I'm doing wrong will be greatly appreciated!

      My database has two tables: a Projects Table and a Tasks Table.  The Projects Table contains a field called Project ID Number, which contains an auto-generated serial number to identify each unique project.  The Tasks Table also has a field named Project ID Number, which I'm trying to populate with the serial number from the Projects Table at creation of the Task.  This will then tie the Task to that particular Project.

      In the Project Table's layout, I have added a button that when clicked runs a script to make a new Task.  It looks like this:

      Go to Layout ["Task Table" (Task Table)]

      New Record/Request

      Set Field [Task Table::Project ID Number; Project Table::Project ID Number]

      Go to Layout [original layout]

      When I run the script, I get a new Task Table record, but the Task Table field, Project ID Number, is blank.

      If I change the Set Field command to Set Field [Task Table::Project ID Number; 99] and run the script it works just fine and places 99 in the Project ID Number field.

      I have set up a relationship between the two Project ID Number fields in both tables, but still get nothing when running the Set Field command.  Here's hoping that this post makes sense to someone!

      Ideas, anyone?

        • 1. Re: Set Field Not Pulling Data?
          philmodjunk

          You have a relationship between the two tables, but there's no link between the new task table record and any record in the Project Table until you enter a value into the Task Table::Project ID Number field.

          Thus, at the time this script step executes, Project Table::Project ID Number cannot refer to any record in the Project Table as you are currently on the Tasks Table layout with a new record not linked to any Project Table record.

          What you can do is put the ID number in a variable before you change layouts, then use set field to copy the value from the variable into the ID field of the newly created record.

          Set Variable [$ID ; value: Project Table::Project ID Number ]
          Go to Layout ["Task Table" (Task Table)]
          New Record/Request
          Set Field [Task Table::Project ID Number; $ID]
          Go to Layout [original layout]

          • 2. Re: Set Field Not Pulling Data?
            RodrigoPerez

            Great!  Thanks very much for the quick feedback!

            I was wondering if FM would remember which record it was on in the Projects Table when I called the script and moved to the Tasks Table.  Apparently not Frown.

            Now on to see where I get stuck next.

            Thanks again!

            • 3. Re: Set Field Not Pulling Data?
              philmodjunk

              Such a "memory" really wouldn't work for all situations. There are many databases where you have multiple layouts based on the same table--each with different current records and the record you want the set field step to access might not be the current record on any of them.

              That said, you can use scripting to program such a thing into your database if you want it:

              Open up field options for Task Table::Project ID Number and give it this auto-enter calculation:

              $$ProjectNumber

              Then, on your project layout, use the OnRecordLoad trigger to perform this script:

              Set Variable [$$ProjectNumber ; value: Project Table::Project ID Number ]

              Now, any time you create a new record in the Task table--either manually or via your script, it will automatically link to the current record on your project layout. (But this won't work if you go directly to the Task layout without ever visiting the project layout to trip that trigger to load the ID into the global variable.)