10 Replies Latest reply on Aug 7, 2015 9:58 AM by CoraWoolsey

    Trying to list values and then omit redundant values

    CoraWoolsey

      Title

      Trying to list values and then omit redundant values

      Post

           Hi there,

           I am trying to set up a calculation field that gets values from another field in related records and then omits duplicate values. For example, in a field called "Shape," I want to access the values from the field "Vessel" in six related records that match this record. These values are: "square"; "square"; "semi-square"; "round"; "square"; and "round." In my "Shape" field, I want to appear the following: "square,semi-square,round". Can anyone help me with the calculation that would achieve this goal?

           Thanks so much!---------Cora

        • 1. Re: Trying to list values and then omit redundant values
          philmodjunk

               What version of FileMaker are you using?

               In FileMaker 12, ExecuteSQL with the DISTINCT keyword can produce such a list

               In older versions, a value list can be defined that lists only values from the related records and then the ValueListItems function can list each value from the related records a single time.

          • 2. Re: Trying to list values and then omit redundant values
            CoraWoolsey

                 Filemaker 10. I started to try what you suggest, but I think I need to be walked through the steps. So far, I have no been able to make it work.

            • 3. Re: Trying to list values and then omit redundant values
              philmodjunk

                   Since I don't know the details, I'll have to make up some names for fields and tables and you'll need to then substitute actual names from your file:

                   Let's say you have this relationship:

                   LayoutTableOccurrence::PrimaryKey = Shapes::Foreignkey

                   The typical "one to many" relationship needed for a portal...

                   You can then set up a conditional value list that only lists values from the related Shapes records for each record in LayoutTable:

                   Open Manage | Value Lists and create a new value list. Name it "Shapes". Select the "use values from a field" option. This opens another dialog box.

                   In the drop down at the top left of the dialog, select Shapes. Click the ShapeName field from which you want to produce this list of unique values.

                   At the bottom of the dialog, select "include only related values, starting from LayoutTableOccurrence.

                   Now define an unstored calculation field in LayoutTable, with LayoutTableOccurrence selected in the "context" drop down at the top of the Specify Calculation dialog box. Select Text as the return type.

                   enter this expression:

                   Substitute ( ValueListItems ( Get ( FileName ) ; "Shapes" ) ; ¶ ; ", " )

                   ValueListItems will separate each unique value with a return, producing a vertical list. I've used substitute here to replace the return with a comma and space to produce a horizontal list as you have requested.

              • 4. Re: Trying to list values and then omit redundant values
                CoraWoolsey

                     This works great, thank you. I have taken the principle and applied it to numerous fields. I would be interested to know what each part of that calculation means/does, but if you don't have time to explain, I am just super glad I was able to achieve the effect I was looking for.

                     Thanks again.-----------------------Cora

                • 5. Re: Trying to list values and then omit redundant values
                  philmodjunk

                       Each of the functions used are documented in FileMaker help. I suggest researching them there first, then feel free to post back here with questions about any specifics that still aren't clear to you. (Unless you know how/why it's set up this way, you may have trouble using it elsewhere...)

                  • 6. Re: Trying to list values and then omit redundant values
                    CoraWoolsey

                         Oh, I've read the help document a few times and refer back to it often. However, I get the feeling that there is an underlying programing logic that I don't know and that makes it hard to figure out how to make the functions work. For instance, I have only just understood that different functions are usually nested like the one above. This is nowhere apparent in the help document. Thank goodness for this forum!

                    • 7. Re: Trying to list values and then omit redundant values
                      philmodjunk

                           Nesting functions one inside another is basic mathematical notation. In math class, you might write sin ( cos theta ) as a way to take the sine of the cosine of the angle theta. You would evaluate the expression from the inside out. The same is true with calculations expression in most computer programming languages and also with Database Systems such as FileMaker.

                      • 8. Re: Trying to list values and then omit redundant values
                        CoraWoolsey

                             Perhaps you could recommend something for me to read on basic programming notation. I have neither a background in programming nor in math, although what you just said makes sense to me according to what I remember from High school math.

                        • 9. Re: Trying to list values and then omit redundant values
                          philmodjunk

                               Just explaining why so many systems don't document this one aspect of calculation expressions--they tend to assume that you already know this detail. I don't really have any specific recommendations that I can make specific to calculation expressions.

                          • 10. Re: Trying to list values and then omit redundant values
                            CoraWoolsey

                            Hi there,

                             

                            I was wondering if you (or anyone) could help me expand this calculation a little bit. I happen to use this calculation all the time in my rapidly expanding database, but now I want to count how many of each value occurs in the related field. So to take the example from above, the new field would read: "square (3),semi-square (1),round (1)" or anything that conveys the same information. Conversely (or maybe as well), I would like to see the values sorted (descending) by how many of each occurs in the related field. Is either option possible?

                            Thanks in advance.----------------------Cora