6 Replies Latest reply on Mar 29, 2011 5:01 AM by HowardChernick

    Creating a list of values from table



      Creating a list of values from table




      I am currently learning how to use FM Pro.  I have worked with Visual Basic over the past 14 years and can replicate the following with no problem in VB but have no idea how to do theis in FM.

      I have a table that has the following fields, SetName, TotaCards, TotalValue....

      I want to have a screen which show a listbox of all the Setnames in my table.  Once the list is displayed, i would like to select an item in the list and display any relevant info about that selection.

      It would be great if someone could get me started on atleast setting up the initial listbox.

      thank you.


        • 1. Re: Creating a list of values from table

          You can set up a portal to function as a list box. (A list box being a box that lists all the options, and clicking a listed option selects that option for some action such as what you describe.

          Is SetName a unique value in the table or do you have multiple records in the table with the same set name?

          The short form of the answer is to set up a self join with the X operator so that any record in your table matches to all records in the self join's table occurrence. Then set add your portal to the self join's table occurrence and format the setName field as a button to run a script that finds the record with the same set name. No point in adding detail until I get an answer about the possibility of duplicate names.

          If Set Name is not unique, then additional effort is needed to filter out the duplicate set names.

          • 2. Re: Creating a list of values from table

            SetName will be a unique record.

            • 3. Re: Creating a list of values from table
              1. In Manage | Database | Relationships, create a self join relationship like this:
              2. Click on the box for your table to select it, then click the button with two green plus signs to create a new table occurrence of the same data source table.
              3. Drag from SetName to SetName in the two boxes to link them, then double click the new relationship line this created to change the = operator to X.
              4. Click OK to Dismiss Manage | Database and to save your changes.
              5. Go to your layout and use the portal tool to draw a rectangle on the layout. You may want to enable the scroll bars option so that you can see all set names even if there are more than will fit in your list box. Add just the SetName field to the portal row.
              6. Write a script, (I'll refer to your layout's table occurrence as YourTable and the portal's table occurrence as YourTable2).
              7. Set Variable [$Setname ; YourTable2::SetName]
                Enter Find Mode[]//clear the pause check box
                Set Field [YourTable::SetName ; $SetName]
                Perform Find[]
              8. While in layout mode, select the SetName field in the portal row and use Button Setup... from the format menu to turn the field into a button and set it to perform the above script.
              9. Use Set Tab Order... in the layouts menu to remove this field from the tab order.
              10. Add the "detail" fields you want to show data when you click a set name in the portal and you are done.


              If "Table Occurrence" is a new term, you may want to read this article:  Tutorial: What are Table Occurrences?


              • 4. Re: Creating a list of values from table

                Thanks for your help. I believe i am on the right track now but i can't get the script to work.  I cnahged the table names to match the script that you sent just to make it easier.


                 Set Variable [$SetName; Value:YourTable 2:SetName]

                Enter Find Mode[]

                Set Field [YourTable::SetName[$SetName]]

                Perform Find[]

                 The message:  The provided find criteria is not valid.

                • 5. Re: Creating a list of values from table

                  You have $SetName in the repetition box instead of as the calculated result. That's why you have an extra pair of brackets there.

                  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.



                  • 6. Re: Creating a list of values from table


                    Thanks so much for your help.