Hello everyone. My application creates invoices. After careful consideration, I realized that some calculated fields should be stored in my Invoice table (e.g. total amount, taxes. etc.) For example, I have a Invoice table and a InvoiceDetail table . Both have Forms for input data, the InvoiceDetail being a sub-form of Invoice. I’v tried several methods with not much success. What would you recommend as a good strategy if I want this: when the user closes a Invoice detail form, the parent Invoice form is displayed with updated data?
In the following example (fdf attached), the field Total is read only. When a user creates or update a new InvoiceDetail record, I want the field Invoice.Total to be updated in the database with the sum of all InvoiceDetal.Amount and also displayed in the Invoice Form. Your help is greatly appreciated, thanks!
Hi Jean,
Thank you for your question,
For your scenario, you can use the event called “On Sub Form Complete” on the form Invoices.
This function triggers as soon as a list/sub-form changes, allowing you to return data from a list/sub-form while staying in the main form, which is where you want to send the data to.
The data from the list/sub-form is retrieved from the context object, and it will give access to two information ‘record’ and ‘records’.
The record contains the current value you are changing/editing.
The records contain all the values, including the last value changed/added.
Using your FDF provided (Thank you for sharing it), this is how they will look.
{“record”:{“key”:{“keys”:[“6977c098-b79f-4274-959d-7feb3c45d7c0”]},“values”:{“InvoiceDetail.InvoideDetailKey”:“6977c098-b79f-4274-959d-7feb3c45d7c0”,“InvoiceKey”:“e4ce2c45-a85b-4e7c-b8a1-53e37637a8f0”,“Description”:“Product 2”,“Amount”:6}},
“records”:[{“key”:{“keys”:[“de03bccc-54d3-4b42-9fd1-c126baed1e25”]},“values”:{“InvoiceDetail.InvoideDetailKey”:“de03bccc-54d3-4b42-9fd1-c126baed1e25”,“InvoiceKey”:“e4ce2c45-a85b-4e7c-b8a1-53e37637a8f0”,“Description”:“Product 1”,“Amount”:“10”}},
{“key”:{“keys”:[“6977c098-b79f-4274-959d-7feb3c45d7c0”]},“values”:{“InvoiceDetail.InvoideDetailKey”:“6977c098-b79f-4274-959d-7feb3c45d7c0”,“InvoiceKey”:“e4ce2c45-a85b-4e7c-b8a1-53e37637a8f0”,“Description”:“Product 2”,“Amount”:6}}]}
The function you need to create and attach to the event “On Sub Form Complete” on the form Invoices should look like this:
function RetrieveValueSubForm(five, context, result) {
five.field.Total = five.sumField(context.records, 'Amount')
return five.success(result);
}
Observation, you can also use the function ‘five.sumField‘ to calculate the total of records and assign it directly to the total field.
Please let me know if that solution works for you.
Regards,
Elton S
Wow so simple! It took me less than a minute to implement the solution you proposed and it works perfectly on my sample application! I spent lot of time looking for complex solutions handling SQL commands in back-end, etc. I would benefit from knowing more about the internal functions of Five! Thank you so much, I love this solution.
Hi Elton,
I have an issue with subform row deletions when updating parent totals in On Sub Form Complete.
When a user deletes a row in the subform using the trash icon:
context.records in On Sub Form Complete still contains the deleted record.
Inspection/JSON dump of context.records shows no deletion flags or status metadata (like _deleted or _action) to filter it out in code.
Consequently, any JS calculation on context.records includes the deleted amount in the parent total.
Is there a way to identify or exclude deleted subform records from context.records during On Sub Form Complete before the parent form saves?
Thanks!
Jean
Hi Jean,
The current values should be automatically updated on the fly; there is no need to identify whether a record is no longer part of the list.
I have tested this on my side, and when I delete a record from the list, the total value (field TotalSale ) on the parent form is automatically recalculated.
This is the function I am using, which is attched to the parent form.
I have also attached the application so you can test yourself.
Please let me know if you still facing this issue.
function CalculateTotalSale(five, context, result) {
const total = context?.records.reduce((sum, record) => {
const price = Number(record.values.Price);
const quantity = record.values.Quantity;
return sum + (price \* quantity);
}, 0);
five.field.TotalSale = total + 0;
return five.success(result);
}
SubFormComplete-20260802-222855645210714.fdf (3.4 MB)
Elton S
Hi Elton,
Thanks for the code and the .fdf sample application, but I get an error message while loading the application: “Importing from newer version of fdf is not supported”. I’m on Five Software (2.9.555)(86)
Regarding the behavior, If I delete a record and save immediately, it works. However, if I delete a record (witout saving) and immediately add a new record, context.records still retains the deleted row in the buffer on my version.
Regards,
Jean
Hi Jean,
Thank you for providing more details on this scenario. I was able to simulate it on my end.
Please consider this alternative approach; you will need to find out which records have been marked to be deleted, and then try to add or update another record, so you can subtract it from the current calculation.
The new application attached should work in your version:
Below is my current function.
function CalculateTotalSale(five, context, result) {
// Current total
let total = context?.records.reduce((sum, record) => {
const price = Number(record.values.Price || 0);
const quantity = Number(record.values.Quantity || 0);
return sum + (price \* quantity);
}, 0);
// Find the dynamic Products:\* key
const productKey = Object.keys(five.listDataManager.lists)
.find(key => key.startsWith('Products:'));
if (productKey) {
const deletedRecords = five.listDataManager.lists\[productKey\].deletedRecords;
Object.keys(deletedRecords).forEach(key => {
const record = deletedRecords\[key\];
const price = Number(record.data?.Price || 0);
const quantity = Number(record.data?.Quantity || 0);
total -= price \* quantity;
});
}
five.field.TotalSale = total;
return five.success(result);
}
Below is an image so you can understand how I manage to get the deleted information from.
SubFormComplete-20260803-040126913816952.fdf (3.4 MB)
Regards,
Elton S
Hello Elton,
Thank you for your help! I implemented your solution and it works fine in my application 
.
I have another issue with the subform calculations. The problem arises when I have another level of form on top of my hierarchy.
I modified the fdf file you provided by adding a new table ‘Customer’ and the Sale table gets a foreign key towards this table. I created the ‘Customers’ form and added a new page to this form: Sales. The Sales form is not modified and still has the Products page.
This is similar to the use case I have in my application. In the following example, I have two product lines and the Total Sale is $5.
Now in the ‘Sales’ form, I add a new product at $4. The Total sale displays $9, which is fine.
As you can see, there are two levels of check symbols: At the Customers level and at the Sales level. I tell my users to always click on the lowest check symbol first, then going up, click until reaching the last check sign if any.
But if by mistake the user clicks the upper check sign first, the TotalSale of the Sales table does not correspond anymore to the total of each of the products in the subform.
In the following example, I clicked on the upper check sign to save the data. If I close the form and re-open it, I still have my three product lines ($9), but the Total Sale is back to $5.
I am thinking of launching a script that recalculates the Total Sale for the Sale table each time the table is displayed on a report, a data view, or whatsoever it is in order to prevent any inconsistencies of my database.
On your side, do you have any suggestions on how to handle this problem? Or should this issue be addressed in a future release of Five?
The fdf modified file is attached.
Regards,
Jean
SubFormComplete-CustomerAdded-20260803-192809765317021.fdf (3.4 MB)
Hi Jean,
I am glad to hear that the solution worked well!
In this new scenario, I would suggest preventing the user from saving the top form Customers without saving the child list Sales.
The images below show the difference in saving the main form Customers with and without saving the child list Sales form.
When saving the Customers without saving the Sales form, you can see that the property five.form.Sales is still visible, meaning the Sales form is still open on the screen and has not been saved/closed yet.
On the other hand, when saving the Customers after saving the Sales form, you can see that the property five.form.Sales is no longer visible, meaning that the Sales form has been saved/closed.
Given these two pieces of information, you can create a function and attach it to the Customer form, event 'On Complete ', to prevent the user from saving the Customers if the Sales form has not been saved yet. This will also work if the user tries to update/delete the record, which is a good approach.
Function used:
function CheckIfSalesFormHasBeenSaved(five, context, result) {
if (five.form?.Sales){
return five.createError('Please, save the Sale record before saving the Customer record.');
}
return five.success(result);
}
If you want to change it via code, you need to create a function to handle it. One way is to use the table events; I would suggest attaching to all Do Before events for the table ProductSale, but as mentioned, because it involves more complexity and tests, I would use the first approach.
I will notify the development team about this.
Please let me know how that goes.
Thank you
Elton S
Hi Elton,
I applied the “check” function and, as expected, it prevents saving the parent form before the child is saved. This is great!
Actually, my application involves a four-level form structure and the user cannot save any parent form before a child form is saved. I am very happy with the result.
Thank you again Elton for your quick help!
Regards,
Jean
Hi Jean,
Your latest account upgrade should have the fixes for this issue.
Can you please try again without the workaround and let me know how that goes?
Thank you.
Elton S