6 Replies Latest reply on Nov 19, 2010 10:22 AM by PaulaS

    Consolidating values in value list related from table

    PaulaS

      Title

      Consolidating values in value list related from table

      Post

      I have a value list based on a field from a related table. I have 9 values that ought to be displayed, but because the characters for two of these values are mostly the same, it only shows 8 (at least my first test showed this is the reason that one value was omitted). How many characters does FileMaker check before it consolidates two values?

      The values are admittedly long.

        • 1. Re: Consolidating values in value list related from table
          philmodjunk

          Are the field types number or text? (They should be text.)

          How many characters long are your values?

          Is this for a sincle column value list or a 2 column value list? (Certain options for 2 column value lists can exlcude values from the list.)

          • 2. Re: Consolidating values in value list related from table
            PaulaS

            text fields

            Value length is 203. There is a small difference between the two values but does not change the number of characters.

            Not sure what a 2-column value list is.

            • 3. Re: Consolidating values in value list related from table
              philmodjunk

              I think 100 characters is the most that FileMaker indexes for a value list. Thus, those value lengths are indeed too long if the last characters are the only ones that are different.

              You might be able to reverse the order of your string to put the part that differs first.

              You might be able to put just the part that differs in it's own field for use in the value list, with scripting or lookups to copy the full value into a different field.

              You can also use that two column value list to make a distinction here.

              1. Define a second field and put the last few characters of your string in this field, keeping your original field unchanged.
              2. In manage | Value LIsts.... , Set up your value lists using the specify field option.
              3. In the Use values from field 1 column, select your original field.
              4. In the Use values from field 2 column, select your new field.
              5. Select the "Sort values using second field" option.


              As long as you sort by your second field, you'll get each value and be able to select them.

              Note: 203 characters makes for a very long value for the user to select from in a value list. Why is it so long? (maybe there's an alternative approach that won't require such a long string in a single field.)

              • 4. Re: Consolidating values in value list related from table
                PaulaS

                I think I'll just have them change one or two things in their verbiage to make them separate from each other. 

                Yes the values are really long. These are for our legal department, making comments on label compliance of packaging design. The word "legal" should tell you why they're so long. They want their comments to be consistent among the group. Thus, common verbiage. 'Nuff said.

                • 5. Re: Consolidating values in value list related from table
                  philmodjunk

                  Then they can select a value such as "standard disclaimer text" in the value list and the system can use a looked up value option on a text field to copy in the actual text. That will make for a simpler value list as well as solving the indexing problem.

                  If they need to see and read the actual text, you can also display the text in a a scrolling portal where clicking a button in the portal row selects that text and copies it into the designated text field. (Or they can even just Drag and drop the text from the portal.)

                  • 6. Re: Consolidating values in value list related from table
                    PaulaS

                    I'm going with the 2-column approach, only showing the values from the 2nd column. I'll have them choose their own key values to shorten their lists. I'm sure they'd rather see them that way as well.