12 Replies Latest reply on May 29, 2014 10:31 AM by vavo

    What is the best way to have "smart groups"?

    vavo

      Hi,

       

      I am about to create a system that requires "smart groups", and by that I dont mean groups of smart people - even though that will help any business - but groups of contacts/ companies that are based on a search and auto update. For example a group "Active Members in Texas" should always contain all records meeting those criteria.

       

      One way to do that is of course use the "Saved Functions"but I would much prefer it to be scripted so I can build it into the regular user interface. So far I have not found a way to use this function in scripts, I guess it does not exist.

       

      The other way, and that is what I am leaning towards is do the following:

       

      - Let the user define criteria through a custom "Create Smart Group" interface.

      - The criteria get saved in a table "GROUPS".

      - A function creates a SQL query returning the id's based on those criteria.

      - Use GoTo Related Record to show the foundset/ show in virtual list if results are from different tables.

       

      It is a bit of scripting up front but it would do what I need and actually provide quite bit of flexibilty. I have used something similar to filter portals and it works beautifully!

       

       

      Now I am curious if anybody has done it this way, differently, if there is a better way etc.!

       

      Thanks,

       

      Valentin

        • 1. Re: What is the best way to have "smart groups"?
          mikebeargie

          The easiest way to save finds is definitely to train the users on how to save their own finds, it's already built in and works well on a per-user-customized basis.

           

          I once made a "save find" button that would go something like:

           

          Modify last find

          set variable [ $fields ; FieldNames( Get(FileName) ; Get(LayoutName) ) ]

          Loop

             set variable [ $i ; $i + 1 ]

              If [ Not IsEmpty ( GetField ( $fields ; $i ) ) ]

                set variable [ $find ; List ( $find ; "[" & GetValue ( $fields ; $i ) & "] {" & GetField ( $fields ; $i ) & "}" ) ]

              End If

             Exit Loop If [ $i = ValueCount($fields) ]

          End Loop

          Go To Layout [ savedfinds ]

          New Record

          Set Field [ savedfinds::finddata ; $find ]

          Go To Layout [ original layout ]

           

          I think I made that waaaaay back when so I'm fuzzy on the ins and outs of that, probably a more elegant way to do it now. To retrieve the request you would just reverse the data you saved into a find request with a loop of Set Field script steps.

           

          Perhaps you could store snapshot links in containers and retrieve those?

          • 2. Re: What is the best way to have "smart groups"?
            BruceRobertson

            Looks like a small error in setting the $find variable.

             

            Should be:

             

                 set variable [ $find ; List ( $find ; "[" & GetValue ( $fields ; $i ) & "] {" & GetField ( GetValue( $fields ; $i )) & "}" ) ]

            • 3. Re: What is the best way to have "smart groups"?
              mikebeargie

              Thanks, you might also have to concatenate in the table correct? IE:

               

              set variable [ $find ; List ( $find ; "[" & GetValue ( $fields ; $i ) & "] {" & GetField ( Get(LayoutTableName) & "::" & GetValue( $fields ; $i ) ) & "}" ) ]

              • 4. Re: What is the best way to have "smart groups"?
                erolst

                AFAICT, you don't need the TO name – and I suggest calculating the required bits only once, instead of several times, i.e.:

                 

                Modify Last Find

                Set Variable [ $fields; Value:FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]

                Loop

                  Exit Loop If [ Let ( $i = $i + 1 ; $i > ValueCount ( $fields ) ) ] // Iet's take a pass on that one …

                  Set Variable [ $curField; Value:GetValue ( $fields ; $i ) ]

                   Set Variable ( $curVal ; GetField ( $curField ) ]

                  If [ not IsEmpty ( $curVal ) ]

                    Set Variable [ $find; Value:List ( $find ; "[" & $curField & "] {" & $curVal & "}" ) ]

                  End If

                End Loop

                • 5. Re: What is the best way to have "smart groups"?
                  mikebeargie

                  six to one, half dozen to the other at this point.

                   

                  I think I did this back in FM8.5, training users to save their searches or snapshots is much easier.

                  • 6. Re: What is the best way to have "smart groups"?
                    erolst

                    Mike Beargie wrote:

                    six to one, half dozen to the other at this point.

                    Well, you asked …

                     

                    Anyway, I try to avoid repeating myself (if I may repeat myself on that point!), …

                    training users to save their searches or snapshots is much easier.

                    … and I'd rather write code; scripts can be made more intelligent and better behaved than the average user.

                    • 7. Re: What is the best way to have "smart groups"?
                      vavo

                      Thanks guys! This looks a lot better than what I had in mind, I will give it a test run today.

                       

                      Obviously it would be easier to have every user know their filemaker, but that is just not the case here and therefore I agree with erolst:

                       

                      … and I'd rather write code; scripts can be made more intelligent and better behaved than the average user.

                      • 8. Re: What is the best way to have "smart groups"?
                        vavo

                        When recalling the saved find using Set Field By Name seems to require fully qualified fieldnames, i. e. table::fieldname. FieldNames () only lists the full name for fields from related tables, not the base table of the current layout.

                         

                        To solve this problem I added a little function in the set $curField Variable step:

                         

                        Let ( [

                                  field = GetValue ($fields; $i);

                                  result = If (           PatternCount ( field; "::") = 0;

                                         Get (LayoutTableName) & "::" & field;

                                                                               field)

                                  ];

                                  result

                        )

                         

                        And now it works.

                        • 9. Re: What is the best way to have "smart groups"?
                          mikebeargie

                          It's good to hear the process at least was pretty clear. Didn't realize that would ever be useful to anyone at all.

                           

                          I heard tell of someone extracting and saving the contents of the XML language in a .fmpsl shortcut link, there might possibly be an easier way to gather and "save" the most recent find in that fashion. If you explore and go that route, please feel free to write a post about your progress!

                          • 10. Re: What is the best way to have "smart groups"?
                            BruceRobertson

                            A snapshot link only contains record IDs. (plus layout; and sort order)

                             

                            It contains no find criteria.

                            • 11. Re: What is the best way to have "smart groups"?
                              mikebeargie

                              must have been something else I was thinking of then.

                              • 12. Re: What is the best way to have "smart groups"?
                                vavo

                                After testing what you suggested yesterday I decided to give my origina idea a shot this morning. Since the planing looked very promising I actually implemented it and I love it. Using ExecuteSQL as the key function I did not have to use any loops and no layout changes at all, in addition I can provide users with a visual query builder similiar to what they are used to from ACT!.

                                 

                                Here is what I ended up doing:

                                 

                                the table CRITERIA contains the fields you see in the portal below:

                                Operator | parenthesesOpen | fieldName | value | parenthesesClose

                                queryBuilder.png

                                 

                                in the table CRITERIA I use a custom functio to create a line I can use in SQL:

                                n cf_sqlLine (field; value):

                                 

                                Let ( [

                                      value = " LIKE lower ('%" & value &"%')";

                                               result = "lower ("& field &")" & value

                                               ];

                                               result

                                          )

                                 

                                i. e. cf_sqlLine ("firstname"; "valentin") would return "lower (firstname) LIKE lower ('%valentin%')"

                                 

                                a calc field adds the parentheses (for nested queries) and operator so I would have a the complete line for my SQL query.

                                 

                                To run the query I set a foreign key field using this function:

                                 

                                Let ([

                                          query = "SELECT id

                                                              FROM contact

                                                              WHERE " &

                                                                   List ( groups_CRITERIA__contact::ns_sqlLine ); // gets the lines from the function above

                                          result = ExecuteSQL (query; ""; "")

                                          ];

                                result

                                          )

                                 

                                and thats it.

                                 

                                I use a portal to display the results, you can of course simply use GTRR to create a foundset etc. As I said, it is fast and super easy to create complex queries without having to understand Filemaker foundsets.

                                 

                                I have to say that the concept you introduced earlier helped me to make this work so nicely, so thanks!

                                 

                                Best,

                                 

                                Valentin