8 Replies Latest reply on Aug 7, 2015 9:30 AM by KerryMiller

    Performing Complex Finds



      Performing Complex Finds


      I am trying to implement a couple of streamlined find features to our current Filemaker system. I am a graphic Designer/ CAD artist, but have only limited knowledge of scripting/programming.

      Find #1:

      A button that asks for the value from a set list, then finds only those Records that have that value.

      I can set it to find a specific value, but I have not been able to have it ask what value you want to find.

      It would also be nice if you could have it select a range from a Custom Value List


      Find #2

      We use Filemaker to track products development status. One of the properties we track is a products release and discontinue "season". For example a product might be released in the Fall 2014, and drop from our catalog in the Spring 2016.

      I would like to be able to perform a Find that results in all products currently available for a given season.

      The find would work something like this:

      User selects a "Season"

      Results would be: Show All Records with a "Release Season" less than and equal to the selected "Season", AND have a "Discontinue Season" greater than or equal to the Selected "Season"

      It should be noted that both "Release Season" and "Discontinue Season" use the same Value List.

      I understand that this may not be possible with a Text Value List, and that is OK. We are open to modifying our data to allow this functionality if needed.

        • 1. Re: Performing Complex Finds

          #1) Set up a global field with a value list for selecting a value. A script can then use the value entered/selected in this field to build a find request and perform the find. You can just put the field on your layout formatted with the value list, put it inside a popover button, use a custom dialog with an input box (but then no value list is possible) or open a new window to a different layout set up with the global field and value list format.

          #2) You need a number field for the Release and Discontinue Season fields as you are comparing numeric values to each. Like #1, you can specify this in a global field. I named it gSeason. The script would look something like this:

          Enter Find Mode []
          Set Field [YourTable::ReleaseSeason ; "<" & YourTable::gSeason ]
          Set Field [YourTable::DiscontinueSeason ; ">" & YourTable::gSeason ]
          Set Error Capture [on]
          Perform Find []

          For more examples of scripted finds of this type, see: Scripted Find Examples

          • 2. Re: Performing Complex Finds


            FileMaker Pro stores dates as the number of days since January 1, 0001
            Home > Designing and creating databases > Creating a database > Defining database fields > Defining date fields

            A calculated field created from Season and replacing Spring2015 with March 21, 2015 would make things easier.

            • 3. Re: Performing Complex Finds

              Hmmm. Good catch David. I missed the fact that we have more than a year here. Hence the reason for using an actual date field for the season fields.

              • 4. Re: Performing Complex Finds

                As I said, both Release Season and Discontinue Season fields are using the same global value list, just a text based one. Under the DATA tab of the Inspector I have "Popup Menu" selected for data entry which uses values from "Season". So it sounds like I was on the right track at least.

                Currently both season fields are "Text" containers. Would changing the Release and Discontinue Fields from "Text" containers to "Number" containers make this possible?

                @ David: Using a solid date would make things easier, the reality is that "release" date is often subject to change. So for our use we prefer to use the abstract/generic "season" method.

                • 5. Re: Performing Complex Finds

                  We know that, But your text won't work for what you want to do for this find. As David pointed out, these should be date fields. You can select a season, but an auto-enter calculation can use the selected season to produce a date. That date will then work for a scripted find using inequality operators.

                  Let ( [ s = Leftwords ( YourField ; 1 ) ;
                             y = RightWords ( YourField ; 1 )
                           ] ;
                           Case ( s = "Spring" ; Date ( 3 ; 1 ; y ) ;
                                        s = "fall" ; Date ( 9 ; 1 ; y ) ;
                                        s  = "winter" ; Date ( 1 ; 1 ; y ) ;
                                        s = "summer" ; Date ( 6 ; 1 ; y )

                  You can of course use different month values than my rather arbitrary choices.

                  • 6. Re: Performing Complex Finds

                    Ok. So using that script I could "convert" our desired display text into a "Date" that Filemaker can use to perform the Find I'm looking for? (If I understand correctly it assigns the date value of "March 1st" to "Spring")

                    Pardon my ignorance, but where would this script be applied? Would it be a Script Trigger (OnObjectEnter?) for the gSeason field?

                    • 7. Re: Performing Complex Finds

                      It's not a script. It's an auto-enter calculation you would put on a hidden date field. ("Hidden" in the sense that you do not need to put this field on any layout.) Your user selects your value in your text field ("YourField" in the example calculation) and the date field automatically updates with a corresponding date. Your scripted finds then ignore the text field and specify search criteria in the date field instead.

                      • 8. Re: Performing Complex Finds

                        Thanks for the Explanation!

                        I actually figured it out late last night and didn't have time to post a reply.

                        For anyone else who finds this thread trying to do something similar, there's what I did:

                        I kept my current "season" fields as is, instead I added four new fields: FilterSeason, FilterNumber, ReleaseNumber, and DiscontinueNumber.

                        The Filter* fields are set up as "global" values in the "storage" section of the field option menu.

                        The *Number Fields are number fields defined by a Calculated Value generated using the "Let" script shown.

                        We then had to go though and re-commit all Release and Disontinue season fields to generate the required numbers.

                        I then added the Filter Season field to the header of our main list view, and added a script trigger to "OnObjectModify" that runs the filtered Find script shown in the original reply. 

                        It may not be "best practice", but it works.

                        Thanks for your help!