12 Replies Latest reply on Feb 9, 2015 11:06 AM by AliSheikh

    How to filter out fields with identical value in a portal?

    JanPeterAxelsson

      Title

      How to filter out fields with identical value in a portal?

      Post

      Hi,

      I have a small basic problem of Í hope general interest.

      I would like to know how to filter-out repetitive field values when a group of records are shown in a portal. The purpose is to show the variation of values in a field (or several fields).

      Consider the following toy example. My database consists of two tables A and B and one record in A can be related to several in B. The record B consists of say 3 fields. Consider a post A1 in A that is related to B1, B2 and B3 in B. The first two fields in B are identical in B1-B3 but differ in the third field. Now we have a layout showing the fields of A and a portal showing only the two first fields of B.

      When we look at the layout (and have done find A1) then we see the content of the fields of A for A1 and we see the two fields of B repeated for B1-B3. Now I want this layout to only show not-repeated fields. Thus fields that are repeated should only be shown once. How can I do this?

      Thanks

      Jan Peter

        • 1. Re: How to filter out fields with identical value in a portal?
          philmodjunk

          Can't quite follow that. Is this what you mean?

          You have the following records listed in a portal:

          Apple
          Orange
          Apple
          Kiwi
          Orange

          And you want to filter it to get:

          Apple
          Orange
          Kiwi

          It's possible, to do this, but not a simple thing. some approaches require a script, or possibly a calculation field that flags subsequent duplicate value records for exclusion in a portal filter expression (FileMaker 11 only), but I'm not sure if this is what you want here.

          • 2. Re: How to filter out fields with identical value in a portal?
            JanPeterAxelsson

            Yes, that is waht I mean.

            I looked in the manula for functions and the function "list" seems to do this on a repeated field with repetition of identical values. I do not understand how to use "list" and I am not sure it could used in a portal either. There might be other ways. I thought one could use the "internal" filter in the portal but it was not obvious to me how. A more "external" script might be needed. I am looking for a general and simple solution.

            Thanks

            Jan Peter

            • 3. Re: How to filter out fields with identical value in a portal?
              philmodjunk

              One solution is not to allow duplicates in the first place. This may or may not be a good idea.

              You can also define a conditional value list of your portal table's values. The value list will automatically drop out duplicate values.

              Your value list would specify values be drawn from the portal table occurrence and you'd specify only related values starting from your layout's table occurrence. (Table occurrences are the "boxes" in your relationhship graph).

              A calculation field set to return text could then be defined as:

              ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotes" )

              Set this field to be several lines of text tall and with a scroll bar and you'll have a read-only list of your values with duplicates automatically dropped out.

              Notes: Get ( FileName ) enables you to keep the function from breaking should you rename your file. If you should rename your value list, your calculation will become blank as this function then returns nothing.

              • 4. Re: How to filter out fields with identical value in a portal?
                JanPeterAxelsson

                I could perhaps using some script "omit" records that have the same value for fields of interest. Here it does not make sense to eliminate them. After all the records are not identical for all fields.

                Your idea of using "conditional values" sounds interesting. However you write to brief and I cannot follow what you say. After all I have not used Filemakter that long either.

                1) I cannot find the "table occurence are teh boxes in yyour relationship graph"

                2) I guess the operatins: Get(FileName); YourValueListNameQuotes; should be done in the portal, right?

                3) What FileNames are we talking aobut?

                Please explain it all better, it seems to be in the right direction.

                Jan Peter

                • 5. Re: How to filter out fields with identical value in a portal?
                  philmodjunk

                  Before I explain in more detail. Does a read-only list work for you? This is a pretty simple way to get a unique list of terms from a related table, but it doesn't work if you need to edit the values directly on this layout.

                  • 7. Re: How to filter out fields with identical value in a portal?

                    Filtering duplicates in a portal field is quite useful. Presently I am using this idea by creating a second table where I import the first and then run a script to delete all of the duplicates. I then use the second table in the manner you describe. However, this is not the best idea and your post set me to thinking about this.

                    First create a calulated field that determines if the value has duplicates in other records. This requires duplicating the TO and then...

                    Then create a calculated field that will show the id field value only for the lowest numbered id based on the duplicated calculated field above.

                    Change your original link to the second field and this should exclude all of the duplicates since they will have no value in that field.

                    I will try this idea in my file and find out what exactly is needed.

                    • 8. Re: How to filter out fields with identical value in a portal?
                      philmodjunk

                      Jack's method of using a calcuation field and a relationship can also be used with a portal filter in FileMaker 11 to filter out the duplicate values.

                      While fairly simple to set up. My approach limits you to a list of values from a single field and is read-only. Thus Jack's approach may be a better way to do this.

                      To expand on my original suggestion:

                      1) I cannot find the "table occurence are teh boxes in yyour relationship graph"

                      Open Manage | Database | Relationships. This is your relationship graph. Each box on this screen is a table occurrence. While these boxes might have the same exact name as the tables listed in Manage | Database | Tables, they aren't quite the same thing and we distinguish between the two by calling the boxes "table occurrences" and the tables in the Tables tab "data source tables".

                      To learn more about table occurrences and how you can use them: Tutorial: What are Table Occurrences?

                      2) I guess the operatins: Get(FileName); YourValueListNameQuotes; should be done in the portal, right?

                      There's no portal involved with the approach I've suggested. Instead, you set up a multiple line calculation field that lists one instance of each value drawn from the related portal table. You can even format this field with a scroll bar like you would with a portal--you just can't modify the values listed.

                      After you've set up your conditional value list, open Manage | Database | Fields. Enter a name for your calculation field. Select Calculation as its Field type and click Create. Enter the following expression, but put the name of your value list in place of "yourvaluelistinquotes":

                      ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotes" )

                      Select Text as the return type for this calculation in the drop down in the lower left corner of this dialog.

                      To learn about how to set up conditional value lists: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list/session/L3NpZC9ocWkzNTdlaw%3D%3D

                      3) What FileNames are we talking aobut?

                      Only one file name. This is the name of your Database file. If you named your file: JansDatabase, you could use 

                      ValueListItems ( "JansDatabase" ; "YourValueListNameInQuotes" )

                      But if you later changed the name of your file to PhilsDatabase, the ValueListItems function would stop working because it is still trying to find a file named JansDatabase. Get ( FileName ) is a function that returns the name of the current file, so by putting it in place of "JansDatabase", you now have a function that will continue to work even if you change the name of your database file.

                      • 9. Re: How to filter out fields with identical value in a portal?

                        I have solved the problem. I will upload a demo to my website later today.

                        It's rather easy:

                        Add two calculated fields to the child, create one duplicate to of the child and filter the portal.

                        • 10. Re: How to filter out fields with identical value in a portal?

                          Here's a Jing link so you can see a little movie of it in action. I only used a single word but the filter could be set to capture the first word or two.

                          http://screencast.com/t/FER2pVr23Lqy

                          • 11. Re: How to filter out fields with identical value in a portal?
                            JanPeterAxelsson

                            Jack,

                            Thank you for your work!

                            I have watched you demo and I think you have got it, but I cannot fully understand how you do it or if we have some missunderstanding.

                            I guess the example actually starts with entries  Mac OS, PowerBook, iPad etc, and I should neglect the first few seconds. (I hear no sound from your demo).

                            You enter duplicate records, what I can see. My point is that the method should apply when we  have duplicate values for fields shown in the portal of otherwise different records.

                            It seems that the calculation of the help column second from left is done when you put the cursor above the right column. Do you start a script by pressing a button there or what?

                            I also see that you can create new records in the filtered portal, which might be useful, but in my original problem it does not really make sense since the portal does not contain all fields.

                            I really think that your solution might be what I am looking for, but I cannot really follow how you do it. Could you simplify the example (and only keep the necessary fields and layouts and scripts) and post it here on the forum. Or alternatively provide a link where I can download your example?

                            Thanks

                            Jan Peter

                            • 12. Re: How to filter out fields with identical value in a portal?
                              AliSheikh

                              I was interested in Jack's solution but it is not descriptive. Can anyone please elaborate more on how he constructed the solution? 

                              I can't figure out what calculations he used for the fields from his description and what fields were used to link the occurances.