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
To add a new lookup table, click Add at the top of the upper pane in the window. Enter a name for the table and a description and click OK.
|Add||Opens a dialog for entering a name and description for adding a new lookup table.|
|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||Deletes the selected lookup table. Only tables that are not currently being used in a recurring transaction can be deleted.|
With a lookup table selected, click Add below in the lower pane to add a new entry for the table.
|Add||Adds a new table entry to the currently selected lookup table.|
|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.
ExampleIf 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.
ExampleIf 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.
ExampleIf 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.
ExampleIf 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
TipIf 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.