Table

Purpose¶

Methods¶

Binding name: p6.table

get¶

Returns the fields and keys of the table.

Syntax

Map[String,List] p6.table.get(String tableId [, String appKey])

Example

def item = p6.table.get('Customers')
p6.log.debug 'Fields:'
item['fields'].each { p6.log.debug it }

p6.log.debug 'Keys:'
item['keys'].each { p6.log.debug it }

listAll¶

Returns all the table names.

Syntax

List<String> p6.table.listAll()

Note

If the item is related to an application, the name will be appkey.name.

Example

p6.table.listAll().each { p6.log.debug it }

list¶

Returns all table names of an application.

Syntax

List<String> p6.table.list([String appKey])

Tip

If no appKey is given or empty, only unbundled tables are listed.

Example

p6.table.list('O2C').each { p6.log.debug it }
p6.table.list().each { p6.log.debug it }

lookup¶

Performs a lookup on the given data table tableId using the Map of conditions (list of key=value).

The table records found are returned as an array of Maps of fieldName and values.

Syntax

Map[] p6.table.lookup(String tableId, Map conditions)

Example

def records = p6.table.lookup('Customers', [Cust_ERP_Identifier: 'MHP'])
println records

lookup (single field)¶

Performs a lookup on the given data table tableId using the Map of conditions (list of key=value). Only fieldName values will be returned.

Syntax

String[] p6.table.lookup(String tableId, Map conditions, String fieldName)

Example

if(p6.table.lookup('Customers', [Cust_ERP_Identifier: 'MHP'], 'Invoice_Aggregation') == ['true']){
   p6.log.debug 'Invoice aggregation is required!'
}

lookup (criteria)¶

New Feature

Since 6.10.25

Performs a lookup on the given data table tableId using a list of typed search criteria, so that a column can be compared with something else than an equality.

All the criteria are combined with a logical AND. The table records found are returned as an array of Maps of fieldName and values.

Each criterion is a Map holding:

Key	Description
`field`	Mandatory. The column name.
`op`	Optional, defaults to `eq`. The comparison to apply: `eq`, `ne`, `ieq` (case-insensitive equality), `lt`, `le`, `gt`, `ge`, `in`.
`value`	The value to compare with. Mutually exclusive with `values`.
`values`	The list of values to compare with, for `in`, `eq` and `ne`. Mutually exclusive with `value`. The comparison operators `lt`, `le`, `gt` and `ge` accept a single value.
`orEmpty`	Optional, defaults to `false`. When `true`, the records whose column is empty match as well, whatever the outcome of the comparison.

Syntax

Map[] p6.table.lookup(String tableId, List criteria)

Info

orEmpty is how an unbounded date is expressed. On a pricebook whose lines are valid from ValidFrom to ValidTo, a line with an empty ValidFrom is valid since forever, and must be returned by a search on a given date. It matches an empty column as well as a column left unset - which happens when the column was added to the table after the records were inserted.

Warning

Table values are stored as text, so the comparisons are string comparisons:

dates must be written in the zero-padded YYYY-MM-DD form, which is the only one comparing in chronological order. 2026-2-1 or 01/02/2026 will not compare as expected;
numeric ranges are not supported: [field: 'Quantity', op: 'gt', value: '9'] compares '10' with '9' as text and finds nothing;
ne does not return the records whose column is empty, unless orEmpty: true is set.

The contains operator is not available: the keyword search of the Tables service covers the full-text need, and requires a dedicated index.

Example

// All the pricebook lines of an article valid at a given date, an empty bound being unbounded
def date = '2026-02-01'

def lines = p6.table.lookup('Pricebook', [
        [field: 'ArticleCode', op: 'eq', value: articleCode],
        [field: 'CatalogCode', op: 'eq', value: catalogCode],
        [field: 'ValidFrom',   op: 'le', value: date, orEmpty: true],
        [field: 'ValidTo',     op: 'ge', value: date, orEmpty: true],
        [field: 'Status',      op: 'in', values: ['ACTIVE', 'DRAFT']]
])

