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