6 Replies Latest reply on Jun 2, 2017 11:48 PM by mz5005

    Querying on portal data in table view

    mz5005

      Imagine Customer and Order info. One customer can of course have more than one order.

      An important lay-out we have is customer based and is always used in table view. In the lay-out are several portal fields.

      One of the portal fields is the ORDER_DATE field.

       

      Q: if I want to query on the ORDER_DATE field, in such a way that I get all customers who have placed their

      LAST order on that date.... how do I do that? (assume that the underlying ORDERS table is sorted on ORDER_DATE)

      But it should be directly on the lay-out, not with a script.

       

      If I do it now, I get the above PLUS all customers who have placed orders on the requested date but also at later dates.

      Is it possible at all > if so how? If not, what is the way to go - help-field with latest order date maybe?

       

      Happy to receive suggestions!

        • 1. Re: Querying on portal data in table view
          philmodjunk

          Since you can't see a portal in table view, " In the lay-out are several portal fields." isn't something that makes a whole lot of sense. Perhaps you mean that you've added a field from a related table--which does not require a portal.

           

          The problem isn't really a question of how your layout is designed (though I doubt that I'd use table view for this. List view offers a similar format with options not possible in a table view.) It's really a matter of how performing a find in FileMaker works.

           

          Simply entering a date into a date field while in file mode doesn't include any criteria to tell FileMaker that you want this record only if there are no other records for the same customer with dates that come after (are greater than) the date specified. This is complicated by the fact that when you specify criteria in a field from a related table, FileMaker finds all parent records with at least one related record matching that criteria.

           

          I suggest that you add an unstored calculation field to the customer table defined as:

           

          Max ( Orders::Order_Date )

           

          If you specify your find criteria in that field, you'll find what you want, but given that it will be an unstored calculation, it will be a slow find. You can likely speed up results, however, if you do your find in two steps. Specify a find with the date entered into Orders::Order_Date first to find all customers that ordered on that date. Then return to find mode, specify the date again but this time entere it into this new calculation field and constrain your found set.

          • 2. Re: Querying on portal data in table view
            mikebeargie

            If you're in a table view, it is no longer a portal, just a related field.

             

            When you show related fields on a layout (outside of a portal), they display data from the FIRST related record in that relationship. So in your case you will see the first related order for that customer. Probably their oldest order, since natural order without sorting is creation order.

             

            So what I would do is go to your relationship diagram, and in the relation from Customers to Orders, on the Orders side, check the "sort relationship" dialog, and then sort by Order Date > Descending.

             

            This will mean that the customer's most recent order information will display in your table view. 

             

            HOWEVER, whenever you search related fields, you are searching all of those related records, as if you were searching in a portal. So to limit to searching just one order AND meet your requirement to stay on the customers layout, you will need a field in the customers table itself storing the most recent order date.

             

            Or, you will need a script that will follow behind your query and omit any records where the first related record is not equal to the search date that was input. Either way will work without leaving the layout though.

            • 3. Re: Querying on portal data in table view
              mz5005

              Philmodjunk, You are right.. portal is not the proper name.

              Thanks for the suggestions, I will try it out tomorrow (late night here

              already).

              • 4. Re: Querying on portal data in table view
                mz5005

                thanks for the clear and extensive explanation. i will work on it tomorrow.. it's late night here already.

                • 5. Re: Querying on portal data in table view
                  philmodjunk

                  Come to think of it, you can do this without the unstored calculation field.

                   

                  Say that you want all customers whose last order was 5/1/17:

                   

                  Enter find mode and specify 5/1/17 in the order_date field. Then select "new request" from the records menu.

                  Specfiy >5/1/17 in the order_date field and click the "omit" button.

                   

                  Perform your find.

                   

                  This is a process that can also be automated in a script and you'd get better performance using a layout based on orders instead of customers. (fields from customers could still be included on such a layout.

                  • 6. Re: Querying on portal data in table view
                    mz5005

                    yes of course - that's it!

                    as usual, the best solutions are simple but often overlooked :-)

                    thanks again.