12 Replies Latest reply on Jan 25, 2015 12:59 PM by richardsrussell

    What is the best way to setup an alphabetic lookup?

    pedroble

      I have a table of images, several thousands, and the images are labelled by ... 'aa', 'ab', 'ac',  ....... 'zx', 'zy','zz'  followed by a number.

       

      My question is, what is the best setup to be able to lookup a group of images for example, 'ac' to 'af' ?

       

      Thanks in advance.

        • 1. Re: What is the best way to setup an alphabetic lookup?
          erolst

          If by lookup you mean “find matching records”, you can use a Find with the request 'ac*..af*' in the label field. (Don't type the single quotes.)

          pedroble wrote:

          what is the best setup

          Maybe a filtered relationship and a portal would be better in your case; but that is hard to say without knowing your existing setup & intended workflow.

          • 2. Re: What is the best way to setup an alphabetic lookup?
            pedroble

            Sorry about the delay in replying.

            The fact is that your reply caused me much confusion as to what my question really meant.

             

            Basically I need a setup for other persons not familiar with FileMaker to be able to access a list of matching records

            I started with buttons for each letter a,b,c,.... but soon realised that that would need 26 or more buttons.

            Then as my list got longer I found the need to be more specific aa,ab,ac...

             

            I think I misled you by putting in the 'ac...af' example

            My imediate concern is setting up the 'find', later on I will confront the the finer details

            • 3. Re: What is the best way to setup an alphabetic lookup?
              BruceHerbach

              You might try using quick find with a on keystroke script trigger.

              Every time the user types a letter in the global field it does a quickfind[globalfield] script step.

               

              Sent from my mobile device... Please excuse typos.

              • 4. Re: What is the best way to setup an alphabetic lookup?
                richardsrussell

                (1) It sounds as if your users will only need to find a single contiguous range at a time. (That is, they might want records from "ac" to "af", as in your example, but they'd never ask for "ac" to "af" and "bq" to "bt" in the same result.) Is that right?

                 

                (2) It also sounds as if they're willing to take all of the different numbers that begin with the same letters. (That is, they wouldn't stop at "af55" if "af56" thru "af99" were also available; they'd take all of the "af"s.) Also true?

                 

                (3) Do all of your image names begin with 2 letters?

                 

                Depending on your answers to the questions, some variant on this approach (3 button clicks per image group) might prove expeditious for your users:

                 

                Finding Letter Combos.jpeg

                • 5. Re: What is the best way to setup an alphabetic lookup?
                  jsp

                  I would make a global search field in which the user can enter for example "ac-af".

                   

                  Left(FIELD;2) you get the left 2 characters of the field

                  Right(FIELD;2) you get the right 2 characters

                  Then search for the range.

                  • 6. Re: What is the best way to setup an alphabetic lookup?
                    siplus

                    What's wrong with

                     

                    Enter Find Mode[]

                    SetField[YourNameField, Substitute(YourGlobalSearchField;"-";"...")]

                    Perform Find[]

                    • 7. Re: What is the best way to setup an alphabetic lookup?
                      erolst

                      pedroble wrote:

                      I started with buttons for each letter a,b,c,.... but soon realised that that would need 26 or more buttons.

                      My imediate concern is setting up the 'find', later on I will confront the the finer details

                       

                      Well, you wouldn't really need 26 buttons … until you know better how to implement this, here is one possible approach. You should try to find a better UI, though.

                      • 8. Re: What is the best way to setup an alphabetic lookup?
                        pedroble

                        Richard S. Russell wrote:

                         

                        (1) It sounds as if your users will only need to find a single contiguous range at a time. (That is, they might want records from "ac" to "af", as in your example, but they'd never ask for "ac" to "af" and "bq" to "bt" in the same result.) Is that right?

                        Correct.

                         

                        (2) It also sounds as if they're willing to take all of the different numbers that begin with the same letters. (That is, they wouldn't stop at "af55" if "af56" thru "af99" were also available; they'd take all of the "af"s.) Also true?

                         

                        Not strictly true. Each letter is followed by a number. Looking up the letter "a" will give "a11" thru to "a99"

                        They might wish to look up "a15f" which would give them all the "a15f"s from "a15f11" thru to "a15f99" or,

                        they might wish to look up "c2a" thru to "c2d" which would give them all the "c2a... c2b...c2c.....c2d"s

                         

                        (3) Do all of your image names begin with 2 letters?

                        No.

                        The image names in question may contain 1,2 or more letters but for the moment a user would only be interested in the first 2 letters and probably the first 2 numbers after the first letter.

                        The user does not know of the numbers, only the description that they represent

                         

                        ------------------------------------------

                        • 9. Re: What is the best way to setup an alphabetic lookup?
                          pedroble

                          erolst,


                          Thank you for the example.

                          I shall put in some actual data to see how it sorts and finds

                          • 10. Re: What is the best way to setup an alphabetic lookup?
                            richardsrussell

                            they might wish to look up "c2a" thru to "c2d" which would give them all the "c2a... c2b...c2c.....c2d"s

                            ....

                            The image names in question may contain 1,2 or more letters but for the moment a user would only be interested in the first 2 letters and probably the first 2 numbers after the first letter.

                            The user does not know of the numbers, only the description that they represent

                            Clarification:

                            - each letter or number is represented by a separate field

                            - the image name is a concoction of those fields

                            If you have any control over the names of those images, for your own peace of mind you'd be well advised to standardize the naming format so that, for example, the 1-digit number "2" would always occupy 2 bytes, as in "02". It's trickier with single letters, because the alphabet doesn't have the equivalent of a placeholder like "0", but you could probably fake it with a "$", so that an "a" would be rendered as "$a".

                             

                            But, taking a broader view, what you're running into here is the eternal database problem of having "smart keys". On first hearing, those sound good, right? Who isn't in favor of smartness? But they're not. What you really want is dumb keys, so the key itself contains no information whatsoever about the thing it's attached to. The smartness is off-loaded into metadata — that is, descriptions about whatever info you're storing. Then you can add categories, assign multiple descriptions, change your mind about something, and so on, all without having to do a thing with the record's dumb key, which is just an arbitrary sequence number.

                             

                            For example, I might have a picture of Martin Luther King on file, and it's labelled "MLK". Smart key, right? It knows something about the image it's attached to. Then I get another photo of him and call it "MLK2". Now it has 2 bits of metadata, a name (well, an abbreviation of a name), and a tie-breaker number. Then I go "Hmmm, that first picture probably needs a tie-breaker number as well, just to be consistent." So you go back and relabel it "MLK1", thereby breaking all the links which already had it as just plain "MLK". I'm not even gonna get into what happens when you start adding photos of Dr. King's father and son (respectively "MLKSr." and "MLKIII") and start wondering whether you should've tacked a "Jr." onto those earlier images.

                             

                            You'd be better off just having that image of Dr. King labelled something like "520078", right between the Grand Canyon at "520077" and an Andy Warhol soup can at "520079", and have its image name, photographer name, chroma info, description keywords, etc. in entirely separate fields. It's those separate fields where you'd do your searching, not on the meaningless image numbers themselves.

                             

                            This is really the only long-term way to avoid the growing pains you're now experiencing as you run out of possible combinations of letters and numbers in a confined field-name space. It'll make more sense to your users, too.

                            • 11. Re: What is the best way to setup an alphabetic lookup?
                              pedroble

                              Ooops!

                               

                              Hopefully you have misunderstood me ...

                              1- the image name (label) is not the image_id

                              2- the image label is a description of a finite number of letters (26) and a finite number of numbers (9) whose characteristics and relationships have been reduced to a finite number of combinations (9).

                              The fact that the letters and numbers may be repeated many times gives rise to a finer search and allows the option of adding more fields when necessary

                               

                              It would seem that my image_id is what you refer to as a "dumb key" and my image labels are "smart".

                               

                              I am very grateful for your warning as I may well be trapped without realising it.

                              • 12. Re: What is the best way to setup an alphabetic lookup?
                                richardsrussell

                                Ah, good. The image_id is indeed the desired "dumb key", so you've got the basics down.

                                 

                                I'd differ slightly about whether your image label is "smart", tho. Clearly its individual components are "smart" in the sense that each of them is a stand-in for meaningful descriptive information, but I think what happens when you concatenate them into one long string is that you get the equivalent of a committee, which, as we all know, has an IQ determined by dividing by the number of its members.

                                 

                                Wouldn't you be better off allowing for a Find on each of the component parts of the image label independently of all the others? And, if so, wouldn't it make more sense to your users to use the actual underlying terms that the letters stand for, rather than having them abstracted out to just 1 or 2 letters or numbers, where they'd need some kind of cross-reference index to keep track of what (for example) "af" really means? Or do they really have that good a grip on the 2-letter codes, so that they really do understand them directly? Seems to me like a lot of stuff to have to keep straight in your head.