Data Reading Functions
Prerequisites
Before reading this section you should make yourself familiar with the Filters for Data Reading because filters are used as arguments in almost all search calls.
You may also want to take a quick look at type codes prior to reading the following sections.
Overview of Search Methods
In the Groovy API, the general purpose search methods are:
// Basic syntax find(String typeCode, Filter ... filters) Â // Allows specifying a starting row find(String typeCode, int startRow, Filter ... filters) Â // Allows specifying a sorting field find(String typeCode, int startRow, String sortBy, Filter ... filters) Â // Allows limiting the number of retrieved rows find(String typeCode, int startRow, int maxRows, String sortBy, Filter ... filters) Â // Allows retrieving only specific fields find(String typeCode, int startRow, int maxRows, String sortBy, List<String> fields, Filter ... filters)
When using this form of the function:
find(String typeCode, int startRow, int maxRows, String sortBy, List<String> fields, Filter ... filters)
i.e., the one with the "fields" parameter, you get back "full objects" which are transferred through type conversion.
If you use the "fields" parameter, you get only raw data for attributeXX fields, which is always a string. That means you will get a hashmap where all values are strings – if you have numbers in the table, you will get back a string!
Some other more specific search methods are:
productExtension(String extensionName, Filter ... filters) // searches product extensions customerExtension(String extensionName, Filter ... filters) // searches customer extensions findLookupTable(String tableName) // searches price parameter tables findLookupTableValues(String tableName, Filter ... filters) // searches price parameter tables' values productCompetition(Filter ... filters) // searches competition data productXRef(Filter ... filters) // searches product references findManualPricelists(Date targetDate, String listName) // searches manual price lists findPriceGrids(Date targetDate, String priceGridName) // searches live price grids findPricelists(Date targetDate, String listName) // searches price lists count(String typeCode, Filter ... filters) // retrieves number of records in an entity
For the complete list of available methods, please refer to the most recent JavaDoc API.
Return Types
All methods, with the exception of count
, always return a (possibly empty) collection of objects matching the passed-in filter criteria. The structure of each object depends on the structure of the underlying Java class.
Type Codes
The first parameter of the method api.find() is always a Type Code. This short code identifies the entity to search. The most common type codes you will encounter are the following ones:Â
Type Code | Description |
---|---|
P | Product Master |
PX | Product Extension (see Searching in Product Extensions below) |
C | Customer Master |
CX | Customer Extension |
PL | Price List |
PLI | Price List Item |
MPL | Manual Price List |
MPLI | Manual Price List Item |
PG | Live Price Grid |
PGI | Live Price Grid Item |
CA | Customer Assignments |
Q | Quote |
For the full list of type codes, see Type Codes.
For searching in Price Parameters, you should almost exclusively use specialized methods api.findLookupTable
and api.findLookupTableValues
. For more details see Searching in Price Parameters below.
Using Attribute Names in Searches
When working with attributes, instead of their database field names you can use technical names in api.find filters. In this case, column names are also returned as keys.
- The api.find/stream methods accept field names from the meta data (custom column names).
- The api.find/stream methods accept sort column names from the meta data.
- When field names are used and a raw DB result is returned, the resulting object has keys from the meta data.
- When loading a list of entities (a result of api.find/stream), the function
List api.namedEntities(List)
has to be called to convert the result of api.find to a list of maps that have keys from the meta data.
If you use the api.find/stream without the List of fields, you get Objects representing the result which can be converted to a List of hashmaps with named fields using api.namedEntities() or iterator.nextNamedEntity().
Limitations of api.find()
There are several things you have to be aware of when using the search methods:
- The number of rows the method api.find can return is limited by a server parameter. It is configurable per partition and by default it is set to 200. You can retrieve the max rows by calling api.getMaxFindResultsLimit. Read how to overcome this limitation below.
 A warning is displayed if the number of returned rows returned equals 200 or formulaEngine.script.findMaxResults and the maxRows parameter of the find was not specified (=0). - When filtering the attributed fields (attribute1, ..., attribute30), you need to pass strings to filters because all attributed fields are stored in the database as strings. This is mainly an issue with Dates. Find out how to convert them properly below.
- Because of the previous point, you cannot use sorting on numbers (integers, real) because api.find returns and sorts the data as strings. When sorting ($100, $70, $25) ascending, the result will be ($100, $25, $70).
Sorting
If you need to sort the data by a specific column, use the method overload which allows you to specify the sortBy parameter. You can sort by several attributes, separated by commas.
api.find("P", 0, "attribute1,attribute2", Filter.lessOrEqual("attribute2", "1000")) // ascending api.find("P", 0, "-attribute1", Filter.lessOrEqual("attribute2", "1000")) // descending - include minus sign api.find("QLI",0,"inputs.Amount.value") // sorting on inputs in Quote line items
api.find("Q", Filter.equal("uniqueName", "P-1457"))[0]
Loading More Than 200 Rows
There is a limit on how many rows the method api.find()
 can retrieve. It is configurable per partition and is by default set to 200 rows. You can retrieve the current settings by calling api.getMaxFindResultsLimit()
