2 Replies Latest reply on Jul 21, 2011 8:24 AM by philmodjunk

    Getting the 2nd column from a Value List



      Getting the 2nd column from a Value List


      I have a global parameters table which I'm using to select criteria for a Find in order to select a set of records to include in a report. One of the fields in the global table is a Category Code which uses a Value List defined to list the 2nd field (name) from the Category table. User chooses a Category from the value list and the Category Code is selected for inclusion in the Find. The Find gets the correct records, ie. only those with the chosen Category Code. So far, so good.

      But, what I also want is to get the Category Name when the user selects a Category, so that I can use it in the title of the report, eg. "Listing Items for category xxxx". Seems a simple requirement - the Category Name is obviously related to the Category Code and it is also displayed in the value list. But I cannot find any way to do this; I cannot see any function to either get the value of the second column (Category Name) from the value list or to lookup the Category Name from the Category table using the Category Code. I have tried being tricky and creating a relationship between the global table and the Category table but that does not work either.

      I am quite new to FileMaker. In Microsoft Access, this simple, common requirement is addressed through an inbuilt function which allows you to get values from the second (or third, etc) column of a value list (for the chosen value). In Access I could also use a database lookup function to get the name using the code. But I cannot find any equivalent functions in FileMaker.

      My data is in a MySQL database so I guess I could write a bit of Applescript to query the MySQL database to get the Category Name using the Code selected from the value list, but that seems like ridiculous overkill for such a simple requirement.

      What am I missing / doing wrong?

        • 1. Re: Getting the 2nd column from a Value List

          You can put the related field on a layout.

          There are a number of visual tricks to hide the code beside or behind the related field.

          One way is to add the arrow indicator to your field  to hide/show the value list. Then shrink the field to just be  the arrow and place it next to the related field for the description. I use this technique on data entry layouts on a report layout you can simply place the related fields on the layout anywhere.

          It looks like you have been able to establish table occurances for your MySQL data in your FMP database so adding a relationship between the tables shouldnt be a problem.

          • 2. Re: Getting the 2nd column from a Value List

            You can place a copy of your category code field in your report header formatted as a pop up menu with a version of your value list that shows only the 2nd field. By setting a line width of zero for the field's borders you can conceal the standard "beveled block" look so that it displays like a normal field, but you'll see the 2nd field category name in place of the code.

            Also, your relationship to this value table should have enabled you to just add the Category Name field from that table occurrence directly to your layout.