9 Replies Latest reply on Dec 26, 2011 10:31 AM by med

    performing find on a related table but keeping all records in the parent table

    med

      Title

      performing find on a related table but keeping all records in the parent table

      Post

      I have a customer table and an order table, related by customer ID. I need to produce a report for customers in a certain city listing their last order PRIOR to a certain date which is determined at the time of producing the report.

      The first step I did is that I went to the database diagram and modified the relation to sort the order table descending by date.

      Then I had a problem performing the find.

      If I perform a find on the order table first to exclude ordes more recent to the cutoff date. The find is destroyed when I perform a find on customer city. 

      If I perform a find on customer city and order date in one step all customers who have orders after the cutoff date do not show.

      If I add to this extend find for customers again then all the orders recent to the cutoff date show.

      So What can I do?

      I do not want to produce a copy of the subset of orders into another table and use it for the report because the table is huge and will take a long time. There must be a better way !!!

        • 1. Re: performing find on a related table but keeping all records in the parent table
          philmodjunk

          Try this:

          Perform the find on the orders table and specify the date criteria and the city in the related customer table in one find request. This should produce a set of all orders records with dates that match your date criteria, but limited to orders from customers in the specified city.

          If this is not the set of records you want, please describe what would need to change in that set to produce the set of records that you need.

          • 2. Re: performing find on a related table but keeping all records in the parent table
            mgores

            I think you need to find the customers by City, then constrain that found set by omitting the orders more recent to the cutoff date.

            Peform Find [customers::city ;  CITY]

            Constrain Found Set [order::date ; <DATE]

            • 3. Re: performing find on a related table but keeping all records in the parent table
              med

              Unfortunately non of the suggestions is producing the resul I want. Let me struggle with that for the rest of the day and if I cannot I will revisit it naxt week after Xmas and update you. Thank you and Merry Xmas

              • 4. Re: performing find on a related table but keeping all records in the parent table
                philmodjunk

                What result are you getting and how does it differe from what you want?

                • 5. Re: performing find on a related table but keeping all records in the parent table
                  med

                  I have been toying more with this issue and I am still stuck. This is my problem I have a layout based on the customer table and I need to produce the report from it because I need to list one line per customer. If I do my find from it it simply shows me all customers who did not have orders after the cutoff date but for those in the found set it is still keeping all orders new and old.

                  So if I go to another layuot based on erders it does find what I want but I cannot print the report from it because it will list for me all orders (not only the last one prior to the cutoff date) for each customer.

                  The problem does not stop here because after I accomplish this I need another version of the report to show the last payment date in addition, which would be kept in a third table.

                  So what can I do?

                  I am really missing two feature in filemaker which I used to use a lot in other packages which are

                  1- the ability to filter each of the related tables indepandently, and then.

                  2- the ability to specify dynamic relationships which you may modify during the scripting.

                  If these exist and I am not aware of them please let me know.

                  • 6. Re: performing find on a related table but keeping all records in the parent table
                    philmodjunk

                    Is this what you want?

                    Customer Name 1  | Fields from most recent Order record
                    Customer Name 2  | Fields from most recent Order record

                    and filtered to only list those customers that are from a specified city (field in Customer table) and that place an oder prior to a specified date (field in orderst table).

                    Sounds like you can get the list of customer names, but the order fields are from the most recent order instead of the most recent order prior to the specified date. Is that correct?

                    I'd use a layout based on the customer table for this, but then would use either a filetered portal or a portal with a filtered relationship to display data from the correct Order record.

                    A filtered relationship might look like this:

                    Customers::CustomerID = DateSpecifiedOrders::CustomerID AND
                    Customers::gSelectedDate > DateSpecifiedOrders::OrderDate

                    gSelectedDate is a date field defined in Customers with global storage specified so you can select one date for all customers records.

                    DateSpecifiedOrders is a new occurrence of your Orders table. You can create additional table occurrences of Orders by clicking it in Manage | database |relationships, then clicking the duplicate button (two green plus signs).

                    A filtered portal would use your original relationship:

                    Customers::CustomerID = Orders::CustomerID

                    But use a portal filter expression such as:

                    Customers::gSelectedDate > Orders::OrderDate

                    • 7. Re: performing find on a related table but keeping all records in the parent table
                      med

                      Ok thank you I will try that and let you know.

                      Is your idea of filtered portals an answer for what I expressed about the desire of filtering a related table independently.?

                      And how about the capability to define a relationship dunamically, does it exisr?

                      • 8. Re: performing find on a related table but keeping all records in the parent table
                        philmodjunk

                        You cannot define relationships dynamically in FileMaker, but there are methods that can achieve most the results you would want to see from such a use in a SQL based system.Many such "on the fly" queries can be done in FileMaker with a scripted Find that uses data specified in global fields by the user. Filtered portals can containe a complex calcualtion that evaluates/filters differently depending on values set by the user or a script in fields or vairables referenced in the calcualtion.

                        I described two methods as each has trade offs. The filtered relationship requires adding a table occurrence, but may work better with larger sets of data than a filtered portal. You can test both options with your database and see which works best for you.