1 2 Previous Next 24 Replies Latest reply on Nov 6, 2016 11:38 AM by BruceRobertson

    Performing a find in a script

    TheNovice

      Hello All,

       

      I’m on FM 14. I have a table called Periodicals that contains a field called Title. I want to create a separate table that will contain only the titles (+ and ID for each). So to copy all the titles in that new table, I have written the following script. Of course once the new table contains one occurrence of a specific title, it does not need a second one, and so my question is what am I missing in the script to avoid duplicates of a same title being created in my new table? I know the script must perform a find at some point but this is where I get stuck. Help would be greatly appreciated.

       

      Go to layout [‘<Periodicals’>]

      Go to Record/Request/Page [<first>]

           Loop

           Set Variable [<$title>]; Value: Periodicals:Title

           Go to layout [‘<Periodicals’>]

           Perform Find [Restore]

           ???

           New Record/Request

           Set Field [Periodicals::Title; $title]

           Go to layout [‘<Periodicals’>]

           Go to Record/Request/Page [Next; Exit after last]

      End Loop

       

      Thanks.

        • 1. Re: Performing a find in a script
          fmpdude

          I'm not sure everything is correct with your script, but based on where you're stuck, you need to

           

          First "Enter Find Mode".

          Then set fields you want to "find" (in Find Mode).

          Then "Perform find".

           

          Does that help?

           

          ------

           

          Also, once you start writing scripts, having the Advanced version with its debugger is quite helpful so you can see what's going on.

          • 2. Re: Performing a find in a script
            TheNovice

            OK.

            I've added Enter Find Mode to the script, but then I'm not sure what you mean by ''set fields you want to "find" (in Find Mode)''. Do I have to create a search request of some sort? Can you be more specific?

             

            Thanks.

            • 3. Re: Performing a find in a script
              David Moyer

              Hi,

              I want to create a separate table that will contain only the titles

              I don't see where you are referencing two different tables in your script - that would be a starting point.  You would need to go to a layout besides Periodicals, which will be based on another table, not Periodicals.

              • 4. Re: Performing a find in a script
                fmpdude

                Filemaker uses "QBE" or "Query by Example".

                 

                So, once you enter find mode, you need to use "SET FIELD" to what you will eventually want to find. That is, fill in whatever fields are needed for the Find you want.

                 

                Then Perform Find.

                 

                Does that help?

                • 5. Re: Performing a find in a script
                  TheNovice

                  As pointed by David, the script needs to go to a second layout. Fixed.

                   

                  But I don't understand how the Set Field step will work. There are about 20 different titles in Periodicals::Title. Does the script have to perform a different search for each title separately? I'm sure that can't be but I don't understand how.

                  • 6. Re: Performing a find in a script
                    fmpdude

                    Sounds like it may be time to check out some of the many online tutorials, get an FMP book, or take a Lynda.com course. There's only so much you can expect from a free forum to teach you a subject.

                    • 7. Re: Performing a find in a script
                      TheNovice

                      Sorry for bothering you...

                      • 8. Re: Performing a find in a script
                        David Moyer

                        here's a great thing to download - and it's free.   FTS Basics ...

                        FileMaker Training Series | FileMaker

                        • 9. Re: Performing a find in a script
                          BruceRobertson

                          "Does the script have to perform a different search for each title separately? I'm sure that can't be..."

                          And why not? Very hard to understand what in the world would lead you to say that.

                          • 10. Re: Performing a find in a script
                            SteveMartino

                            But I don't understand how the Set Field step will work. There are about 20 different titles in Periodicals::Title. Does the script have to perform a different search for each title separately? I'm sure that can't be but I don't understand how.

                            No, you're missing some basics.

                            Based on your script and you're explanation, you want to create a new record in the new table, from the old table, only if there isn't a record already created in the new table.

                            The first caveat may be that there could be a typo or some extra space(s) at the end of the title, and you could end up with a duplicate record.  It appears to be a one time script, hence the loop.

                            But try something like this...Before you try this it is IMPERATIVE that you try it on a back up of the database first.  Check all the data, then move it to your production database.  Let's call the destination Table NewTable, and the field, Title, based on the layout NewTable.

                             

                            Go to layout [‘<Periodicals’>]

                            Go to Record/Request/Page [<first>]

                            Freeze Window

                                 Loop

                                 Set Variable [<$title>]; Value: Trim(Periodicals:Title)

                                 New Window [Table/layout you want to create new record/or check for duplicate

                                 Go to layout [NewTable]

                                 Enter Find Mode [ ] //uncheck pause

                                 Set Field [NewTable::Title;<$title>// the field you want to check against a duplicate

                                 Perform Find [ ] //make sure there are no find requests

                                 If (not Get(FoundCount) //name does not exist exists in new table/layout

                                      New Record/Request

                                      Set Field [NewTable::Title; $title]

                                      Commit Records

                                 End If

                                 Close Window

                                 Go to Record/Request/Page [Next; Exit after last]

                            End Loop

                             

                            Try it while watching it in the script debugger to make sure it's working fine

                            • 11. Re: Performing a find in a script
                              SteveMartino

                              Couldn't edit my post...There's also an easier way to do this using 'Magic Key' or the push technique where you relate the 2 tables by title, check allow creation in the second table.  Then in the script, instead of the find, if the title already exists it wont create the record, if it doesn't it will.

                              • 12. Re: Performing a find in a script
                                fmpdude

                                You were not bothering me, but to learn FMP, it just takes some study. This forum is extremely helpful, but it's not substitute for say, the FM training series.

                                 

                                So, if you're just looking to solve a single problem, this forum is great and the folks here are second to none. But, if you don't know FMP yet, there are good ways to learn it.

                                 

                                Thus, I was just pointing out the terrific resources available. Lynda.com, for example, has several FileMaker courses and I would highly recommend it!


                                FileMaker has been around for 30+ years and it's chalked full of all kind of cool stuff.

                                 

                                Enjoy!

                                • 13. Re: Performing a find in a script
                                  TheNovice

                                  Thanks a lot for your suggestions Steve,

                                   

                                  I will test both approaches on my DB as soon as I can and get back to you.

                                  • 14. Re: Performing a find in a script
                                    TheNovice

                                    Hi Steve,

                                     

                                    I've tested the first approach you suggested. It works in the sense that it gives me the result I want, but there is one problem. I'll explain.

                                     

                                    As I said in an earlier post, there are 20 different titles in Periodicals::Title, for 1200 records or so. When I run the script, it produces 20 records in NewTable::Title (and not 1200). So far, so good, that is exactly what I want. The problem is that each time the script reaches a record where the title is different from the immediately preceding one, I get prompted by FM with the message that there are no records matching the search criteria and asking me if I want to continue, and when I click on YES, the script keeps running until it hits the next new title, and so on. And once the script has gone through all the records, I still get the right result.

                                     

                                    I did make sure there is no find request in the script and I reviewed each script step carefully, but I still don't understand what I'm doing wrong.

                                     

                                    Thanks.

                                    1 2 Previous Next