Knowledge Base Article

Referencing Datasets in Formulas

Dataset algorithm functions

vCalc provides a set of functions that can be used within an equation or constant algorithm that facilitate using information from tables.  The functions are:

Get a dataset row:  

  • Returns an array (list) containing all of the cells in a row.
  • Syntax:  x = VDatasetUtils.getRow(tableID , Long rowid)

Get a dataset column:

  • Returns an array (list) containing all of the cells in a column.
  • Syntax:  x = VDatasetUtils.getColumn(tableID , String colName)

Get a dataset cell:

  • Returns an Object containing the number or string from a specific cell.
  • Syntax:  x = VDatasetUtils.getCell(tableID , String colName, Long rowId)

Get a dataset’s latest row:

  • Returns an array (list) containing all of the cells in the last row entered.
  • Syntax:  x = VDatasetUtils.getLatestRow(tableID )

Get a dataset’s number of rows:

  • Returns an integer (Long) of the number of rows in a dataset.  
  • Syntax: x = VDatasetUtils.getNumRows(tableID );

Note: each function requires an ID (uuid) to identify the vCalc table.

Convert a dataset "Date" or "DateTime" type to a formatted string:  vCalc datasets include bot a "Date" data type and a "DateTime" data type.  When a column is designated as on of these types, the user automatically provided with a calendar (and clock) to choose the date (and time) which is stored in the dataset.

  • Returns a formatted string based on the "Date" or "DateTime".
  • outString = rowVal[0].getDateString()  //Provides a formatted string containing date e.g. (8/3/15)
  • outString = rowVal[0].getDateTimeString()  //Provides a formatted string containing DateTime e.g. (10/19/15 6:43:25 AM)

Dataset function Examples

Gem Specific Gravity Retrieval from a dataset

// See Specific Gravity
def gem = args.GEM   //User input of gem name from the pulldown list

def tabID =  "d39aecee-f011-11e3-b7aa-bc764e2038f2" // Table ID of the specific gravity of gems.

def gems     = VDatasetUtils.getColumn(tabID,"gem")   // Get the column of gem names
def specGrav = VDatasetUtils.getColumn(tabID,"SG")   // Get the column of specific gravities
def numRows  = VDatasetUtils.getNumRows(tabID)      // Get the number of rows in the spec grav table

def notFound = 1                                                                   // Initialize while loop criteria

def i = 0

while ( notFound ){                                         //Loop through the rows of gems to find the user’s choice.
               if (gems[i].equals(gem) )  
                              return specGrav[i]  // If gem is found, return the specific gravity for the same row.
               If (i >= numRows)
                              return "gem not found"

Retrieve the latest pricing for drywall panel from a dataset

//See Price drywal 4x8 5/8 inch

def tableID = "a62d5669-4d57-11e4-a9fb-bc764e2038f2"  //Set dataset UUID to a local variable (tableID)
def rowVal = VDatasetUtils.getLatestRow(tableID)               //Use function to retrieve latest row of data.
def price = rowVal[5]                                                                  //The latest price of 4x8 5/8in drywall panel

return price