10 Replies Latest reply on Apr 16, 2013 11:25 AM by LaRetta

    Scripting a field's index

    micinfo

      I am looking for a way to compare data (a single word) that's entered by a user in a Custom Dialog box (or global variable), against the index of an existing field in that table so that I can warn the user to choose a different entry if what they entered already exists in another set of records in that table.

       

      I was hoping there was something like getFieldIndex[fieldname] so that I could do something like If patterncount ( getFieldIndex [fieldname] ; $CustomDialogData ) > 0, but no such luck.

       

      Any ideas about how I might compared whether a data entered into a custom dialog field already exists in the current index values of an existing field? I need to avoid doing a Find and I want to search the ENTIRE index of a field, i.e., including records not in the current found set.

       

      Thanks.

       

      Phil.

        • 1. Re: Scripting a field's index
          Mike_Mitchell

          What about a relationship between the global field and the field you're checking?

           

          Mike

          • 2. Re: Scripting a field's index
            steve_ssh

            Hello Phil,

             

            In FM11, my first pass at this problem would be to do exactly as Mike suggests.

             

            As I do more work in FM12 I see these sorts of scenarios as opportunities to utilize ExecuteSql.

             

            ExecuteSql is especially attractive if there is no other need for the relationship I'd need to create to check for the existence of the match string.

             

            It's also attractive in a case where your script doesn't even have the potential match string stored in a field (but rather it's stored in a global variable as mentioned in your post).

             

            I find that I'm still at the point where I have to remind myself about this option, since my development habits are still somewhat v11-centric.

             

            Hope this helps,

             

            -steve

            • 3. Re: Scripting a field's index
              KevinSmith

              I'm assuming that the field you're indexing may be more complex than a simple word e.g. "The quick brown fox jumped over...". In order to get a link between the search term "fox" and the sentence I've just made up, you need to build a calculation field that chops up the individual words into a list:

              "The¶

              quick¶

              brown¶

              fox¶

              jumped¶

              over¶..."

               

              Next step, build a relationship between the search field and the calcuation field containing the list.

               

              Use the substitute function to create the pseudo index field:

              $PseudoIndex=Substitute($ParagraphOfSillyText;" ";"¶")

              • 4. Re: Scripting a field's index
                LyndsayHowarth

                I use the filtered relationship for this kind of thing too.

                 

                There is also a built in validation (Field Options) for a field that the value "Must be Unique". This is the simplest to use as you can lock the user from proceeding if they do not enter something unique.

                 

                - Lyndsay

                 

                Don't try and make a square wheel when there is already a round one available.

                • 5. Re: Scripting a field's index
                  micinfo

                  Duh, that's embarrassing! lol Talk about not seeing the forest!

                   

                  Anyway, several here have chimed in with the "obvious" answer.

                   

                  Lyndsay, I had considered the "unique" validation, but it's possible that the same value might be associated with several values in a predefined "set" of records, but I need to prevent ANOTHER set of records being entered with that same value.

                   

                  Kevin, thanks for the alert about multi-word matching; in my case that's not an issue because there could only be a single "word" in that field, but your point is well taken.

                   

                  Steve and Mike, thanks for the slap-in-the-face wakeup call. I shoulda known better. I guess I need to increase my sleep cycle a bit, eh?!

                   

                  Fun stuff!

                  • 6. Re: Scripting a field's index
                    Mike_Mitchell

                    One thing to remember: With text fields there are two indexes - a word index, and a value index. The value index is based on the first 100 characters of each value (as separated by carriage returns, like Kevin pointed out), and is used for relational joins. The word index is based on each word (see the Help for what characters qualify as word separators), and is used for Finds. So if you're wanting to lock out values based on the word index (what you see if you check "Show individual words" in the Insert From Index dialog), using a relationship won't work.

                     

                    Glad we could help.

                     

                    Mike

                    • 7. Re: Scripting a field's index
                      micinfo

                      Thanks Mike. Yeah, I'm aware of the two indices. In fact, older versions of FMP only considered the first 20 characters for relationships, so fp7+ is a vast improvement.

                       

                      As I said, in my case it's moot because, by design, the field is limited to a single short word.

                       

                      Thanks again, folks.

                      • 8. Re: Scripting a field's index

                        Another inexpensive option is to create a value list of that field and then simply test - either using field validation against 'member of value list' and/or via trigger using calculation similar to:

                         

                        not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "your VL name" )  ; your Global ) )

                         

                        ... which produces Boolean 1 (true ) if it already exists.

                        • 9. Re: Scripting a field's index
                          mbraendle

                          LaRetta schrieb:

                           

                          Another inexpensive option is to create a value list of that field and then simply test - either using field validation against 'member of value list' and/or via trigger using calculation similar to:

                           

                          not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "your VL name" )  ; your Global ) )

                           

                          ... which produces Boolean 1 (true ) if it already exists.

                           

                          Erm, no. This is a bad and very inefficient approach. ValueListItems loads the whole index into memory (which might be problem if it is larger than 2 GB, and that can easily happen with large text databases), FilterValues must parse it sequentually.

                           

                           

                          It's much better to use hash-table mechanism of the DB by either

                           

                          - using ExecuteSQL()

                          - a find

                          - a relationship

                           

                          What hasn't been discussed here is matching terms which are only slightly different (e.g. words with umlauts matching to such with umlauts expanded, or slight alterations such as hyphens between words compared to non-hyphenated variants).

                           

                          I remember a project where we had to match the headwords of two encyclopedias (Wikipedia and another one), and where we improved matches considerably by "normalizing" the terms.

                          • 10. Re: Scripting a field's index

                            MartinBraendle wrote:

                             

                            LaRetta:

                             

                            not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "your VL name" )  ; your Global ) )

                             

                            Erm, no. This is a bad and very inefficient approach. ValueListItems loads the whole index into memory (which might be problem if it is larger than 2 GB, and that can easily happen with large text databases), FilterValues must parse it sequentually.

                             

                            I am not sure I agree, Martin.  It would be no different than the index required for a relationship or a find on stored values. It is a technique used by top developers for several years now that I'm aware of and is no different than using List().  And if you think ExecuteSQL() is faster then you haven't really used it with joins on large datasets.

                             

                            Regardless, it is good to provide different options and let folks test what works best for them so I am glad you spoke up.  And if you have any tests or proof to your statement then I hope you point out those links because I do not care who is right or who is wrong ... only in knowing and presenting the best answers. If proven wrong, I'll change in a heartbeat.  In meantime I'll try to run my own tests and share them as well.