I have an interesting problem that I have before me. There are certain situations in the report where an account (in this case C 0) may have more then one line to show multiple exceptions. In the example below, there are two lines for Account C 0; one is to indicate that the member has surpassed his/her Line of Credit (LOC) and the other to also indicate that he/she has a HOLD on the account. This information is essentially the same (thank god, as it is the same account), but what poses a problem is that I need to capture the LOC amount to make a calculation for the Hold.
This is how I have the templates setup:[LIST=1]
The detail line is defined to capture all the member's accounts so I trap it using Alpha Trap for the Account character and a Numeric Trap for the numeric portion of the Account. I also fill the blank cells with the information of the previous record for Member, FSR# and NAME.
/LISTFrom the detail, I have a formula to extract the appropriate information from the Reason field, depending on what appears at the beginning (Ex: > LOC, < HOLDS, etc). I should also note that when a member has an LOC and has overdrawn his limit, there is an "EX" appended after the Balance. I have no problems extracting the information that I need (like LOC amount, HOLD amount, etc), the problem is the calculation I need to perform when I find a HOLD.
:confused:This is the tricky part: I need to calculate the exception amount, so when there is a HOLD on the Account and the Account Balance is overdrawn with an LOC (I know this because it has an EX after the balance), I need to know the LOC amount to make my calculation for the HOLD. Since the LOC amount is not on the same line, I don't know how to proceed. I am told that any account that has an LOC and[/B] HOLD, the LOC information will come first.
Ex: ExceptionAmount (for HOLD record) = 3,333.33 (Hold Amount) + -1,111.11 (Balance *-1 since there is an EX appended to it) + 222.22 (LOC amount from previous occurrence of this Member Account - 123456, C 0).
I thought of extracting the LOC amount portion exclusively from the Reason field and fill empty cell with the previous value, but the HOLD amount sometimes interferes with the columns used for LOC (as in the example) or other messages in the Reason field.
Is there a way of saving then retrieving the LOC amount to use when I do the calculation when I find a HOLD amount? Maybe with a newer version? Or Pro version?
RUN : 05FEB09 23:17 OVERDRAFTS AND EXCEPTIONS FOR: 05FEB09 23:59
REPORT # : 101 ALL BRANCHES CONSOLIDATED
MEMBER FSR# NAME PHONE ACCT BEN BALANCE REASON NET CHANGE SRC OD NSF
123456 1234 STUCK, A. LITTLE 555-1212H C 0 PK1 1,111.11EX > LOC OF 222.22 15 DAYS 43 23
N 0 3.33
P 0 444.44
Z 0 5.55
C 0 PK1 1,111.11EX < HOLDS OF 3,333.33 43 23 /code