I am trying to store approval data within an approval workflow.
The original request contains two tables of bookings (plus and minus bookings). Each record in each table has six values (account number, receiving dep, amount...). Each request can contain one or multiple bookings.
Now when the first of four approvers receive the request, he or she can adjust each record in each table. They can alter existing records, add new ones, or delete existing ones. I must store each change to each record in each step of the approval flow. So, I created three new collections (change, add, delete). In "change" I only save the booking id, the changed field, the old and the new value. In both "add" and "delete" I save the entire record.
Since I save the approval in a JSON object anyway, my idea was to attach the content of these three collections to the actual approval. And that works perfectly.
Collect(
col_Approval,
{
Step: rec_ThisApproval.step,
Approver: User().Email,
Timestamp: Now()
changes: If( !IsEmpty( col_Changes), col_Changes),
deletions: If( !IsEmpty( col_Delations), col_Deletions),
additions: If( !IsEmpty( col_Additions), col_Additions)
}
);
If I create a JSON object from this I get something like
[
{
"additions": [
{
"account": "19082",
"amount": 200.98,
"index": 4,
"type": outgoing
}
],
"approver": jack.liberty@mycompany.de,
"changes": [
{
"field": "amount",
"index": 2,
"new_value": 200,
"old_value": 220
},
{
"field": "account",
"index": 1,
"new_value": "12009",
"old_value": "12090"
}
],
"deletions": null,
"step": 1,
"timestamp": "19/04/2023 17:07"
}
]
When I parse the object I can parse the nested tables into my new collection:
ClearCollect(
col_AllApprovals,
ForAll(
Table(
ParseJSON(
rec_thisRecordGbl.Approvals)),
{
step: Text( ThisRecord.Value.step),
...
additions: Table( ThisRecord.Value.additions)
changes: Table( ThisRecord.Value.changes)
deletions: Table( ThisRecord.Value.deletions)
}
)
);
But now I'm stuck. I feel like I was more than half-way there, but I don't know how to parse the values out of "col_AllApprovals.additions". If I look at the collection it shows a table icon for this column. When I click on the item the table is empty (obviously). There is only a "?" in the column "Value" of this table.
I tried something along the lines of
ForAll(
Table(
ParseJSON(
col_AllApprovals.changes))
{
...
}
);
Which results in an error since ParseJSON considers the col_AllApprovals.changes not as text but as table. Converting the nested tables to text while parsing the approval object also won't work.
Do you have any ideas what I can do from here?
(Fallback would be to save the change-, addition-, and deletion-object in separate objects in the SharePoint list. But I think we can do better than that).
