Formula Modification Question - Duplicates

RickOShay · August 22, 2023, 9:51pm

Hi,

I am going into this question knowing this is a long shot if I can get an answer…I have an incredibly complex formula that is doing exactly what I need it to do (a variable) and took about 20 hours to write this one formula. My problem is that there are some loans that are showing up two times, but I understand why, it’s because a couple of the objects that my formula references (I think Billing Interest Amount and Billing Principal Amount) can have multiple instances (for example a loan can have two billing schedules, not just one, some loans have an interest only bill and a principal billing schedule simultaneously). The data the formula provides is very accurate for what I need, but in those rare situations that there are multiple billing schedules, I need to either just see the bigger of the two amounts or possibly add both billing amounts together. The reason I need this is because Webi will duplicate the loans (which makes sense because one of the objects is not the same), but this creates a major issue, as when the file is uploaded into another system, it now get’s viewed as this borrower has two loans for the same amount, which totally breaks things.

I pasted my code below and hoping for some kind of miracle, either advice on how to go about this or possibly someone to tweak my code, as I don’t think my brain can handle any more complexity on this one.

Thanks,

=If [Note Bill Type Code] = "A"  Then ([Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Query 1 with LoanDly].[Note Pmt Sched Escrow Amount])ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "0" And [Note Pmt Sched Frequency Code] = "1" Then [Note Daily Accrual Amount]*30 ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "0" And [Note Pmt Sched Frequency Code] = "2" Then (([Note Daily Accrual Amount]*30)/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "0" And [Note Pmt Sched Frequency Code] = "3" Then (([Note Daily Accrual Amount]*30)/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "0" And [Note Pmt Sched Frequency Code] = "4" Then (([Note Daily Accrual Amount]*30)/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "0" And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "0" And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "0" And [Note Pmt Sched Frequency Code] = "9" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 1 And [Note Pmt Sched Frequency Code] = "1" Then [Note Daily Accrual Amount]*30 ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 1 And [Note Pmt Sched Frequency Code] = "2" Then (([Note Daily Accrual Amount]*30)/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 1 And [Note Pmt Sched Frequency Code] = "3" Then (([Note Daily Accrual Amount]*30)/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 1 And [Note Pmt Sched Frequency Code] = "4" Then (([Note Daily Accrual Amount]*30)/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 1 And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 1 And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 1 And [Note Pmt Sched Frequency Code] = "9" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 4 And [Note Pmt Sched Frequency Code] = "1" Then [Note Daily Accrual Amount]*30 ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 4 And [Note Pmt Sched Frequency Code] = "2" Then (([Note Daily Accrual Amount]*30)/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 4 And [Note Pmt Sched Frequency Code] = "3" Then (([Note Daily Accrual Amount]*30)/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 4 And [Note Pmt Sched Frequency Code] = "4" Then (([Note Daily Accrual Amount]*30)/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 4 And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 4 And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 4 And [Note Pmt Sched Frequency Code] = "9" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "1" Then [Billing Interest Amount] + [Billing Principal Amount] ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "2" Then (([Billing Interest Amount] + [Billing Principal Amount])/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "3" Then (([Billing Interest Amount] + [Billing Principal Amount])/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "4" Then (([Billing Interest Amount] + [Billing Principal Amount])/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "9" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "1" Then [Billing Interest Amount] + [Billing Principal Amount] ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "2" Then (([Billing Interest Amount] + [Billing Principal Amount])/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "3" Then (([Billing Interest Amount] + [Billing Principal Amount])/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "4" Then (([Billing Interest Amount] + [Billing Principal Amount])/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 2 And [Note Pmt Sched Frequency Code] = "9" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "1" Then [Billing Interest Amount] + [Billing Principal Amount] ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "2" Then (([Billing Interest Amount] + [Billing Principal Amount])/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "3" Then (([Billing Interest Amount] + [Billing Principal Amount])/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "4" Then (([Billing Interest Amount] + [Billing Principal Amount])/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "9" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "1" Then [Billing Interest Amount] + [Billing Principal Amount] ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "2" Then (([Billing Interest Amount] + [Billing Principal Amount])/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "3" Then (([Billing Interest Amount] + [Billing Principal Amount])/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "4" Then (([Billing Interest Amount] + [Billing Principal Amount])/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] = "P" And ToNumber([Note Pmt Sched Payment Plan Nbr]) = 3 And [Note Pmt Sched Frequency Code] = "9" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] InList ("A";"B";"C";"D";"E";"F";"G";"J") And [Note Pmt Sched Frequency Code] = "1" Then [Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Query 1 with LoanDly].[Note Pmt Sched Escrow Amount] ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] InList ("A";"B";"C";"D";"E";"F";"G";"J") And [Note Pmt Sched Frequency Code] = "2" Then (([Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Query 1 with LoanDly].[Note Pmt Sched Escrow Amount])/4) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] InList ("A";"B";"C";"D";"E";"F";"G";"J") And [Note Pmt Sched Frequency Code] = "3" Then (([Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Query 1 with LoanDly].[Note Pmt Sched Escrow Amount])/6) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] InList ("A";"B";"C";"D";"E";"F";"G";"J") And [Note Pmt Sched Frequency Code] = "4" Then (([Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Query 1 with LoanDly].[Note Pmt Sched Escrow Amount])/12) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] InList ("A";"B";"C";"D";"E";"F";"G";"J") And [Note Pmt Sched Frequency Code] = "5" Then "0" ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] InList ("A";"B";"C";"D";"E";"F";"G";"J") And [Note Pmt Sched Frequency Code] = "6" Then (([Note Daily Accrual Amount]*30)*2) ElseIf
[Note Bill Type Code] InList ("B";"C") And [Note Pmt Sched Type Code] InList ("A";"B";"C";"D";"E";"F";"G";"J") And [Note Pmt Sched Frequency Code] = "9" Then "0" Else "ERROR!"

MarkP · August 23, 2023, 8:24am

It will depend what other objects you have in your block, but a simple sum() or Max() around this variable should do the trick. If not, then use:
=Sum([Your Variable]) ForEach([Missing Object 1];[Missing Object 1])
Where Missing Object is any object that the variable needs to split the logic out on which isn’t in your block. ForEach will include them in the calculation context without them being added into the block.