As a temporary work around I created an indexed field and am using a nightly script to move the Invoice Total data to the indexed field. I do not want this to be a permanent solution.
I think you must, Nancy. Having the invoice total as an unstored calc is going to have so many downstream performance issues (reporting,...). Creating invoices is a typical use-case for a transactional approach where you set the invoice fields based on the invoice items and then lock it down. All scripted, no calc fields.
Any change to an invoice should be a controlled and audited workflow anyway, not something left up to calculated fields.
Re: "Any change to an invoice should be a controlled and audited workflow anyway, not something left up to calculated fields."
Quite so. While an invoice is being prepared there may be a case for the total to be fluid, but surely business rules should require that once the invoice is completed and issued it gets locked down.
Re: "using a nightly script to move the Invoice Total data to the indexed field"
Why not simply use an auto enter calc? You should be able to devise a calc which allows it to update if a line item is changed/added/deleted and then lock once an invoice is finalised.
I will also lend my voice to Wim's suggestion. Go scripted. There are a lot of pitfalls to even using the Auto-enter calcs if you aren't 100% certain what and when FileMaker updates them. Scripting give you full control over the actions.
This was a lesson I learned the hard way several years ago. It's a touch more work up front, but easier to maintain in the long run. And easier to debug when something goes astray. Plus the difference in performance both with data entry and reporting will be very significant.