p6.log.info "${lines.size()} pricebook line(s) found for ${articleCode} at ${date}"

lookup (criteria, single field)¶

New Feature

Since 6.10.25

Performs a lookup on the given data table tableId using a list of typed search criteria. Only fieldName values will be returned.

See lookup (criteria) for the description of a criterion.

Syntax

String[] p6.table.lookup(String tableId, List criteria, String fieldName)

Example

def prices = p6.table.lookup('Pricebook', [
        [field: 'ArticleCode', op: 'eq', value: articleCode],
        [field: 'ValidFrom',   op: 'le', value: date, orEmpty: true],
        [field: 'ValidTo',     op: 'ge', value: date, orEmpty: true]
], 'UnitPrice')

upsert¶

Upsert the records in the table named tabledId.

Syntax

void p6.table.upsert(String tableId, List<Map<String, String>> records)

Info

If a new record has the same values for the primary keys than an existing record, the existing record’s values are updated with the new ones. If not, the new record is added to the table. If the list of records is empty, nothing happens.

Example

p6.table.upsert('Customer', p6.table.csvToRecords([
  uri:'file:./src/test/Customer.csv'
]))

insert¶

Insert the records in the table named tabledId.

Syntax

void p6.table.insert(String tableId, List<Map<String, String>> records)

Info

If a new record has the same values for the primary keys as an existing record then an exception is raised. If not, the new record is added to the table. If the list of records is empty, nothing happens.

Example

p6.table.insert('Customer', [
    [Cust_ERP_Identifier: 'MHP', Invoice_Aggregation: 'true'],
    [Cust_ERP_Identifier: 'MPP', Invoice_Aggregation: 'false']
])

updateIndexes¶

Start the indexation job for the table. A P6Exception is thrown if the job is already running for this table.

!!! quote ‘Syntax”

String p6.table.updateIndexes(String tableId)

Example

p6.log.debug 'Indexing job: ' + p6.table.updateIndexes('Customers')

delete¶

Delete the records from the table named tabledId.

Syntax

void p6.table.delete(String tableId, List<Map<String, String>> records)

Info

If the list of records is empty, nothing happens.

Example

p6.table.delete('Customer', [
    [Cust_ERP_Identifier: 'MPP', Invoice_Aggregation: 'false']
])

deleteAll¶

Delete all the records from the table named tabledId.

Syntax

void p6.table.deleteAll(String tableId)

Example

p6.table.deleteAll('Customer')

toList¶

Performs a lookup on the given data table tableId returning all rows and values.

Syntax

Map[] p6.table.toList(String tableId)

Example

p6.table.toList('Customer').each { println it }

toXml¶

Performs a lookup on the given data table tableId returning all rows and values expressed as XML. xmlTemplateText is optional. Use this to override the internal XML template used.

Syntax

GPathResult p6.table.toXml(String tableId, String xmlTemplateText)

Example

println p6.table.toXml('Customer')

toDom¶

Performs a lookup on the given data table tableId returning all rows and values expressed as a DOM.

Syntax

org.w3c.dom.Document p6.table.toDom(String tableId[, String xmlTemplateText])

xmlTemplateText is optional. Use this to override the internal XML template used.

Default table XML structure

<table>
    <TABLENAME>
        <rows>
            <row id="0">
                <key id="FIELDNAME1">fvalue1</key>
                <key id="FIELDNAME2">fvalue2</key>
            </row>
        </rows>
    </TABLENAME>
</table>

Example

println p6.table.toDom('Customer')

recordsToCsv¶

Convert a list of records to a CSV file specified in the configuration map.

Syntax

void p6.table.recordsToCsv(Map<String, Object> configuration)

Parameter: configuration

Name	Description
records	Mandatory. List of records. See `lookup(String tableId, Map keys)` and `toList(String tableId)` methods
uri	Mandatory. A file to open and use as the CSV output (expressed as a string).
separator	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
quoteChar	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
escape	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
converter	Optional closure. Paramaters (String column, String value). Called each time a value is added to the CSV.

Example

p6.table.recordsToCsv([
  uri:'file:./src/test/Customer.csv',
  records: p6.table.list('Customer')
])

