Hi, I need the SUM of all records, both loaded and changed. SUM automatically created on the table counts only values originally loaded from a data source, not changed by the user. Please see sample below.
The picture depicts the expense allocation form. The purpose of this table is to split or merge records from the database. User can split one expense into more categories or merge records into less by providing 0/null into Local Amount. The format of the editing grid is necessary for the user experience to see all records listed in one column rather than editing every record separately.
Question 1:
How can I calculate the sum from records coming from DB and records changed by the user? You can see that the SUM provided by the table is wrong. It is 29.55, but the correct is 129.55.
I use this sum to ensure that the sum of the local amount on all records is always the same, i.e., the delta is 0.00. But this check doesn't work.
Question 2:
I need to send a full list of records to the database. However, the changeset contains only changed records. You can see that Changeset provides the wrong SUM as well. Anyway, the backend procedure needs the list of all records: changed and unchanged in one JSON value.
How can I achieve that?
since you're using changeset this would be a legacy table and not the new one right?
I think the only problem here might be how you're calculating the sum with changeSet. .changeSet only contains changes made since the last time changes were saved or since loading if none have been made yet, so you can't just sum up everything using the value of .changeSet if that's what you're doing.
I'd suggest using a transformer on the query that gets the original dataset and in it you can Sum up what you need to then add a new property to the object to hold this value and return the new object. the transformer lets you use query1.data and query1.rawData, the later of which you can use to retrieve the original query response and in combination with table1.data you can piece together the full list of records by merging the 2 together (loop through table1.data and for each row.id check if query1.rawData contains it, if it does you want to update the sum by subtracting the .rawData value and adding the table1.data value.... or you can probably chain a bunch of JS Array methods, but I'm a c guy so this is easier for me). so now you have query1 where, thanks to the transformer, you have query1.rawData which always contains the original query response and query1.data that always has all the changes done since loading and you have the process for a query2 to get the full list of records and to update the sum property added to query1.data. i think all you need to do from here is add a Save Change Event Handler to table1 which calls query2 from above to merge and update sum. when you want to push the full list to the db, just use query2.data since it will always have the merged list after any changes are saved.
if I've misunderstood something let me know but if so hopefully at least adding a transformer to get .data and .rawData points you to the right direction =)
Hi @bobthebear , thank you very much for your response. I can see you put lots of effort into it. I'm a beginner in javascript, so I'm sorry your description of transformers handling is too complex to go through. So, I created the sample app. Please see the attached.
Firstly, I use the latest table template from Retool.
Secondly, this is not a typical showing of the result data grid from the query, but it allows the user to split the receipt amount with descriptions. This behaviour is critical to have all rows edited in one step as the sum has to always be the same, and the user must see the new split completely before the update.
The user has to be able to add more records in the receipt split or remove the previous split. As far as I know, the current table doesn't allow adding new records in its grid compared to the old decommissioned table. Therefore, I generate records with negative IDs. then I know in a database processing which record is new and which is existing. If the user uses the value 0 for the amount, the record will be deleted.
The sum of new records is important from two perspectives: 1) When records are changed, the sum of old records is misleading. 2) The user can save data only when the sum of changed records is the same as that of loaded records.
The sample application is attached. You just need some SQL server to run one query.
for you, your data would look like this afterwards:
{
data: [array of objects from above],
sum: 30
}
so if this query was named getData you can use getData.data.data to get the array of objects for your table (this will be the table data source) and you can use getData.data.sum to get the sum of all these records.
next we want to store this somewhere so we can modify the sum as things are edited so we add a Success event handler to the query
now that we store everything lets change the table data source to the variable so the table doesn't change if the query fails.
ok so at this point we have a query named getData. when the query runs, we sum up everything then add this value to the data our query returns. after this the query finishes and if it's successful we store the results in a variable. we also set the table to use the variable as the data source so that every time the query runs successfully the table is updated with the new data.
as far as changeset goes, it will contain only the rows that have been modified.
here i've changed a number under column9 on the 4th row in the table to the value 1.
the following will loop through each item that was changed and adjust the sum in our variable accordingly.
let sum = query1.data.sum;
table1.changesetArray.forEach((item) => {
let newValue = item.Amount;
let oldValue = table1.data[item.id];
let difference = oldValue - newValue;
sum += difference;
})
variable13.setIn(['sum'], sum);
lets create a Javascript resource query so we can run this when we want.
we'll run this code whenever changes are saved. to do this we use the Save actions Add-on and set it to run the updateSum query we just made.
now when you make changes and click Save this will run and update the sum property.
so what if you want the sum to update after every change instead of just after saving?
we use the Change Cell event and set it to run the updateSum query, just like when save is clicked
to use the sum in like a text area or something you would set the value to {{ variable13.value.sum }} then every time the sum is updated the text updates too. if you want to get the original un-edited table data you use getData.data or getData.rawData (this is the query from the 1st picture where we added js to the Transform Result area to add a sum property)
let me know if this is too confusing and i'll make a little demo for you after i get off work and back home.
