Skip to main content
C
chrish1-Visitor
1-Visitor
February 9, 2016
Solved

How to return MAX() from a specific field in Data Table?

  • February 9, 2016
  • 1 reply
  • 5824 views

I'm trying to create a service that I can globally put in a ThingShape; then reference inside every new DataTable that I create. This service will find the PK of each new data table and ensure that it is populated with a unique/increasing value. To do this, I need to be able to find the current MAX() value of all data in the data table for the PK field; then set new row PK field to MAX()+1.

If I'm simply way out in left field...please reign me in, but I think I'm close.

Please provide a sample script for how to query this information from an existing data table.

Best answer by PaiChung

So to find out what Aggregate puts out, you can always first set the result to the Aggregate (basetype infotable) and see the result in Composer.

Aggregate pre-pends the operation before the field it is acting upon.

So newID = dtMax.MAX_KEY_Field

1 reply

P
PaiChung22-Sapphire I
22-Sapphire I
February 9, 2016

The Aggregate service can give you MAX (as well as MIN COUNT AVERAGE SUM)

C
chrish1-VisitorAuthor
1-Visitor
February 10, 2016

Ok, almost there!  dtMax variable is a INFOTABLE with exactly 1 row and 1 column. How do I set the variable "newID" to the single numeric value of dtMax?

    var dtParams = { maxItems: undefined /* NUMBER */  };

    var dtValues = me.GetDataTableEntries(dtParams); //INFOTABLE

    var newID=0 // NUMBER

    var dtMaxParams = {

        t: dtValues, // INFOTABLE

        columns: KEY_Field, // STRING

        aggregates: "MAX", // STRING

        groupByColumns: undefined // STRING

      };

   

    var dtMax = Resources["InfoTableFunctions"].Aggregate(dtMaxParams); //INFOTABLE


    newID = ????

P
PaiChung22-Sapphire IAnswer
22-Sapphire I
February 10, 2016

So to find out what Aggregate puts out, you can always first set the result to the Aggregate (basetype infotable) and see the result in Composer.

Aggregate pre-pends the operation before the field it is acting upon.

So newID = dtMax.MAX_KEY_Field

Terms & ConditionsAccessibility statement