csvToRecords¶

Convert a CSV file specified in the configuration map into a list of records.

Syntax

List<Map<String, String>> p6.table.csvToRecords(Map<String, Object> configuration)

Parameter: configuration

Name	Description
uri	Mandatory. A file to open and use as the CSV output (expressed as a string).
separator	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
quoteChar	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
escape	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
skipLines	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
ignoreLeadingWhiteSpace	Optional. See: http://opencsv.sourceforge.net/apidocs/com/opencsv/CSVParserBuilder.html
encoding	Optional. The encoding to use when reading the file. The default is UTF-8.
converter	Optional closure. Parameters (String column, String value). Called each time a value is read from CSV.

Example

p6.table.upsert('Customer', p6.table.csvToRecords([
  uri:'file:./src/test/Customer.csv'
]))

toFile¶

New Feature

Since 6.9.2

Performs a lookup on the given data table tableId, write the records to a CSV file and return the file path.

Syntax

String toFile(String tableId)

Warning

Generated CSV file is saved in the temporary folder (cf. p6.tmp.folder). You should delete or move the file when processed.

Example

// Query the table service
String filePath = p6.table.toFile('BigTable')

// Open the file and create a CSVReader object
p6.csv.parse([uri: 'file://' + filePath]) { row ->
    p6.log.debug(row.values().join(", "))
    true
}

new File(filePath).delete()

lookupToFile¶

New Feature

Since 6.9.2

Performs a lookup on the given data table tableId using the Map of conditions (list of key=value), write the records to a CSV file and return the file path.

Syntax

String lookupToFile(String tableId, Map conditions)

Warning

Generated CSV file is saved in the temporary folder (cf. p6.tmp.folder). You should delete or move the file when processed.

Example

// Query the table service
String filePath = p6.table.lookupToFile('BigTable', ['active': 'true'])

// Open the file and create a LineNumberReader object
LineNumberReader reader = new LineNumberReader(new FileReader(filePath))

// Skip to the end of the file to get the line count
reader.skip(Long.MAX_VALUE)

// Get the line count
int numLines = reader.getLineNumber()

// Close the file and the reader
reader.close()
new File(filePath).delete()

p6.log.debug('Number of lines in the CSV file: ' + numLines)

lookupToFile (criteria)¶

New Feature

Since 6.10.25

Performs a lookup on the given data table tableId using a list of typed search criteria, write the records to a CSV file and return the file path.

See lookup (criteria) for the description of a criterion, and for the string comparison caveats.

Syntax

String lookupToFile(String tableId, List criteria)
String lookupToFile(String tableId, List criteria, String fieldName)

Warning

Generated CSV file is saved in the temporary folder (cf. p6.tmp.folder). You should delete or move the file when processed.

Example

String filePath = p6.table.lookupToFile('BigPricebook', [
        [field: 'ValidFrom', op: 'le', value: '2026-02-01', orEmpty: true],
        [field: 'ValidTo',   op: 'ge', value: '2026-02-01', orEmpty: true]
])

try {
    new File(filePath).eachLine { p6.log.debug it }
} finally {
    new File(filePath).delete()
}

lookupToFile (single field)¶

New Feature

Since 6.9.2

Performs a lookup on the given data table tableId using the Map of conditions (list of key=value), write only the fieldName values to a CSV file and return the file path.

Syntax

String lookupToFile(String tableId, Map conditions, String fieldName)

Warning

Generated CSV file is saved in the temporary folder (cf. p6.tmp.folder). You should delete or move the file when processed.

Example

// Query the table service
String filePath = p6.table.lookupToFile('BigTable', ['active': 'true'], 'SupplierName')

// Open the file and create a LineNumberReader object
LineNumberReader reader = new LineNumberReader(new FileReader(filePath))

// Skip to the end of the file to get the line count
reader.skip(Long.MAX_VALUE)

// Get the line count
int numLines = reader.getLineNumber()

// Close the file and the reader
reader.close()
new File(filePath).delete()

p6.log.debug('Number of lines in the CSV file: ' + numLines)