Processing manual data edits

☝

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.

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)