3 Replies Latest reply on Dec 7, 2011 12:42 PM by philmodjunk

    Set Field By Name

    plegler

      Title

      Set Field By Name

      Post

      I have developed a script that uses the Set Field By Name script step, but the script doesn't produce any results. Here is the situation:

       

      - Application imports and processes financial and other numeric/text data for specific periods.

      - Numeric data is imported to a "working" or "staging" field, and then needs to be moved to the appropriate "period" field.

      - There is a preferences table where the period is set, and related to be accessible as a flag

      - I'm trying to use Set Field By Name to move data from the "working" import field, to the appropriate "period" field, and it isn't working

      Field name calculation portion of the script step is:

      Case ( COST_STRUCTURE_Preferences::Quarter = "Q1";COST STRUCTURE_resource assignments::Q1DQ;

      COST_STRUCTURE_Preferences::Quarter = "Q2";COST STRUCTURE_resource assignments::Q2DQ;

      COST_STRUCTURE_Preferences::Quarter = "Q3";COST STRUCTURE_resource assignments::Q3DQ;

      COST_STRUCTURE_Preferences::Quarter = "Q4";COST STRUCTURE_resource assignments::Q4DQ;

      )

       

      Calculated value to paste is: 

      COST STRUCTURE_resource assignments::WorkingDQ

      In Data Viewer, all of the fields, and their values are visible. The Preferences table flag is also visible (properlay related tables)

      When the Set Field By Name script step process in Script Debugger it returns Error 102 (Field is Missing). Tables and table instances involved are properly related.

      I've looked at the FM help system for this script step, and can't find anything wrong with my sytax. I've read the part about enclosing the field name in quotes, but the examples provided do show a syntax like mine. They use the If function, but I would think the Case function would work as well. It is, after all, the equivalent of a series of nested Ifs.

      Any help/suggestions on how to get this working would be very much appreciated.

       
       
       
       

        • 1. Re: Set Field By Name
          philmodjunk

          Your missing some quotaion marks.

          Set field by name evaluates the expression in the first parameter to get the name of the table occurrence::Field to which to assign the value of the expression in the second parameter.

          When you use: COST STRUCTURE_resource assignments::Q1DQ

          as the first parameter, FileMaker evaluates that by returning the current value stored in the Q1DQ field. Since this field is currently empty or stores a value that is not the table occurrence and field name of a field, nothing happens.

          Use:

          "COST STRUCTURE_resource assignments::Q1DQ"

          and COST STRUCTURE_resource assignments::Q1DQ will receive the name of the expression, COST STRUCTURE_resource assignments::WorkingDQ, used in your second parameter.

          PS. I'd use separate records instead of separate fields for each period. That's usually a much simpler and more flexible structure to your data.

          • 2. Re: Set Field By Name
            plegler

            Thank you.

            Ordinarily I would agree with your suggestion regarding separate records. In this case, however, and for a number of reasons, the structure we have is going to be more efficient. Many calculations, and the creation of many additional records will occur each quarter, which would result in a database of severl million records each year. And, while FM can handle it, reporting on a database that size would be extremely slow, and inefficient, given the IT resources of the organization that will be using it. This is a relatively short term, interim solution, pending the acquisition of a commercial application to do what they ultimately want.

            Again, thank you for the clarification.

             
             
             
             
            • 3. Re: Set Field By Name
              philmodjunk

              Hmmm, could easily be wrong here, but it would seem to me that putting the data in separate records or separate fields both will have the same calcualtion load---just with less flexibility...