11 Replies Latest reply on Jan 8, 2013 10:50 AM by JeffreyPylant

    DIsplaying a list of unique values from a find

    user18614

      Title

      DIsplaying a list of unique values from a find

      Post

           When searching through a table (called KPI - see below), there will be multiple entries for each date.  There is a extra field, called committed, that gets flagged when a record is marked as "complete".  Basically, I want to display of list dates where there are records that are not committed.  From there, the user will select a date, which will set the g_Selected Date global field in a table higher up in the relationship, and take them to the layout for that date.  The global field and the layout is already in use and working fine.  I just don't know how to show a list of "unique dates" from the KPI table for the selected client where the committed field is 0.  I'm also looking for suggestions on the easiest way to do this - a value list or a portal.  Of note - this database is plublished via IWP.

            

           Table Structure:

           Client (parent)
           - client ID
           - g_Selected Date (global field to pass date to another layout)

           Department (child; many depts linked to one client)
           - dept ID
           - client ID (linked to client table)

           KPI (grandchild; many KPIs to one department, one for each date/department ID)
           - dept ID
           - KPI date
           - KPI 1
           - KPI 2
           - Committed (0 or 1)

        • 1. Re: DIsplaying a list of unique values from a find
          philmodjunk

               Instead of a value list or a portal, I'd consider a calculation field with ExecuteSQL (if using fileMaker 12) or a list view layout using sub summary layout parts and no body layout part to list one instance of each committed date.

          • 2. Re: DIsplaying a list of unique values from a find
            user18614

                 I like the idea of the SQL statement (although I'm a bit rusty).  However, how would that calculation field be translated into a value list that can be selected from a drop down so I can pass the date as a variable?

            • 3. Re: DIsplaying a list of unique values from a find
              philmodjunk

                   What you can do with the calculation field is make it multiple rows of text tall and use the onObjectEnter script trigger to perform a script that uses the cursor location--which is determined by your mouse click, to determine which row of text in the calculation field was clicked. This does not work if you have to give the field a scroll bar.

                   On the other hand, a return separated list of values produced by ExecuteSQL could be defined in a table of just one record and then your value list can list the value from that one record in that one field.

                   The simplest method is to use a list view layout of sub summary layout parts to condense your list down to just unique values. A button in the sub summary part can then be clicked to select the data for that row of data.

              • 4. Re: DIsplaying a list of unique values from a find
                user18614

                     Hmm...  unfortuantely, since I'm doing this as an IWP, I can't use script triggers.

                • 5. Re: DIsplaying a list of unique values from a find
                  philmodjunk

                       Nor can you use a summary report with sub summary layout parts.

                       That does leave the 2nd option, however.

                       And it's not impossible to use a portal for this.

                       Please, always tell the forum when you are needing this to work with IWP. There are many limits imposed by IWP that people who are trying to help you need to know so as not to waste their time suggesting solutions not compatible with IWP.

                  • 6. Re: DIsplaying a list of unique values from a find
                    user18614

                         It was noted in the original.  If I use the ExecuteSQL option and have a table with just one record, will the Value List display the entries as multiple values that can be selected?

                         I also just stumbled upon this possible solution that I'm trying to wrap my head around...but I'm not sure it'll work since I have parent - child - grandchild and the relationship I need is to show unique dates from the grandchild for the selected parent.

                    http://fmforums.com/forum/topic/71906-getting-more-out-of-filtered-portals-3unique-values/

                    • 7. Re: DIsplaying a list of unique values from a find
                      user18614

                           This worked...although I'm not sure I understand how sicne I don't understand the statement.  But, I replaced the fields in the filter statement with the fields in our tables and it worked.

                           Now, to research it more to understand what is happening.

                      • 8. Re: DIsplaying a list of unique values from a find
                        philmodjunk

                             Apologies for missing that statement in your original post.

                        • 9. Re: DIsplaying a list of unique values from a find
                          user18614

                               Not a problem.  I realized it was buried in the post.  In the future, I'll note it at the top of the post.  If you missed it, others can easily miss it as well and I don't want to make the people who are helping me mad!

                               Now, I have it partially working.  It will display a list of unique dates, which is great.  But, I only want to display dates that aren't committed (KPI Table, committed=0).  However, I don't know how to modify this filter to include that.  If you have any suggestions, I would appreciate it.  Of note - there is one extra field in the KPI table I didn't mention (didn't seem relevant).  But, there is a KPI_ID field which is just a serialized number.

                               

                                    Let ( [
                                    indexValues = List ( KPI::KPI_ID ) ;
                                    searchValues = List ( KPI::Date_of_Shift ) ;
                                    index = ValueCount ( Left ( indexValues ; Position ( ¶ &  indexValues  & ¶ ; ¶ &KPI::KPI_ID & ¶ ; 1 ; 1 ) ) ) ;
                                    prevValues = LeftValues ( searchValues ; index -1 )
                                    ] ;
                                    IsEmpty ( FilterValues ( KPI::Date_of_Shift ; prevValues ) )
                                    )

                          • 10. Re: DIsplaying a list of unique values from a find
                            philmodjunk

                                 Using the example that you have posted, you would need to use a relationship that only matched to KPI records where committed = 0

                                 you might use a field constZero with the number 0 as it's sole term as that additional match field in the relationship.

                            • 11. Re: DIsplaying a list of unique values from a find
                              JeffreyPylant

                                   Hi. I'm an amateur user of Filemaker (only 15 years or so....) - so, I'm sure you guru's can improve upon the answer I _found_ somewhere else on the net.  I have a fairly large list of women's names.  Imagine how many "Sue" and "Susan's" and "Deb" and "Debbie" and Debi's there were.  Hundreds of records with the first name of "Sue", etc.  

                                    

                                   I wanted to "find unique FIRST name records ONLY".  

                                    

                                   The trick is to create a  SUB-SUMMARY field - and tell FM to create that Sub-Summary when sorted by first name.... THEN choose the export command AND during that export dialog, tell it to GROUP by First Name.  Voila!  ONLY those unique first names will be exported.