Lookup Tables

Lookup tables are used with recurring transactions to automatically apply a fee or an amount to a loan based on certain criteria as defined in the lookup table.

Lookup tables can be configured by clicking File > Setup then Loan Setup > Lookup Tables.


Lookup Table

To add a new lookup table, click Add Add at the top of the upper pane in the window. Enter a name for the table and a description and click OK.

Add Add Opens a dialog for entering a name and description for adding a new lookup table.
Modify Modify Opens a dialog for editing the description of the selected lookup table. The name of a lookup table cannot be modified once it is created.
Delete Delete Deletes the selected lookup table. Only tables that are not currently being used in a recurring transaction can be deleted.

Table Entry

With a lookup table selected, click Add Add below in the lower pane to add a new entry for the table.

Add Add Adds a new table entry to the currently selected lookup table.
Delete Delete Deletes the selected table entry.

To edit a table entry, click on any of the cells in the table and enter a value into the cell that will be used to match against in the loan.

Table Entry Fields:

Original Loan Balance The amount that will be matched against the loan’s original balance.
Current Principal Balance The amount that will be matched against the current principal balance of the loan.
State Will be matched against the State of the contact’s address to which the loan belongs.
Days Active The number that will be matched against the number of days the loan has been active.
Days Past Due The number that will be matched against the number of days the loan is days past due.
Amount The amount that is to be applied to the loan, under the transaction type as defined in the recurring transaction setup, when a match occurs.
Percentage This field is reserved for future use.

How Values are Matched

During a lookup, a match will occur if the numeric value (in the loan) being looked up is equal to or greater than the value in a table entry—except for the State field which will only look for an exact match.

Example

If a recurring transaction is set up with a late fee transaction code using a lookup table containing entries for 10, 30, and 60 days past due with the amount field set to 5, 15, and 25 respectively, a loan that is 50 days past due will match the 30 days past due table entry and apply a $15 late fee to the loan when the recurring transaction is processed.

A table entry must satisfy all entered fields for the entry to be a match during a lookup.

Example

If a table entry has 10,000 entered in its original loan balance and 10 entered in the days past due, a loan’s original balance must be $10,000 or greater and currently be 10 or more days past due on its payment for the table entry to be a match.

If more than one table entries satisfy a criteria, the table entry with the least amount of difference to the value it is matched against will be used.

Example

If there are table entries with the original loan balance set to 5,000 and 10,000, a loan with an original loan balance of $15,000 will satisfy both table entries but the table entry with 10,000 will be used as it is closer to 15,000.

When different table entries with different fields satisfy a criteria, the table entry with the highest precedence will be used. The order of precedence, high to low, is original loan balance > current principal balance > state > days active > days past due.

Example

If there is a table entry with the original loan balance set to 10,000 and another table entry with days past due set to 10, a loan with an original balance of $10,000 and is currently past due by 10 days will satisfy both table entries but the table entry with the original loan balance set to 10,000 will be used because the original loan balance has a higher precedence than days past due.

Using Lookup Tables

Lookup tables can be configured to be used when setting up a loan template with a recurring transaction or for individual loans by querying a loan and selecting Recurring Transactions . Select Use Lookup Table from the Amount Type drop down list in the recurring transaction setup dialog and click next to the amount field to select a lookup table. When the specified recurring transaction event occurs, the defined lookup table values will be compared against the loan values and apply the specified amount when there is a match.

Tip

If you do not see a lookup table listed, go back to the lookup tables setup and make sure Active status is selected for that table.