9 Replies Latest reply on Apr 12, 2017 9:49 PM by user28653

    set field to all records of a found set

    user28653

      Hello All, I am stuck on the following problem.

       

      I have a group of records (several thousand) with the field "title", contained in this field is a model number.  I need to parse out the model number to a  called "model number".  The problem is that the model number is located is all over the place in "title" so parsing has proved to be as much work as just manual sorts.  

       

      So I had an idea that i might be able to script it using find mode.  I have a master list of all the possible "model numbers" so my thought is to loop through that list, get a found set for each model number in the list, then on all records in the "found set" set the field "model number" equal to the search variable for all the records in the found set.

       

      I hope I explained this clearly if not please don't hesitate for clarification.

        • 1. Re: set field to all records of a found set
          Johan Hedman

          Sounds like you have a good idea. I would use

           

          Loop

               If ( Patterncount ( YourTitleField ; "SpecificModelValue" ) = 1 )

                    Set Field ( YourModelFIeld ; "Value 1" )

               Else if ( Patterncount ( YourTitleField ; "SpecificModelValue2" ) = 1 )

                    Set Field ( YourModelFIeld ; "Value 1" )

               xxxx

               and so on

               Go to record next, exit after last

          End Loop

          • 2. Re: set field to all records of a found set
            Vincent_L

            Put all your known models in a table ModelTable, where the model is stored in the model field

            Provided your maintable is called mainTable, in which you've a record key (like a SKU) you do this

             

            use the power of ExecuteSQL

             

            ExecuteSQL(

            "SELECT A.\"SKU\",B.\"model\" FROM \"MainTable\" A

            JOIN \"ModelTable\" B ON A.\"title\" LIKE '%'+B.\"model\"+'%'

            WHERE  A.\"SKU\" IN ('SKU01','SKU02','SKU03') AND B.\"model\" IS NOT NULL";char(9);"¶")

             

            Be careful as the model have to be exactly as is in the title, that's case sensitive. If you want case insentivity, do this instead

             

            ExecuteSQL(

            "SELECT  A.\"SKU\",B.\"model\" FROM \"MainTable\" A

            JOIN \"ModelTable\" B ON UPPER(A.\"title\") LIKE '%'+UPPER(B.\"model\")+'%'

            WHERE  A.\"SKU\" IN ('SKU01','SKU02','SKU03') AND B.\"model\" IS NOT NULL";char(9);"¶")

             

             

            in the IN part you put your all the SKUs of the foundset, if the sku are numbers then remove the ' '

             

             

            This will result in something like that (a tab separated array)

             

            SKU01 Model-899

            SKU02 Model-7899

            SKU03 Model-77885

            SKU03  Model-899             If the SKU03's title contains both Model-899 and Model-77885, you'll get 2 lines

            SKU03  Model-77885         but probably won't happen

             

            If you want to do it agians whole database, just remove A.\"SKU\" IN ('SKU01','SKU02','SKU03') AND

            • 3. Re: set field to all records of a found set
              beverly

              wow! lots of suggestions.

              here is another. You had not provided any examples, but I'm going presume "all over the place" can mean words separated by spaces, where the Model can be any of the words. IF this is the case, read on...

               

              Create an auto enter (text field) with:

              Substitute ( title ; " " ; Char(13) ) // change the spaces to returns.

              Now you have a List, but it also can be a multi-line key (for a relationship).

              If you have a table with the Models in a field that field can relate to the new multi-line key in a Relationship. This allows you to do several things, including quickly finding and "extracting" the Model number.

               

              Please provide some example text and we can get some demos together for you.

               

              beverly

              • 4. Re: set field to all records of a found set
                philmodjunk

                I have a master list of all the possible "model numbers" so my thought is to loop through that list, get a found set for each model number in the list, then on all records in the "found set" set the field "model number" equal to the search variable for all the records in the found set.

                 

                Replace Field Contents can be used to assign a value to a given field for every record in the current found set. That seems to be exactly what you asked for here. (Not to denigrate the other excellent suggestions here, but thought it useful to answer the specific question that was asked...;-) )

                • 5. Re: set field to all records of a found set
                  user28653

                  Wow thanks for all the great info, I will work on it some more tonight (This is an in-house project for my company and I try to dedicate a couple hours a night to making the database better).

                   

                  @Johan Hedman

                  I am going to try this idea first as it looks the most like what I was starting to attempt on my own, I have not used pattern count before but it looks like a promising road.

                   

                  @Vincent_L

                   

                  I am not very familiar with SQL and frankly learning filmmaker has been like drinking from a firehose so i want to try to stick with native filmmaker scripting and if I can do the SQL part in filemaker then i am really lost.

                   

                  @Beverly

                   

                  I uploaded a sample file to my profile (I did not see a way to attach it the this thread) I just have it in a separate file from my main database so i can play around with it and not break anything.  I am not really sure I am understanding your method as I already have the model numbers in a separate table.

                   

                  "all over the place" was to mean sometimes the model number is the 3 word sometimes the 4 word, sometimes its encapsulated with ( ) or * * but it is very random.

                   

                  @philmodjunk

                   

                  You are right, the part I need to automate is finding the found set then scripting the replacement.      

                  • 6. Re: set field to all records of a found set
                    philmodjunk
                    I did not see a way to attach it the this thread

                    Use a web browser, not email to reply to this thread

                    Click the "use advanced editor" option and you'll find a link to click for attaching files.

                    • 7. Re: set field to all records of a found set
                      user28653

                      This worked great I wrote a few lines with my model numbers and it was perfect.  So now i am going to ask a lazy question.  I have almost 200 model numbers is there anyway to use pattern count and maybe set up a value list with the model numbers I have and just step through the list or is my only option to write out the script one line at a time.  Still allot less work than the way i was doing it. 

                      • 8. Re: set field to all records of a found set
                        Johan Hedman

                        I do not say you can´t, but then you will have to do a more complex Set Field

                        • 9. Re: set field to all records of a found set
                          user28653

                          Yeah, I think i am just going build it with allot of copy and pasting, simple and reliable.  Thank you so much to everyone for taking the time to help