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")
println "Fields:"
item['fields'].each { println it }

println "Keys:"
item['keys'].each { println 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 { println 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 { println it }
p6.table.list().each { println 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"]){
   println "Invoice aggregation is required!"
}

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.

Syntax

String p6.table.updateIndexes(String tableId)

Example

println "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¶

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

import com.opencsv.CSVReader

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

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

// Read the CSV data line by line
String[] line
while ((line = reader.readNext()) != null) {
    println(line.join(", "))
}

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

lookupToFile¶

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

println("Number of lines in the CSV file: $numLines")

lookupToFile (single field)¶

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

println("Number of lines in the CSV file: $numLines")