5 Replies Latest reply on Jul 28, 2011 9:15 AM by philmodjunk

    Find based on aggregates of  related records

    HB

      Title

      Find based on aggregates of  related records

      Post

      Hi

      I wish to run a find for several criteria of customers first, then I wish to weed out all the customers who have a work order AND who, on the last day they had a work order, at least one of the work orders were larger than $300.

      I've scratched my head about this for a while but need help. I got the first find, just need the weeding out part. (Thanks in advance)

      I am in a customer file (one record for each customer) which is related to a Work order file (possibly many records for each customer) via the customer number..

        • 1. Re: Find based on aggregates of  related records
          philmodjunk

          If you sort that relationship by the Work Order amount in descending order, the first related record will be the work order with the largest amount. If it is $300 or larger, it needs to be "weeded out".

          #Perform this on the Customer layout after performing the find
          Go To Record/Request/Page [first]
          Loop
             IF [WorkOrdersSorted::Amount > 300]
                 Omit Record
             Else
                 Go To Record/Request/Page [next ; exit after last]
             End If
          End Loop

          • 2. Re: Find based on aggregates of  related records
            HB

            Great - only one problem - the relationship needs to be by Date for another procedure.

            My guess is you will say - just make another relationship, correct?

            • 3. Re: Find based on aggregates of  related records
              philmodjunk

              Yep, you can add a new Table Occurrence of the same table. That's why I named mine "WorkOrderSorted" in the example script.

              Hmmm, come to think of it, I don't think we need this relationship to be sorted nor the looping script either if we set up the right criteria in our find. You will need the relationship to link to the correct group of records however so you may still need an additional relationship to get it to work for you.

              You can add an omit request to your find that omits all customer records where all customer records with a related work order record with amount > 300 is omitted.

              Here's how to do it in a general outline that can be done manually or via a script:

              On your customer layout, enter find mode and specify the criteria to find the customers you want to find.

              Create a new request (by hand, select New Request from the Requests menu, by script, use New Record/Request)

              Enter "> 300" in the WorkOrders::Amount field

              Make this an Omit request (by hand, click the Omit button in the status area. In a script, use Omit Record)

              Perform your find.

              • 4. Re: Find based on aggregates of  related records
                HB

                Actually - wouldn't that omit all the customers who have ANY workorders over $300, not just the last time they were in?  I tried your first method - seems to work good.

                • 5. Re: Find based on aggregates of  related records
                  philmodjunk

                  Yes it would, but the script will also unless you changed the sort order to list the most recent records first. (Sort by date, then by amount, both in descending order.)