8 Replies Latest reply on Jun 2, 2014 8:06 PM by bbringardner

    Find based on contents of another field

    bbringardner

      (Newbie Question) I would like to filter a list of records based on a company name entered in another related table and I would like this to occur while entering a layout.

       

      I have seen the script trigger that would allow me to trigger the script when entering, but how do you script a find to return records based on contents of another field? I have attempted to write this

       

      Enter Browse Mode

      Perform Find []

       

      but I do not understand the syntax I should use in the find request to set this up. Feel free to totally redirect me, but any help would be greatly appreciated!

       

       

      Thanks in advance!

      Ben

        • 1. Re: Find based on contents of another field
          erolst

          Hi Ben –

           

          usually you'd put the search term into a variable, go to the other table and perform a search, including trapping for errors and missing input, i.e.:

           

          Set Variable [ $searchTerm ; YourStartTable::yourField ]

          If [ IsEmpty ( $searchTerm ) ]

            Show Custom Dialog [ "No Search Term Given" … etc. ]

             Exit Script

          End If

          #

          Enter Find Mode [ no dialog ; no restore ]

          Go to Layout [ YourSearchLayout ( YourSearchTable ) ]

          Set Field [ YourSearchTable::yourSearchField ]

          Set Error Capture [ On ]

          Perform Find

          If [ not Get ( FoundCount ) ]

            Go to Layout [ YourStartLayout ( YourStartTable ) ]

            Show Custom Dialog [ "No Entries found for " & $searchTerm … etc. ]

          End If

           

          On the other hand, this may be something which is better solved using a relationship (and of course having defined one in the first place …). Difficult to tell without knowing your setup.

          • 2. Re: Find based on contents of another field
            vavo

            Hi,

             

            If I understand you correctly you have a field "companyName" in table1 and in table2 you have records also with the field "companyName" and more.

            Table1 has only one record where "companyName" contains "Big Corporation inc.", table2 has many records where "companyName" contains "Big Corporation inc.". Now when you go from table1 to table2 you want to see all the records that contain "Big Corporation inc.".

             

            If this is the case your best bet is to

             

            1. create a relationship connecting the fields "companyName" in table1 and table2  (even though you really should use unique ids here)

            2. use the script step "Go to related Record [Show only related records...]" to navigate to the other layout.

             

            If my understanding of your situation is correct you can try what I suggested. But if you are going to use relationships you should generally not create those using a field like companyName but use the unique id of a record, i. e. a field called "id" in table1 and then have a field "id_company" as the foreign key in table2 containing the same value.

             

            Hope that helps.

             

            Valentin

            • 3. Re: Find based on contents of another field
              flukey

              A slight modification to your script would avoid having to go into find mode and pasting the serch term.  You can do it all with a pre-stored search criteria as follows:

               

              If [ IsEmpty ( YourStartTable::yourField ) ]

                Show Custom Dialog [ "No Search Term Given" … etc. ]

                 Exit Script

              End If

              #

              Set Variable [ $searchTerm ; "=" & YourStartTable::yourField ]

              Go to Layout [ YourSearchLayout ( YourSearchTable ) ]

              Set Error Capture [ On ]

              Perform Find (Restore)

              If [ not Get ( FoundCount ) ]

                Go to Layout [ YourStartLayout ( YourStartTable ) ]

                Show Custom Dialog [ "No Entries found for " & $searchTerm … etc. ]

              End If

               

              Pay attention to the bolded text above.  In the perform find your search criteria would be YourTargetField = $searchTerm, e.g.

              Capture.PNG

              1 of 1 people found this helpful
              • 4. Re: Find based on contents of another field
                erolst

                flukey wrote:

                A slight modification to your script would avoid having to go into find mode and pasting the serch term […]

                And I want to avoid that because …? In fact, I prefer spelling out my search terms explicitly; the Restore option requires you to open the step to see your search criteria, which may be a bit more complicated than a simple ”contains“.

                 

                Also, in your version $searchTerm is now ”slightly modified“, which may result in a dialog that states (e.g.) "No Entries found for =bananas". (I guess I'll have to settle for =cherries …)

                 

                But then of course, searching for ”contains“ via Restore doesn't really require the "=" operator …

                flukey wrote:

                […] Pay attention to the bolded text above […]

                Gee, thanks. Do you have free capacities? Could I take some more lessons …?

                 

                But I appreciate the spirit in which your advice was given. Keep up the work.

                • 5. Re: Find based on contents of another field
                  bbringardner

                  Vavo - You have understood my goal correctly.  This does make sense conceptually.

                   

                  I have attempted to replicate this, but I get an error

                   

                  "This operation could not be completed because this layout cannot display the result."  Is there something about the layouts which need to match to allow the result to be seen?

                   

                  To be more specific about my setup.  I have a "company" field in currentUser table and a "company" field in a dailyreports table.  I would like to display only the records with the currentUser::company when I enter the dailyreports layout.  I would like to do this in the simplest and most straightforward faship since I am still a newbie.

                   

                   

                  Thanks!

                  Ben

                  • 6. Re: Find based on contents of another field
                    bbringardner

                    Also I was suprised to see that the find relate records asked for a table and not a field.  Is there a way to display related results from a specific field instead of a table? 

                    • 7. Re: Find based on contents of another field
                      vavo

                      Hi again,

                       

                      Since you want to display records from the dailyreports table you will have to select a layout based on this table in the Go To related Record script step. By default this is set to "Current Layout" but you have to change that. Also select "Show only related records" to make sure you only see the reports you want.

                      Have in mind that the company name has to be abolutely identical in the company and the dailyreports table for the relationship to work, filemaker is very picky this way. The better way would be to have a unique compnay number (such as 1000, 1001, 1002 etc.) that never changes even if you change the company name slightly (spelling, "inc." instead of "inc"...) and use the same number in a field in dailyreports.

                       

                      Sounds a bit complicated but this introduces to you the world of relational database design, which is really cool and opens so many possiblities. One of them is that you can use a portal in your company table to display all the daily reports right in the company view...

                       

                      Check out the free FilemakerTraining Series , lesson 7-8 for more on relations.

                       

                      Regarding your question below:

                      Relationships are always between tables, they are linked by key fields (primary key [currentUse::company] and foreign key [dailyreport::company). Think of it rather as records being linked (related) through those fields,

                      you can then choose in your layout (or portal) which fields of the related record you want to display.

                       

                      Hope that helps,

                       

                      Valentin

                      1 of 1 people found this helpful
                      • 8. Re: Find based on contents of another field
                        bbringardner

                        At first when I read this response it appeared over complicated to me because I just wanted to be able to filter things down based on a simple "perform find" command.  But I spent the weekend digging in and learned more about variables and have come to the conclusion that they are very powerful. 

                         

                        I was able to get the find working correctly pretty easily once I understood the tools that FMP had built around variables and how to pass the variable into the perform find call.

                         

                         

                        Thanks for the help,

                        Ben