Editing Perspective Table Datasets with Difference Styling
This allows a user to edit a dataset, view, and accept or reject the changes in a Git diff kind of manner. I'm sure some optimizations can be added to my example.
The example view has a rawData custom prop that holds the original data, an EditData prop for the live edited data, and a diffData dataset prop that shows the difference between the two.
The diffChanges prop holds only the changes for reviewing and auditing.
Getting all Data Edits
// Left join original → edited on the PK
// Alias both sides to avoid column name clashes
// unionAll three categories: DELETED, MODIFIED, ADDED
unionAll(
// Column schema for a edit list
asMap(
"change_type", "String",
"primary_key", "String",
"column", "String",
"old_value", "String",
"new_value", "String"
),
// DELETED: in original, not in edited ------------------------------------------//
forEach(
where(
leftJoin(
alias({view.custom.rawData}, "raw_"),
alias({view.custom.editData}, "edit_"),
it()["raw_city"],
it()["edit_city"]
),
it()["edit_city"] = null
),
asList(
"DELETED",
it()["raw_city"],
null,
null,
null,
it()["raw_city"]
)
),
// MODIFIED: in both, but have cell differences----------------------------------//
flatten(
forEach(
// Outer loop: joined rows where at least one cell differs
where(
leftJoin(
alias({view.custom.rawData}, "raw_"),
alias({view.custom.editData}, "edit_"),
it()["raw_city"],
it()["edit_city"]
),
it()["edit_city"] != null,
len(
where(
columnsOf({view.custom.rawData}),
toString(it(1)["raw_" + it()[0]]) != toString(it(1)["edit_" + it()[0]])
)
) > 0
),
// Inner loop: one result row per changed cell in this joined row
forEach(
where(
columnsOf({view.custom.rawData}),
toString(it(1)["raw_" + it()[0]]) != toString(it(1)["edit_" + it()[0]])
),
asList(
"MODIFIED",
it(1)["raw_city"],
it()[0],
toString(it(1)["raw_" + it()[0]]),
toString(it(1)["edit_" + it()[0]])
)
)
)),
// ADDED: in edited, not in original ----------------------------------------------//
forEach(
where(
leftJoin(
alias({view.custom.editData}, "edit_"),
alias({view.custom.rawData}, "raw_"),
it()["edit_city"],
it()["raw_city"]
),
it()["raw_city"] = null
),
asList(
"ADDED",
it()["edit_city"],
null,
null,
it()["edit_city"]
)
)
)
Note that some column must act like a primary key, this could be abstracted from the expression into a separate prop.
Adding deleted rows to the edited data and color code
forEach(
// This adds deleted rows to the edited data so the data diff view can display it
unionAll(
// Schema comes from the edited dataset
columnsOf({view.custom.editData}),
// All edited rows
{view.custom.editData},
// Only deleted rows from original
where(
{view.custom.rawData},
len(
where(
{view.custom.dataChanges},
it()["primary_key"] = it(1)["city"],
it()["change_type"] = "DELETED"
)
) > 0
)
),
// convert dataset to JSON for custom coloring
asMap(
forEach(
asMap(it()),
it()[0],
asMap(
'value', it()[1],'style',
asMap(
'backgroundColor',
case(true,
len(where({view.custom.dataChanges},
it()['primary_key'] = it(2)['city'] && it()['change_type'] = 'ADDED')) > 0, 'lawngreen',
len(where({view.custom.dataChanges},
it()['primary_key'] = it(2)['city'] && it()['column'] = it(1)[0])) > 0, 'khaki',
len(where({view.custom.dataChanges},
it()['primary_key'] = it(2)['city'] && it()['change_type'] = 'DELETED')) > 0, 'lightcoral',
''
)
)
)
)
)
)
Example v.81 Perspective View
[
{
"type": "ia.container.flex",
"version": 0,
"props": {
"direction": "column"
},
"meta": {
"name": "root"
},
"position": {},
"custom": {},
"children": [
{
"type": "ia.container.flex",
"version": 0,
"props": {
"alignItems": "center"
},
"meta": {
"name": "HeaderFlex"
},
"position": {
"basis": "50px"
},
"custom": {},
"children": [
{
"type": "ia.display.label",
"version": 0,
"props": {
"text": "Data Diff (Edits are made in this table)",
"textStyle": {
"fontWeight": "bold"
}
},
"meta": {
"name": "Label"
},
"position": {
"grow": 1
},
"custom": {}
},
{
"type": "ia.input.button",
"version": 0,
"props": {
"text": "Submit Edits",
"primary": false,
"image": {
"icon": {
"path": "material/save_alt",
"color": "limegreen"
}
}
},
"meta": {
"name": "Button_0"
},
"position": {
"shrink": 0,
"basis": "125px"
},
"custom": {},
"propConfig": {
"props.enabled": {
"binding": {
"type": "expr",
"config": {
"expression": "len({view.custom.dataChanges}) > 0"
}
}
}
},
"events": {
"component": {
"onActionPerformed": {
"type": "script",
"scope": "G",
"config": {
"script": "\tself.view.custom.rawData = self.view.custom.editData"
}
}
}
}
},
{
"type": "ia.input.button",
"version": 0,
"props": {
"text": "Reject Edits",
"primary": false,
"image": {
"icon": {
"path": "material/restore_from_trash",
"color": "lightcoral"
}
}
},
"meta": {
"name": "Button"
},
"position": {
"shrink": 0,
"basis": "120px"
},
"custom": {},
"propConfig": {
"props.enabled": {
"binding": {
"type": "expr",
"config": {
"expression": "len({view.custom.dataChanges}) > 0"
}
}
}
},
"events": {
"component": {
"onActionPerformed": {
"type": "script",
"scope": "G",
"config": {
"script": "\tself.view.refreshBinding('custom.editData')"
}
}
}
}
}
]
},
{
"type": "ia.container.flex",
"version": 0,
"props": {
"alignItems": "center"
},
"meta": {
"name": "NewRowFlex"
},
"position": {
"basis": "50px"
},
"custom": {},
"children": [
{
"type": "ia.input.text-field",
"version": 0,
"props": {
"placeholder": "city..."
},
"meta": {
"name": "TextField_City"
},
"position": {
"grow": 1,
"basis": "25%"
},
"custom": {},
"propConfig": {
"props.text": {
"binding": {
"type": "property",
"config": {
"path": "view.custom.newRow_City",
"bidirectional": true
}
}
}
}
},
{
"type": "ia.input.text-field",
"version": 0,
"props": {
"placeholder": "country..."
},
"meta": {
"name": "TextField_County"
},
"position": {
"grow": 1,
"basis": "25%"
},
"custom": {},
"propConfig": {
"props.text": {
"binding": {
"type": "property",
"config": {
"path": "view.custom.newRow_Country",
"bidirectional": true
}
}
}
}
},
{
"type": "ia.input.numeric-entry-field",
"version": 0,
"props": {
"placeholder": "population..."
},
"meta": {
"name": "NumericEntryField_Population"
},
"position": {
"grow": 1,
"basis": "25%"
},
"custom": {},
"propConfig": {
"props.value": {
"binding": {
"type": "property",
"config": {
"path": "view.custom.newRow_Population",
"bidirectional": true
}
}
}
}
},
{
"type": "ia.input.button",
"version": 0,
"props": {
"text": "Add Row",
"primary": false,
"image": {
"icon": {
"path": "material/playlist_play",
"color": "limegreen"
}
}
},
"meta": {
"name": "Button_AddRow"
},
"position": {
"shrink": 0,
"basis": "125px"
},
"custom": {},
"propConfig": {
"props.enabled": {
"binding": {
"type": "expr",
"config": {
"expression": "{view.custom.newRow_City} != ''\r\n\t&& {view.custom.newRow_Country} != ''\r\n\t&& {view.custom.newRow_Population} != null"
}
}
}
},
"events": {
"component": {
"onActionPerformed": {
"type": "script",
"scope": "G",
"config": {
"script": "\tdataIn = self.view.custom.editData\n\tnewRow = [\n\t\tself.view.custom.newRow_City,\n\t\tself.view.custom.newRow_Country,\n\t\tself.view.custom.newRow_Population,\n\t]\n\t\n\tdataOut = system.dataset.addRow(dataIn, newRow)\n\tself.view.custom.editData = dataOut\n\t\n\tself.view.custom.newRow_City = ''\n\tself.view.custom.newRow_Country = ''\n\tself.view.custom.newRow_Population = None\n\t"
}
}
}
}
},
{
"type": "ia.input.button",
"version": 0,
"props": {
"text": "Delete Row",
"primary": false,
"image": {
"icon": {
"path": "material/playlist_play",
"color": "lightcoral"
}
}
},
"meta": {
"name": "Button"
},
"position": {
"shrink": 0,
"basis": "120px"
},
"custom": {},
"propConfig": {
"props.enabled": {
"binding": {
"type": "expr",
"config": {
"expression": "len({view.custom.selectedData}) > 0"
}
}
}
},
"events": {
"component": {
"onActionPerformed": {
"type": "script",
"scope": "G",
"config": {
"script": "\tdataIn = self.view.custom.editData\n\tselectedRow = self.view.custom.selectedRowIndex\n\t\n\tdataOut = system.dataset.deleteRow(dataIn, selectedRow)\n\tself.view.custom.editData = dataOut"
}
}
}
}
}
]
},
{
"type": "ia.display.table",
"version": 0,
"props": {
"selection": {
"selectedColumn": "country"
},
"columns": [
{
"field": "city",
"visible": true,
"editable": false,
"render": "auto",
"justify": "auto",
"align": "center",
"resizable": true,
"sortable": true,
"sort": "none",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY",
"width": "",
"strictWidth": false,
"style": {
"classes": ""
},
"header": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
}
},
{
"field": "country",
"visible": true,
"editable": false,
"render": "auto",
"justify": "auto",
"align": "center",
"resizable": true,
"sortable": true,
"sort": "none",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY",
"width": "",
"strictWidth": false,
"style": {
"classes": ""
},
"header": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
}
},
{
"field": "population",
"visible": true,
"editable": true,
"render": "auto",
"justify": "auto",
"align": "center",
"resizable": true,
"sortable": true,
"sort": "none",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY",
"width": "",
"strictWidth": false,
"style": {
"classes": ""
},
"header": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
}
}
],
"rows": {
"striped": {
"color": {
"even": "#FFFFFF",
"odd": "#D5D5D5"
}
}
},
"pager": {
"bottom": false
}
},
"meta": {
"name": "DiffTable"
},
"position": {
"basis": "400px"
},
"custom": {},
"propConfig": {
"props.data": {
"binding": {
"type": "property",
"config": {
"path": "view.custom.diffData"
}
}
},
"props.selection.data": {
"binding": {
"type": "property",
"config": {
"path": "view.custom.selectedData",
"bidirectional": true
}
}
},
"props.selection.selectedRow": {
"binding": {
"type": "property",
"config": {
"path": "view.custom.selectedRowIndex",
"bidirectional": true
}
}
}
},
"events": {
"component": {
"onEditCellCommit": {
"type": "script",
"scope": "G",
"config": {
"script": "\tdataIn = self.view.custom.editData\n\tr = event.row\n\tc = event.column\n\t\n\tdataOut = system.dataset.setValue(dataIn, r, c, event.value)\n\tself.view.custom.editData = dataOut"
}
}
}
}
},
{
"type": "ia.display.label",
"version": 0,
"props": {
"text": "Edited Data",
"textStyle": {
"fontWeight": "bold"
}
},
"meta": {
"name": "Label_0"
},
"position": {
"basis": "50px"
},
"custom": {}
},
{
"type": "ia.display.table",
"version": 0,
"props": {
"selection": {
"selectedRow": ""
},
"rows": {
"striped": {
"color": {
"even": "#FFFFFF",
"odd": "#D5D5D5"
}
}
},
"pager": {
"bottom": false
}
},
"meta": {
"name": "EditTable"
},
"position": {
"basis": "400px"
},
"custom": {},
"propConfig": {
"props.data": {
"binding": {
"config": {
"path": "view.custom.editData"
},
"type": "property"
}
}
}
},
{
"type": "ia.display.label",
"version": 0,
"props": {
"text": "RawData",
"textStyle": {
"fontWeight": "bold"
}
},
"meta": {
"name": "Label"
},
"position": {
"basis": "50px"
},
"custom": {}
},
{
"type": "ia.display.table",
"version": 0,
"props": {
"rows": {
"striped": {
"color": {
"even": "#FFFFFF",
"odd": "#D5D5D5"
}
}
},
"pager": {
"bottom": false
}
},
"meta": {
"name": "RawTable"
},
"position": {
"basis": "400px"
},
"custom": {},
"propConfig": {
"props.data": {
"binding": {
"config": {
"path": "view.custom.rawData"
},
"type": "property"
}
}
}
}
]
}
]