.
If you want to load more rows (possibly all), use the following code snippet:
def all = [] int start = 0 while ( items = api.find(type, start, NULL, filters) ) { start += items.size() all.addAll(items) } return all
Or you can use the syntax with the maxRows parameter:
def all = [] int start = 0 int maxRows = api.getMaxFindResultsLimit() while ( items = api.find(type, start , maxRows, "id", filter) ) { start += items.size() all.addAll(items) } return all
In this case, using the sortBy field is strongly recommended. In some cases, paging can be broken and you might get some objects twice and some not at all. This has been experienced on large PXs (500k+ rows) on Oracle.
api.find(type, start, maxRows, "id", filter)
Searching in Product/Customer Extensions
There are two ways to search in Product (PX) or Customer (CX) Extensions. If you do not need advanced features like sorting, you should always use the method api.productExtension()
or api.customerExtension()
.
For example:
api.productExtension("ListPrices", Filter.greaterOrEqual("attribute1", api.targetDate().format("yyyy-MM-dd"))) api.customerExtension("Addresses", Filter.equal("attribute1", "Czech Republic"))
When you use either of these methods, the sku
or customerId
is filtered automatically. If you need to search for a different sku, you need to use api.find().
However, there are cases when you need more flexibility and you need to use api.find(). Then you need to specify the PX/CX name as a filter because the data of all Product Extensions are stored in one DB table. The same applies to Customer Extensions.
See the example below:
api.find("PX", 0, "attribute1", Filter.equal("name", "ListPrices"), Filter.greaterOrEqual("attribute1", api.targetDate().format("yyyy-MM-dd"))) // sorting on attribute1 api.find("CX", 0, "attribute1", Filter.equal("name", "Addresses"), Filter.equal("attribute1", "Czech Republic")) // sorting on attribute1
In case of global PX:
api.find("PX", Filter.equal("name", "GLOBAL.ListPrices"), Filter.greaterOrEqual("attribute1", api.targetDate().format("yyyy-MM-dd")))
Searching in Price/Company Parameters
Again, there are two ways to search in Price/Company Parameters (PP). If you do not need advanced features, you should always use the method api.findLookupTableValues().
For example:
api.findLookupTableValues("Rabatt", Filter.greaterThan("value", 20))
It will automatically pick the currently valid and active price parameter "Rabatt" and list all values whose "Rabatt" is greater than 20.
Sometimes, however, you need to use an advanced feature. In such cases, you need to specify the ID of the lookup table as a filter.
def table = api.findLookupTable("Rabatt") // first retrieve the currently valid and active table def values = api.find("LTV", 0, "name", Filter.equal("lookupTable.id", table.id)) // use the ID of the table
As there can be more than one "Rabatt" price parameter, we need to get the handle of the currently valid and active one. We can then use the handle when calling api.find().
The type code differs per price/company parameter type. The following table summarizes when to use which code.
Type Code | Price Parameter Type |
---|---|
LTV | SIMPLE or RANGE |
MLTV | MATRIX with value type MATRIX |
MLTV2 | MATRIX with value type MATRIX2 |
MLTV3 | MATRIX with value type MATRIX3 |
MLTV4 | MATRIX with value type MATRIX4 |
MLTV5 | MATRIX with value type MATRIX5 |
MLTV6 | MATRIX with value type MATRIX6 |
You can find the list of all type codes here.
Searching for Dates in Attributed Fields
If you use a date in your filter, there is one thing you need to be aware of when filtering attributed fields with Date or Timestamp type (i.e., attribute1, attribute2, ... attribute30). Since the values in attributed fields are stored as strings in the database, you need to convert your Date object into a proper String representation. The comparison in the database is then lexicographical but because it follows formatting standards it works well.
The String format you need to use differs per date type. See the following table:
Date Type | Date Format | Example |
---|---|---|
Date | yyyy-MM-dd | api.targetDate().format("yyyy-MM-dd") |
Timestamp | yyyy-MM-dd'T'HH:mm:ss | api.targetDate().format("yyyy-MM-dd'T'HH:mm:ss") |
Example: Let's have a Product Extension called List Prices. Let's assume the following structure:
Part-Id | Valid From (attribute1) | Valid To (attribute2) | Price (attribute3) |
---|---|---|---|
000001 | 1/1/2015 | 30/6/2015 | 123.00 |
000001 | 1/7/2015 | 31/12/2015 | 130.00 |
The Valid From and Valid To fields are of the Date type.
In the following example we want to find a list price valid on the target date. As stated earlier, we need to convert the Date into a proper String representation.
def date = api.targetDate().format("yyyy-MM-dd") // notice the conversion to a string def products = api.find("P", Filter.equal("name", "List Prices"), Filter.greaterOrEqual("attribute1", date)) if (products) { return products[0].attribute3 // return the price of the first product in the list } return null
The key part of the snippet is the call of the method format("yyyy-MM-dd"). If the fields were of the Timestamp type, you would use format("yyyy-MM-dd'T'HH:mm:ss") instead.
Found an issue in documentation? Write to us.
Â