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 = 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 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 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 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 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)
    ]
]

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