Read / Lookup / Search / Find Functions
The following table provides a comprehensive guide to available Groovy API methods for reading, looking up, searching, and finding data within the application. It details the usage of each function, including examples and notes on deprecated methods and their modern replacements using the QueryAPI.
Table (when searching...) | Public Groovy API | Code Example |
---|---|---|
BoM |
| |
BoM |
| |
Most Master Data tables |
| |
PL, MPL, PG and SIM |
| |
Reference to the currently processed line (i.e., can refer to many types of tables – products, customers, pricelistitem, quoteitem,...) |
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() |
| |
C | The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiCustomer for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | |
CX | The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the customerExtensionRows for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | |
PR, CT, CTLI, RBA, RBALI |
| |
All tables which work with CFS, Calculation Flow |
| |
| 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) | 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 rowsdef 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. The method api.find() will be deprecated in the future releases; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() for new implementations. It is already possible for some tables. For more information see the list of supported tables and example of queryApi() implementation. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained.
| |
PLI, XPLI |
| |
CFS |
| |
CA |
| |
DL |
| |
LT | findLookupTable (returns ID) | The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiCompanyParameterRow for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. |
LTV | findLookupTableValues (returns rows) | The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the companyParameterRows for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. |
MPL |
| |
PG |
| |
PL |
| |
| ||
SIM |
| |
Line items of Q, CT, RA
| The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the quoteLineItems for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | |
| ||
The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the quoteLineItems for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | ||
The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the completeCalculationResults for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | ||
| ||
The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiPriceGridLineItem for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | ||
The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the conditionRecords for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | ||
The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiPriceListLineItem for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | ||
| ||
| ||
| ||
The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiPriceListLineItem for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | ||
Matrix PL |
| |
| ||
PL |
| |
PL | The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiConditionRecord for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | |
P | 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 the "sku" parameter is not specified, it tries to find the current product SKU from the context. The product SKU is available in many contexts – Price List Row, Price Grid Row, CFS (on the Products), Quote Line Item. If the product is not available in the context, the user will be prompted for an input. 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. The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiProduct for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | |
PCOMP |
| |
PX
| The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the productExtensionRows for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. | |
| ||
| ||
| ||
| ||
Roles |
| |
| ||
Most tables |
| |
Most tables |
| |
U |
| |
LTV | 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] The method has been deprecated in version 14.0; however, it will remain available to ensure backward compatibility. We strongly recommend starting to adopt the queryApi() with the QapiCompanyParameterRow for new implementations. Note that while the deprecated method does not entail the removal of functionality, it will no longer be maintained. For more information see the example queryApi() implementation. |
Found an issue in documentation? Write to us.