3 Replies Latest reply on Jul 27, 2015 1:20 PM by philmodjunk

    obtain maximum value of one field for each entry in another field

    HB

      Title

      obtain maximum value of one field for each entry in another field

      Post

      I must have brain freeze this am.  How can I obtain the maximum value of one field for each entry in another field that shares the same data in another field on the same table.

       

      ie  In a 'WorkOrder' table, find the last 'StatusDate' for every 'Client' who has a 'WorkorderNum' whose 'Status' = "complete"

        • 1. Re: obtain maximum value of one field for each entry in another field
          philmodjunk

          Do you have a client table? That seems the correct context for this.

          There is a SQL and a nonSQL method available to get what you want.

          The nonSQL method requires a relationship that matches a client record to an Tutorial: What are Table Occurrences? of Work Orders by both Client ID and by a specified status value.

          Clients::__pkClientID = WorkOrders|Complete::_fkClientID AND
          Clients::constComplete = WorkOrders|Complete::status

          Then a Max function in Clients or a Maximum type summary field in WorkOrders|Complete would return the status date of the most recently complted work order for that client.

          The SQL method is much the same, but does not require adding  a new table occurrence or a constComplete field to your database as it does the same "join" along with a WHERE to get the maximum date of the same set of work order records for a given client.

          • 2. Re: obtain maximum value of one field for each entry in another field
            HB

            Interesting!  What I did was make a Maximum Summary field based on the 'StatusDate' field (called it WorkOrderSumLastWO_Date) in the WorkOrder table, had the script find all the 'WorkorderNum's whose 'Status' = "complete" and sort them by CustID and do a replace field contents function using getsummary(WorkOrderSumLastWO_Date;Wrk_Ordr::Customer Number) and it seems to work!

             

            (Hope i am clear)

             

            I hav never used the SQL bit will look into it. Thanks

            • 3. Re: obtain maximum value of one field for each entry in another field
              philmodjunk

              While you could use your summary field in place of the field in Clients using the Max function. The method I describe does not require a script--nor use of the replace fields tool.

              Replace fields might fail to correctly update records if another user has one of the records being updated by replace field contents open for editing.