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)

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: 

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.
  (information) 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

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.

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:

Example: Let's have a Product Extension called List Prices. Let's assume the following structure:

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.