6 Replies Latest reply on Jun 12, 2012 11:31 AM by med

    finding only records with related ones in a related table

    med

      Title

      finding only records with related ones in a related table

      Post

      I am having a problem finding records in table 1 which have related records in tbale 2 (using fm12)

      I actually wanted to find the number of customers of a certain type who had service in a date range.

      Here is what I did

      table 1 customer has customerid and customertype

      table 2 service has customerid and servicedate

       

      I created two glabal fields in customer datefilter1 and datefilter2

      I related the tables as follows 

      service::date <= customer::datefilter1

      service::date >= customer::datefilter2

      service::customerid = customer::customerid

      then I wrote the following script

      goto layout "customer"(customer)

      show all records

      set field customer::datefilter1 value:$date1

      set field customer::datefilter2 value:$date2

      set error capture on

      perform find as follows      find  customer::customertype=$type

                                            omit  service::date =       (I also tried service::date ?)

      Set variable $total value:get(foundcount)

      THAT DID not work for me itr always gave the total students of that type regardless of having service or not

      I ENDED UP writing the following script which worked for me 

      With same relationships I wrote

      goto layout "customer"(customer)

      show all records

      set field customer::datefilter1 value:$date1

      set field customer::datefilter2 value:$date2
      set variable $total value:0
      go to record first
      loop
      if not isempty(service::date)
      set variable $total value:$total+1
      go to record next exit after last
      endloop
       
      NOW THIS is an inefficient way of doing things and although I have a solution I really want to know if
      I am missing a basic knowledge of the meaning of relationships

        • 1. Re: finding only records with related ones in a related table
          philmodjunk

          If you are using Datefilter1 and Datefilter2 to produce a date range of DateFilter1...DateFilter2, then either you have a typo in your post or you have the wrong inequality operator. You should have:

          service::date >= customer::datefilter1 AND
          service::date <= customer::datefilter2 AND
          service::customerid = customer::customerid

          With that relationship in place, this find will find all records in customer with at least one related record in Service:

          Enter Find Mode [] (no need to show all records when your next step enters find mode)
          Set Field [Service::Date ; "*"]
          Set Error Capture [on]
          Perform Find []

          Please note that you can also get this list with only this relationship:

          service::customerid = customer::customerid

          Go to Layout [ "CustomerList" (Service) ]
          Enter Find Mode []
          Set Field [Service::Date ; Customer::datefilter1 & "..." & customer::datefilter2]
          Set Error Capture [on]
          Perform Find []

          Two key things are required for this to work:

          1) datefilter1 and datefilter2 must have global storage specified.

          2) The find must be performed on a layout based on Service, not Customer. But keep in mind that with the relationship that I've specified, you can include any fields you want from the customer table on this layout--you don't need any fields from service on it and it will still work--though I would guess that you'd want at least service::date on this layout.

          • 2. Re: finding only records with related ones in a related table
            med

            thank you for the quick reply

            The >= was a typo but in the post not in my original script

            I ended up replacing the omit line in my find to omit service::date [*] instead of service::date [=]

            With that and everything else the same, it worked but I do not understand why

            the operator * means any zero or character which means I am excluing the records with something in the date field, which I needed to include not exclude

            Can you please explain more.

            Also correct me if I am wrong but if I perform a find in a layout based on service will I not get the service record count rather than the customer count? and then I have to exclude the ones with repeated cutomer ID?

            • 3. Re: finding only records with related ones in a related table
              philmodjunk

              I ended up replacing the omit line in my find to omit service::date [*] instead of service::date [=]

              With that and everything else the same, it worked but I do not understand why

              My example script does not use an Omit request and should not need to do so. It makes no sense to me either and what you describe doesn't match my example script. You'd need to post the script that worked for you before I could say any more on that.

              Also correct me if I am wrong but if I perform a find in a layout based on service will I not get the service record count rather than the customer count? and then I have to exclude the ones with repeated customer ID?

              Those are definitely valid points and one of the reasons why I included both options. You can set up the layout with a sub summary layout part instead of a body part to eliminate the duplicate entries should there be more than one service record for a given customer in the specified date range. You can also use How to count the number of unique occurences in field. or A new way to count unique values in FileMaker 12 (Fmp 12 only) to compute your customer counts and put them in the subsummary layout parts.

              Whether it's better to use a more complex relationship graph or one of these two more complex methods for computing the count is one of those choices best left to you as each option has its trade offs.

              • 4. Re: finding only records with related ones in a related table
                med

                Thank you again

                in the first point when I said I replaced the omit I was refering to using my script which i presented in the first post

                About the excute SQL where can I read documentation about it .. I know sql queries from othe rlanguages but I need to see how it is implemented in Fm12 ... I like to use it

                • 5. Re: finding only records with related ones in a related table
                  philmodjunk

                  I bugged the documentation manager at FMP about that and she shared with me that ExecuteSQL can only execute Select statements (no suprise there) and that the documentation on the Select statement found in the ODBC and JDBC guide also applies to ExecuteSQL.

                  It's a far from complete documentation of what you can do. Select distinct with count being an example of something I figured out by researching general SQL select statements and then doing some trial and error, but it's a start.

                  You can find this guide, by pulling down the help menu in FileMaker and then selecting it from the product documentation sub menu.