7 Replies Latest reply on Mar 8, 2013 8:25 AM by philmodjunk

    Help with complex find...



      Help with complex find...



           I've imported some data into FileMaker and I'd like to perform a complex search on the data.  My data would be something like:

      Company, Job Title
      Best Company, Admin
      Best Company, Supervisor
      Beans Ltd, Admin
      Beans Ltd, Admin
      Gas Guzzlers, Admin
      Gas Guzzlers, Supervisor
      Prime Time, Admin
      Prime Time, Admin

      I would like to perform a search such as "show me all the companies that don't have a Supervisor".  The resulting list should be:

      Beans Ltd, Admin
      Beans Ltd, Admin
      Prime Time, Admin
      Prime Time, Admin

           Can FileMaker do this and if so how?


        • 1. Re: Help with complex find...

               I would ask you...

               How did you mentally do it for this post?

               I did it by separating the Company name from the Job Title by using the "delimiter" comma [ , ].  If your import separated the two parts into fields, then it is not so complex to find the "empty" Admin.


          • 2. Re: Help with complex find...


                 I guess I'm not sure what you're saying.

                 First, yes, each item is separated into it's own "cell" when impoted.  There is a "Company" heading and a "Job Title" heading and the data points are imported into those values.

                 But I'm not sure what you mean by finding the "empty" Admin.  Every "Job Title" has a value.

                 Logically for me this is how I see it but I'm not sure how to impliment this as a find:

                 For a Company:
                 - If any Job Title contains Supervisor then don't add to list
                 - Go to next Company



            • 3. Re: Help with complex find...

                   To find all records where a specific field is empty put a lone = in that field while in find mode.

                   To find all records where a field does not contain the text "admin", enter find mode, put admin in the field and then click the omit button.

                   Both types of finds can also be done in scripts.

              • 4. Re: Help with complex find...

                     There are no empty fields.  In the example I provided there will always be a value in all fields.

                     I guess I'm not being clear in what I'm looking for.  I can use the Omit  button like you say to show entrires that do not contain "Admin" but then this is my result:
                Best Company, Admin
                Beans Ltd, Admin
                Beans Ltd, Admin
                Gas Guzzlers, Admin
                Prime Time, Admin
                Prime Time, Admin

                when this is the result I want:

                Beans Ltd, Admin
                Beans Ltd, Admin
                Prime Time, Admin
                Prime Time, Admin

                     So again from my example, "Best Company" and "Gas Guzzlers" have an entry that contains "Supervisor".  By using the omit button the enries with "Supervisor" aren't displayed but the company's "Admin" entries are shown.  What I'm looking for is that if a company has ANY "Supervisor" in ANY entry, then I don't want that company to show up in the results.

                Again, I hope I'm being more clear.

                • 5. Re: Help with complex find...

                       It would be much simpler to put the "admin, supervisor, etc" data in a separate field.  Then relationships can be used to produce the desired set of records.

                       Using what you have will require a script with a two stage find:

                       1) Find all Supervisor records

                       2) Loop through the records and build a list of the company names in a global field or a variable

                       3) return to find mode and build a series of omit requests that omit each company name in the list.

                       4) perform this second find to produce the list that you want

                  • 6. Re: Help with complex find...

                         As I said above, each value is in its own field, just like in a spread sheet... they are actually being imported from a spread sheet.

                         From what you're saying it sounds like there is no easy way to do this and there will be a lot of manual work which is what I'm trying to avoid in the first place.  I was hoping there would be a single step to "find all companies that don't have any suporvisors".


                    • 7. Re: Help with complex find...

                           What I described in my last post would be in done in a script. It would be a bit complicated to set up, but simple for your users as all they would do is perform the script.

                           But with the position data in a separate field you can simplify the process by using relationships.

                           Let's call your table "Main" with fields called "Company" and "Position" to be the two fields you show as a single line separted by a comma.

                           Define a calculation field named constSupervisor with this calculation:


                           Create this relationship:

                           Main::constSupervisor = Main|Supervisor::Position

                           Define another calculation in Main, cSupervisorList as:

                           List ( Main|Supervisor::Company )

                           This is now an unstored calculation that lists the names of all companies where there is record with "Supervisor" in the Position field.

                           Define another relationship:

                           Main::cSupervisorList ≠ Main|NoSupervisor::Company

                           With those calculations in place, a portal to Main|NoSupervisor will list all the companies that do not have a record with "supervisor" in the Position field of at least one record or you can use the script step:

                           Go To Related Record [Show only related records; From table: Main|NoSupervisor ; Using layout: "Main" (Main) ]

                           to pull up these records as a found set.


                      1.           Main, Main|Supervisor, Main|NoSupervisor are all Tutorial: What are Table Occurrences? with the same data source table.
                      3.           This pulls up all "no supervisor" companies from your table if additional criteria needs to filter this list down further, either a constrain found set can be done after the GTRR, a filter can be put on the portal, or one or more of the relationships used can be modified to include additional match fields.
                      5.           This method is 'hardwired' to omit supervisor entries, if you replace constSupervisor with a simple text field--often a global text field, you can omit different groups of records by specifying different positions, not just "Supervisor".
                      7.           If your are using FileMaker 12, there are ways to use the ExecuteSQL funciton to produce this list that do not require the additional table occurrences and relationship in your relationships graph.
                      9.           This suggestion is based solely on the information you have provided. With a complete picture of all fields, tables involved and a "big picture" understanding of how you want to use this capability, it might be possible to suggest other approaches that are more flexible or less complex.