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.