Read / Lookup / Search / Find Functions

def currentItem = api.currentItem()
def currentSku = currentItem != null ? currentItem.attribute20 : api.product("attribute20")
 
def mplId = api.currentItem("pricelistId")
 
if  (api.currentItem("rebateType.uniqueName")=="MY2") {

Notes:

• It is only suitable for calculation logics which are called for every item of a list (e.g., Price List, Live Price Grid, Quote, CFS, etc.). It might not work in Test Drive during debugging. Always check it the function does not return null.
• In case of an Analytics Data Load logic, currentItem() returns a map representing the Data Load definition (allowing to access properties such as targetFilter).

Filters are used when searching for specific rows in data-tables.

api.filter() provides the same functionality as "Filter API" functions (see Filters for Data Reading), it is just a different way to do the same.

We have two versions:

• With 2 parameters which always use the "equal" operator.
• With 3 parameters where you must specify the comparison operator.

def mainMplis = api.find("MPLI", 0, "sku", 
                            api.filter("pricelistId", "=", c.assignmentId), 
                            api.filter("sku", "=", sku[0..7] ))
 
def dmLookupResult =  api.datamartLookup("DMQuery",
   api.filter(api.getElement("FilterField"),"=",api.product("Artikelnummer")),
   api.filter("PricingDateYear","=",api.getElement("LastYear")),
   "Quantity\$")
 
def cas = api.find("CA",api.filter("assignmentId",mplId),api.filter("assignmentType","MPL"))
 
datamartQuery.where(api.filter("PricingDate",">",targetDate.minus(365).format("yyyy-MM-dd")))
 
def expiryFilter = Filter.or(api.filter("expiryDate",">=",targetDate), Filter.isNull("expiryDate"))
activeMPLs = api.find("MPL", startRow, api.filter("status","=","Active"), 
                      api.filter("validAfter","<=",targetDate), expiryFilter)
 

Finds and returns multiple rows of the given table. When you provide a filter, it will return only the filtered set of rows.

This function has many various parameters, see Public Groovy API for the full list of options.

def StockPX = api.find("PX", 0, 1, "-attribute2", ["attribute4", "attribute9", "attribute15"], 
                       Filter.equal("name", "DWHStockReport"), Filter.equal("attribute1", "OFF"),
                       Filter.equal("sku", api.product("sku")), Filter.greaterOrEqual("attribute2", date)
                      )
 
def filters = [
    Filter.equal("lookupTable.id", tableId),
    Filter.equal("name", elementName)
]
return api.find("MLTV", 0, 1, null, ["attribute1", "attribute2", "attribute3", "attribute4", "attribute5"], *filters)

Important: If the result set of rows is too big, then this function does not return all the rows, but only a certain amount of them (see api.getMaxFindResultsLimit()) – if this happens a warning is displayed. If you expect a long result set, you can either use the loop example (below) or the api.stream() function.

def startRow = 0
  while ( products = api.find("P", startRow, <filters>) ) {
    startRow += products.size()
    for(def product : products){
    // ...
  }
}

  Instead of DB field names (e.g., attribute9) you can use the custom names created by user.

Returns the value of the given attribute of the current (or given) Product.

def sku = api.product("sku")
 
def pk = api.product("Pricing key")
 
def at = api.product("attribute12")
 
def currentItem = api.currentItem()
def cost = api.product("Cost", currentItem.sku)

If you run the code in Test Drive, you can select the context. If you select "Product", Test Drive will ask you to pick the product to be used.

Note: To specify which attribute of the product you want, you can use both the attribute name and the column name. The column label cannot be used.

Finds and returns the value from a Price Parameter.

Example with One Parameter

Given that you have a "simple" price parameter with the "Country adj for price list" label

return api.vLookup("CountryAdj_Example")

When you make a new Price List, then in the second step "Get Parameters", you will see an input drop down field "Country adj for price list" and when you select "CZ", the return value of the function is a float number 0.01 (which represents 1%).

Example with Two Parameters

Given that you have a simple (i.e., it has only key and value) pricing parameter "Shipping_Costs" with a String key.

When you run the following code

ownShippingCost = api.vLookup( "Shipping_Costs", api.product("attribute25").toString() )

then you will get the value for the key calculated by api.product("attribute25").toString().

Example with Three Parameters

Given that you have a Matrix (with one key and many attributes) pricing parameter "PriceList_GE".

When you run the following code

def prevailing = api.vLookup("MixThreshold", "attribute1", "Prevailing")

then you get the value of the attribute "attribute1" from the row with the key == "Prevailing".

Example with a Map of Keys

If you have a matrix with several keys, you can pass the keys as a map and you get back a map with the values of the columns of attributeNames as keys or full object if attributeNames is null.

When you have the following Price Parameters table

and run the following code

api.vLookup("GroupCountry", [“Margin Adj”, “Min Margin“], ["Product group": "Beef", "Country": "Germany"])

then you get

["Product group": "Beef", "Country": "Germany", “Margin Adj” : 0.05, “Min Margin“ : 0.2]