1 2 3 Previous Next 31 Replies Latest reply on Aug 16, 2011 11:22 AM by philmodjunk

    Scripts Question regarding Value Lists

    AnnetteSteinberg

      Title

      Scripts Question regarding Value Lists

      Post

      I'm new to FM.  I need to create a Report using a field from a table that has a value list with several names. There is one value from the list I don't want to show in the Report. or putting it another way,  I only want Records to show that have the remaining values from the list.

      I've tried several options from other forums - nothing seems to work.  I'm sure I'm skipping some step.  Any help is appreciated.

      Thanks,

        • 1. Re: Scripts Question regarding Value Lists
          philmodjunk

          Can you give an example of the actual list of values and what value you want to exclude?

          I can't tell from your post what criteria should be used to exclude that value.

          Are you just excluding the value from this field or are you using the data in this field to control what appears in your report and you need to make sure this value does not affect that process?

          What format does this field take on your report? Could it be a check box group on that report? (There may be a way to use a slightly different value list on the report layout to hide the value you want to keep out.)

          • 2. Re: Scripts Question regarding Value Lists
            AnnetteSteinberg

            An example of the list would be

            MIP

            CX

            Dispatched

            I would like to use the data in this field to control what appears on the report.  It's not a check box group, but a value list with a drop-down box. I just removed the ability to edit the list.

            • 3. Re: Scripts Question regarding Value Lists
              philmodjunk

              "There is one value from the list I don't want to show in the Report."

              Which value do you not want to show in the report? MIP, CX, or Dispatched?

              Is omitting this value this always the case or a user specified option?

              What is the relationship between the data in this value list and the records you want to include in your report?

              • 4. Re: Scripts Question regarding Value Lists
                AnnetteSteinberg

                I didn't list all the possibilities - there are ten.  The one I don't want to show is named "Completed".

                It will always be the case for this Report.

                The data in the value list indicates which customers are are at a particular stage of production.

                And, thanks for being soooo patient.

                • 5. Re: Scripts Question regarding Value Lists
                  philmodjunk

                  Does that mean that a field in your report's table contains one or more of the values in this value list?

                  If so, I think you want to perform a find using all the values in your value list except the value completed to pull up the records in your value list. Is that correct?

                  If so, you can add a field to your database with global storage enabled that uses this value list (but excludes complete) and a script can then be set up to use these selected values to find the records that you want.Is this a value list of custom values or are the values drawn from a table in your database?

                  With answers to these last questions, I think we can rough out a script that will work for you.

                  • 6. Re: Scripts Question regarding Value Lists
                    AnnetteSteinberg

                    Does that mean that a field in your report's table contains one or more of the values in this value list?

                    It means that a field in my report table contains one of those (10) values for each Record.

                     

                    • 7. Re: Scripts Question regarding Value Lists
                      philmodjunk

                      Please answer the other questions as well.

                      • 8. Re: Scripts Question regarding Value Lists
                        AnnetteSteinberg

                        If so, I think you want to perform a find using all the values in your value list except the value completed to pull up the records in your value list. Is that correct?

                        Yes, that's correct.

                        If so, you can add a field to your database with global storage enabled that uses this value list (but excludes complete) and a script can then be set up to use these selected values to find the records that you want.Is this a value list of custom values or are the values drawn from a table in your database?

                        They are custom values.

                        Thanks.

                         

                        • 9. Re: Scripts Question regarding Value Lists
                          philmodjunk

                          Here's my take on how to do what I think you want. Let me know if this isn't what you had in mind. I'm assuming you want the user to be able to select one or more values from this value list and then a script uses those selected values to pull up what you want for your report.

                          Define a text field with global storage and format it with a value list. Use a new value list for this field that has all the same values as your current value list except "completed". That way the user cannot specify completed for this report.

                          Here's the script:
                          If [valuecount ( YourTable::YourglobalValueListField ) ]
                             Enter Find Mode [] //clear the pause check box
                             Loop
                                Set Variable [$I ; value: $I + 1]
                                Set Field [YourTable::YourField ; getValue ( YourTable::YourGlobalValueListField ; $I ) ]
                                Exit Loop If [$I = ValueCount ( YourTable::YourGlobalValueListField )]
                                New Record/Request
                             End Loop
                             Set Error Capture [on]
                             Perform Find []
                          End If

                          I may have misinterpreted what you want to have happen with that completed value. If you want to find all records that do not have "completed" in this field, modify the script to make it like this:

                          If [valuecount ( YourTable::YourglobalValueListField ) ]
                             Enter Find Mode [] //clear the pause check box
                             Loop
                                Set Variable [$I ; value: $I + 1]
                                Set Field [YourTable::YourField ; getValue ( YourTable::YourGlobalValueListField ; $I ) ]
                                Exit Loop If [$I = ValueCount ( YourTable::YourGlobalValueListField )]
                                New Record/Request
                             End Loop
                             New Record/Request
                             Set Field [YourTable::YourField ;   "Completed"]
                             Omit Record
                             Set Error Capture [on]
                             Perform Find []
                          End If

                          Note: depending on how unique are the values in this value list, you may need to use either an = or == operator as part of the set field steps that assign find criteria in this script.

                          • 10. Re: Scripts Question regarding Value Lists
                            AnnetteSteinberg

                            Hello,

                            Sorry, I haven't posted back.  But I've just tried the script and I'm doing something wrong, but don't know what

                            Could you pick apart the Set Variable step for me please.  What is [$l; value $l;1]

                            Thank you,

                            • 11. Re: Scripts Question regarding Value Lists
                              philmodjunk

                              For starters, there's a plus sign where you have a semi-colon in your last post.

                              Set Variable [$I ; Value: $I + 1]

                              is a loop counter that increments by one each time the script loops. It is used in GetValue to extract a specific value from the list of values the user has selected in to YourValueListField so that a different selected value is extracted from the field with each pass through the loop.

                              The variable is also used in the Exit If step to exit the loop once the counter equals the number of values that the user has selected in the field.

                              • 12. Re: Scripts Question regarding Value Lists
                                AnnetteSteinberg

                                 Hi Phil, Script is still not working for me.  I've tried used two $$ in front of the variable name, I tried adding an = or == to the Set Field step as suggested, but I get this error message "a number, text constant, feld name is expected here" and nothing seems to work".

                                Any other suggestions?  My script looks like this

                                If [ValueCount ( JPMS::status_prod_status_global)]

                                   Enter Find Mode []

                                   Loop

                                     Set Variable [$$prodstatus; Value:$$prodstatus + 1]

                                     Set Field [JMPS::status_production; GetValue (JPMS::status_prod_status_global; $$prodstatus)]

                                     Exit Loop If [$$prodstatus = ValueCount (JPMS::status_prod_status_global)]

                                     New Record/Request

                                 End Loop

                                 New Record/Request

                                 Set Field [JMPS::status_production; "Completed"]

                                 Omit Record

                                 Set Error Capture [on]

                                 Perform Find []

                                End If

                                 

                                 

                                • 13. Re: Scripts Question regarding Value Lists
                                  philmodjunk

                                  Sounds like you are having trouble with the set field step. This is a common occurrence for new users.

                                  When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Click OK. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                                  • 14. Re: Scripts Question regarding Value Lists
                                    AnnetteSteinberg

                                     I did have problems with that, but then I finally figured it out.  Would it have anything to do with my variable name using two $$ instead of one $?  Should I rewrite the script instead of revising the original?

                                    1 2 3 Previous Next