Do not expect changes made in related portal records to be reflected in the parent record calcs until the record(s) get committed. Portal records are uncommited after edits until the parent record is committed, so the calcs won't update until then.
You can script a commit using a script trigger, or just by exiting all fields in both the parent record and the portal rows.
This has always been one of the biggest headaches of getting proper screen refreshes on edited portal rows.
Thanks for your reply, it's much appreciated! : )
From your bio it sounds as though you should know what you are talking about and thus because Filemaker works that way I am discouraged. : (
From the web link I listed it sounds like that should work and fix the problem but it did not for me. : (
I would think this would be such a common task in any database and therefore there must be some method or apporach to such madness. I'm not sure what else to do except create temp fields in the invoice file to hold amounts (parts of the payment to be applied) and then in a script create new records & move amounts. But that could easly turn into a multiuser nightmare. Or maybe (just thought of it) use a script trigger on field changes to add any amounts togehter in a global variable rather than rely on the Sum function in a field calculation.
I'm just trying to work with how Filemaker works rather than work against it.
Again thanks for your help and the saga continues .............
One of the things some developers do is include a Save or Update button in the portal row which runs a script to set a variable for the portal object name (if there are multiple portals on the layout) and the Row Number. Then the script goes to a non-portal object, commits the record, refreshes the window/screen, then returns to the portal and row saved in the variables.
The user never sees anything but a moment's pause and they're right where they were.
And that's really only necessary if you need calcs in the parent record to update based on live changes without appearing to leave the portal.
Thanks for your response! : ) Thought that does sound cumbersome and awkward for a user as well as losing the transaction ability of Filemaker.
I love how transactions work in Filemaker even though it can be a little bit more work implementing at times.
It's still a bit of a challenge gettting the billing part of our company application done but I will somehow get it figured out.
In many cases I've found on the Internet there is a payment and portal of which invoices are selected and added to. However in my case each invoice is a service (and there are many services) so it would be a lot of work for the user to select a new service (invoice) to add to the portal. Instead I would like to list all invoices/services and the user enter amounts that should be applied to invoices/services. I can base the portal off the invoices/services and add a PaymentDetail::Amt to each portal record, which would create a new PaymentDetail record for any invoices Amt is entered for. But what if a user didn't mean to enter an amount and blanks the amount out. To late the PaymentDetail record has already been created. The only why I knew to resolve this is to make sure a 0 was entered for blank amounts in PaymentDetail. Then OnCommit have a script that goes to another layout to delete any records with 0.
Most things in life are best keep simple/stupid (as the old saying goes), Filemaker seems to work better with that idea. Still trying to work with how Filemaker works. There is so much to love about Filemaker and I'm trying to think simple to keep things simple. Just having a hard time finding that method of with billing.
Again thanks and have a blessed Christmas season!
One method you might like to consider keeps the user from ever touching the live tables in your database—actual payments and invoices, etc. This method involves having a separate table for data entry. This table mirrors the live table but you only ever create temporary records. So, if the user wants to enter a payment, say, a new temporary record is created, all the details are entered, you can build it all to work with FM's standard features, etc. but from the user's point of view they would appear to be creating all the details as normal—BUT the big "Commit" button you put on the layout will run a script that gathers up all the data entered in this temporary table, checks everything for validity, sends the user back to fix errors, then if everything is OK creates the relevant records in the live tables, finally deleting the temporary records.
I have successfully applied this technique in a Cash Book db where data integrity is high priority.
Thanks for your help! I had thought of that. There are a number of directions to work towards to get this to work. It's nice to hear how others get projects accomplished to help stimulate ideas and to know what has worked for others. Thanks again.
I believe what you are trying to do is take a payment and split it among invoices due but not apply more than the amount received.
You also need to prevent error.
$_payment_balance = check amount
go to table layout and find the customers unpaid invoices, sort by oldest date.
go to first record
set variable $_invoice to invoice number
set variable $_amount to invoice balance
got to table layout for payment records
set invoice number to $_invoice
if( $_amount < or = $_pament_balance)
set field amount paid to $_amount
set variable $_payment_balance to $_payment_balance - $_amount
set field amount paid to $_payment_balance
set variable $_payment_balance to 0
go to record next exit if last
exit loop if $_payment_balance < = 0
I believe you can use commit records to save all of these records if OK or Revert to delete them.
This method pays all invocies upto the amount due and will apply a partial payment if there is an unpaid balance or if funds are insufficient for a full payment.
Please verify the script steps before implementing.