<aside> ☝ This pattern always Peliqan to be used as a unified data cockpit where SaaS data from multiple platforms (CRM, accounting ERP…) is viewed AND updated in a central place, and where these data edits are written back to the SaaS applications.

</aside>

Users can manually make updates to data in tables in Peliqan, for example edit cells, add rows, add columns etc. These changes are stored in a “Change log” which can be viewed by selecting “View changes” for a table. All these changes are applied on top of the source data, each time the data is queried in Peliqan.

Using a writeback script, it is possible to push these data updates back to the source.

Fetch the table changes in a script:

pq.list_changes(table_id=1234)
pq.list_changes(table_id=1234, status="NOT_PROCESSED")
pq.list_changes(table_id=1234, status=["NOT_PROCESSED", "PROCESSED"])
pq.list_changes(table_id=1234, type="i")
pq.list_changes(table_id=1234, type=["i","u"])

Update the status of a single Change record, in order to avoid processing it again in the next run of the script:

pq.update_change(change_id=1234, status="PROCESSED")

Note: pq.update_change() should not be confused with pq.update_cell() which is used to insert a new record in the change log.

Example where manual changes are written back to a SaaS business application:

# Update a few cells in a table "Products" from Odoo (ERP, SaaS)
# For testing only, normally data edits are done by a user in the spreadsheet
for row_id, value in [(5, "Lamp"), (6, "Car"), (7, "Pen")]:
    pq.update_cell(
			table_name = 'products', 
			field_name = 'name', 
			row_id = row_id,  # must match a value in primary key column of table
			value = value)
    
# Get the change list of a table
changes = pq.list_changes(table_name = 'products', writeback_status = ['NOT_PROCESSED', 'FAILED'])

# Update the SaaS business application (Odoo)
connection = pq.connect("Odoo Writeback")
for change in changes:
    kwargs = {
      'id': change['row_pk'],
      change['field_name']: change['values']
    }
    response = connection.update('product', **kwargs)

    st.text(f"Response for change_id({change['id']}): {response}")
    if response['status'] == "error":
      pq.update_writeback_status(
				table_name = 'products', 
				change_id = change['id'], 
				writeback_status = 'FAILED')
    else:
      pq.update_writeback_status(
				table_name = 'products', 
				change_id = change['id'], 
				writeback_status = 'PROCESSED')

    
# Show the result (optional)
st.text("Failed Changes")
failed = pq.list_changes(table_name = 'products', writeback_status = ['FAILED'])
st.dataframe(failed)

st.text("Processed Changes")
processed = pq.list_changes(table_name = 'products', writeback_status = ['PROCESSED'])
st.dataframe(processed)