This may be too complex for a formula. And, to be honest, it may not really be necessary. In most cases, a member will only have, at most 2 or 3 late items. So it may be easier to just calculate manually.
First, background: If a borrowed item is overdue, a late fee must be paid. It is also possible that an item might be returned late, but the member can’t pay the late fee at that time. Details of borrowed items (date borrowed, returned T/F, date returned, late fee paid, etc) are stored in the LibraryRecord collection.
Also, different items have different late fees ($1 per day for movies, $5 per day for tools). That info is stored in the LibraryItems collection.
So, my “returns” screen shows a) all unreturned borrowed items for current user, and b) all items that have been returned late but the late fee is still unpaid.
When an item is returned, the “returned” toggle is checked. If the item is not late, that action also marks the item “cleared”. In that case the item disappears from the screen.
But if the returned item is late, a late fee is due. The item can be marked “returned” but not “cleared”.
Uncleared items are still visible on the screen with a checkmark to show “returned” but also with a late fee displayed. In this case, the item will only be cleared after the late fee toggle is checked.
When a late item has not yet been returned, the formula calculates the late fee from the due date up to current date:
That fee will increase every day until the item is returned.
But, once the item is returned, the late fee formula shows the amount from due date to returned date:
This late fee will no longer increase. The item has been returned, so the amount is locked.
Here is one idea that might work, but I don’t know how to set up the formula:
The list on the “returns” screen is a list of uncleared items (i.e. LibraryRecords with “cleared” toggle false) for current user. Would it be possible to write a formula that calculates total late fee of all uncleared LibraryRecords for current user?