Read / Lookup / Search / Find Functions
The following api. functions are available:
When searching table... | Use this function... | Code example |
---|---|---|
BoM | bom | |
BoM | bomList | |
Most Master Data tables | count | |
PL, MPL, PG and SIM | createElementNameFilter | |
Reference to the currently processed line (i.e., can refer to many types of tables – products, customers, pricelistitem, quoteitem,...) | currentItem | 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:
|
The same as currentItem() | currentItemByElementName | |
C | customer | |
CX | customerExtension | |
PR, CT, CTLI, RBA, RBALI | customerToRelatedObjectsFilter | |
All tables which work with CFS, Calculation Flow | customEvent | |
filter | 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:
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) | |
All tables (except QuoteLineItem and some special ones) | find | 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. Example of iteration through all rows 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. |
PLI, XPLI | findApprovedPricelistItems | |
CFS | findCalculatedFieldSets | |
CA | findCustomerAssignments | |
DL | findDataLoad | |
LT | findLookupTable (returns ID) | |
LTV | findLookupTableValues (returns rows) | |
MPL | findManualPricelists | |
PG | findPriceGrids | |
PL | findPricelists | |
findRebateRecordLoad | ||
SIM | findSimulations | |
Line items of Q, CT, RA | getCalculableLineItem | |
getCalculableLineItemCollection | ||
getCalculableLineItemResult | ||
getItemCompleteCalculationResults | ||
getMaxFindResultsLimit | ||
getPriceGridSummaryQuery | ||
getPricelistItem | ||
getPricelistSummaryQuery | ||
getProductReferences | ||
getProperty | ||
getPropertyByElementName | ||
getRebateRecordSummaryQuery | ||
Matrix PL | getSecondaryKey | |
getSimulationSummaryQuery | ||
PL | pricelist | |
PL | pricelistItem | |
P | product | 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. |
PCOMP | productCompetition | |
PX | productExtension | |
productToRelatedObjectsFilter | ||
productXRef | ||
relatedObjectToCustomersFilter | ||
relatedObjectToProductsFilter | ||
Roles | roles | |
runSummaryQuery | ||
Most tables | stream | |
Most tables | update | |
U | user | |
LTV | vLookup | Finds and returns the value from a Price Parameter. Example with One ParameterGiven that you have a "simple" price parameter with the "Country adj for price list" label and a calculation logic for a Price List with this code: 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 ParametersGiven 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 Example with Three ParametersGiven 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 KeysIf 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] |
Found an issue in documentation? Write to us.
Â