2 Replies Latest reply on Apr 29, 2016 12:21 AM by alangodfrey

    finding in a value list or another method


      I used to think myself a little Filemaker savvy then I run into real silly issues that seem like it should be easy.


      Here is my dilemma/situation:


      It is simply a sales system that I am pulling line items into that are from my inventory database. I can easily have a value list pop up and show the items in my inventory database but what I want is to be able to have a way to start typing what I want like the value list but have it look in any portion of the field or fields.  Unfortunately it currently only starts to look at the first item , so if I have lets say:

      sku field and I start typing in my value list the sku like B-10-1, it does go to that eventually and I can select it but I want to be able to type in any portion of what I know lets say 1" Riser and it shows those that I can select from. Even when I have 2 fields that will show sku and item name (B-10-1 1" Riser) it still only goes to the item as you type in exactly what it is, I cannot even just start typing 1" riser it wont go to that, yes I could reverse it but then I can't use the sku.


      So to recap:

      What I want is to be able to type into a field and it will start showing me items that match from my inventory database what I type, sku, partial item name etc. then when I select it, it will pull over the inventory id so I can populate the remaining fields like normal.


      Any help appreciated.



        • 1. Re: finding in a value list or another method

          A method I use is to create a calc field (call it utility_NameSearch or something like that) and use the following calc:


          Substitute ( <theFieldYourReallyWantToSearch> ; " " ; "¶" )


          This method:

          —provides a LIST of ALL WORDS in the <theFieldYourReallyWantToSearch>

          —purpose is to enable searching to find any word in the name, not just the whole field content from the start


          You can then use this field as a value list, or—my preferred method—in association with a portal search where you type into a search field and a portal shows an ever-reducing list of matches.

          • 2. Re: finding in a value list or another method

            I address that problem often - value lists for Catalogue listings tend to be too long and unwieldy.  I always use a filtered portal that lists all the catalogue items.  There is a global filter field above it, with a script trigger (to commit and return to it On Modify) that is the filter for the portal.  You can have a various types of filters:

                 - a simple filter looking for the Catalogue Item starting with the gFilter letters.  This replicates what you have now.
                 - a Patterncount ( gFilterField ; CatalogueNumber ) that will list only articles containing the filter data.

                 - allow the user to enter several filter groups separated by a space, eg 'b ris 10' and filter for the Patterncount of the first word, AND the second word, AND the third word.  As the user adds more data strings (in any order) they will find only items mentioning all of them (eg: B-10-Riser).

                 - you can of course change it to be an OR filter, if that is more suitable (but I've never had a use for that).


            Clicking on the item in the portal then (has a script which) effects the same as selecting the item from a value list.


            You can also sort the portal in a way that might suit the user better than the simple alphanumeric of a Value List - and you can even allow the user to sort the portal dynamically if they sometimes prefer by Catalogue Number, sometimes by Product Group first, etc.