Tables

The platform6.tables service allows to edit and visualize the cross-references tables.

Read the user guide of the Tables service to discover its operating mode.

List a table’s records

This resource looks up a table’s records.

Parameters

Headers of the request

Warning

All the header’s values must be strings!

Header’s key	Description
platform6.request.action	The action to perform (required, expected value list.records)
tableId	The table’s identifier (required)
fields	A records of conditions used to filter the retrieved records
offset	The number which indicates the distance between the table’s first record and the first record displayed (default to 0)
limit	The number of the retrieved records (by default, all the records are returned)
orderByFieldName	The field by which the records are sorted
sortAscending	Whether the sort is ascending or descending (default to true)
platform6.request.user	The email address of the user sending the message

Headers of the response

Header’s key	Description
results	The retrieved table’s records
fields	The table’s fields

Examples

We’ll use the Exchange_rates table for the examples.

Currency_From	Currency_To	Rate
ARS	USD	0.069
AUD	USD	0.724508
BDT	USD	0.013
BRL	USD	0.25

The call below will list all the table’s records without further settings.

def cm = [
    headers: [
        'platform6.request.action': 'list.records',
        'tableId': 'Exchange_rates'
    ]
]

def request = p6.service.request('platform6.tables', cm)
print request.headers['results']
print request.headers['fields']

The output will be:

INFO [root] stdout: [["ARS","USD","0.069"],["AUD","USD","0.724508"],["BDT","USD","0.013"],["BRL","USD","0.25"]]
INFO [root] stdout: ["Currency_From","Currency_To","Rate"]

If the table is not found, it will return an error:

{
    "message" : "Requested table is not found: 'Exchange_rates'.",
    "stackTrace" : [
        "com.amalto.b2box.core.api.B2boxException: Requested table is not found: 'Exchange_rates'.",
        "    at com.amalto.service.tables.TablesAction$class.recordsRecords(TablesAction.scala:29)",
        "    at com.amalto.service.tables.TablesService.recordsRecords(TablesService.scala:66)",
        "    at com.amalto.service.tables.TablesService.notifyRequestMessage(TablesService.scala:161)",
        "    at com.amalto.b2box.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at com.amalto.b2box.core.impl.platform.messagebus.BusQueueController$ServiceQueueRunner.run(BusQueueController.java:183)",
        "    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)",
        "    at java.util.concurrent.FutureTask.run(FutureTask.java:266)",
        "    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)",
        "    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)",
        "    at java.lang.Thread.run(Thread.java:748)"
    ]
}

Here are some examples with additional parameters:

1. The parameter fields is set to [ Rate: '0.15' ]: it will return all the records for which the Rate field has the value 0.15.

   import groovy.json.JsonOutput
   
   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'fields': JsonOutput.toJson([ Rate: '0.15' ])
       ]
   ]
   
    print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["CNY","USD","0.15"],["DKK","USD","0.15"]]
   

2. The parameter offset is set to 1: it will return all the records after the first record excluded.

   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'offset': '1'
       ]
   ]
   
    print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["AUD","USD","0.724508"],["BDT","USD","0.013"],["BRL","USD","0.25"]]
   

3. The parameter limit is set to 2: it will return the two first records of the table.

   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'limit': '2'
       ]
   ]
   
   print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["AUD","USD","0.724508"],["ARS","USD","0.069"]]
   

4. The parameters orderByFieldName is set to Rate and sortAscending is set to false: it will sort the records in descending order by the Rate field.

   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'orderByFieldName': 'Rate',
           'sortAscending': 'false'
       ]
   ]
   
   print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["AUD","USD","0.724508"],["BRL","USD","0.25"],["ARS","USD","0.069"],["BDT","USD","0.013"]]
   

Upsert records

Upsert records in a table.

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.

Parameters

Headers of the request

Header’s key	Description
platform6.request.action	The action to perform (required, the expected value upsert.records)
tableId	The table’s identifier (required)
records	A new set of records to insert into the table
platform6.request.user	The email address of the user sending the message

Headers of the response

No headers are returned in the response.

Examples

We’ll use again the Exchange_rates table for the example.

Currency_From	Currency_To	Rate
ARS	USD	0.069
AUD	USD	0.724508
BDT	USD	0.013
BRL	USD	0.25

The fields Currency_From and Currency_To are the table’s primary keys.

import groovy.json.JsonOutput

def records = []

def record1 = [
    Currency_From: 'CAD',
    Currency_To: 'USD',
    Rate: '0.728980'
]
records << record1

def record2 = [
    Currency_From: 'CHF',
    Currency_To: 'USD',
    Rate: '1.03'
]
records << record2

def record3 = [
    Currency_From: 'ARS',
    Currency_To: 'USD',
    Rate: '0.15'
]
records << record3

def cm = [
    headers: [
        'platform6.request.action': 'upsert.records',
        'tableId': 'Exchange_rates',
        'records': JsonOutput.toJson(records)
    ]
]

p6.service.request('platform6.tables', cm)

Since the record3‘s primary keys already exist in the table, the field Rate will be updated with the new value.

The primary keys of the records record1 and record2 do not already exist in the table so the two records will be added to the table.

The resulting table will be:

Currency_From	Currency_To	Rate
ARS	USD	0.15
AUD	USD	0.724508
BDT	USD	0.013
BRL	USD	0.25
CAD	USD	0.728980
CHF	USD	1.03