Back to Technical Support Document Index

How To Query On "Loan To Value":

The trick here is to use a numeric field not currently in use and do a Global Replace of the 2 fields that determine the loan to value ratio. Then the query can be done on the replaced numeric field.

If loan to value is determined by first trust deed divided by the transfer value, then in FMS the expression would be: (FIRST_TD / TFR_VAL)

But, since FMS or pretty much any software program will "crash" if you divide a number by 0, (division by 0 is undefined in mathematics), the expression becomes a little more complex: (IIF(TFR_VAL > 0, ((FIRST_TD/TFR_VAL)*100),0))

So the whole process would go something like this:

1. Get In Farm: Start FMS and select the "View A Farm" option. Select the farm and order as usual.
2. Backup Original Farm: (Optional) If you don't want to permanently replace any field, you will need to make a copy of the farm first. To do that, press F5 for the Misc Menu, and select "Farm Copy". Enter the name for the new farm, and press Enter. If you want to do the copy on the new farm just created instead of the current farm, just select "Switch Current Farm" from the Misc Menu, and select the new (copied) farm.
3. Select Replaced Field: Press F5 for the Misc Menu, and select "Global Replace". Press any key to bypass the warning, and select the NUMERIC field you wish to replace with the Loan To Value percent. Valid fields are ASSD_VAL, BLDG_SQFT, LOT_SIZE, SECOND_TD, and LAND_VAL. Using other fields will require that the conversion expression be modified, see page 2
4. Enter Expression: In the "Replace Expression" window type the following EXACTLY: (IIF(TFR_VAL > 0, ((FIRST_TD/TFR_VAL)*100),0))
5. Replace: Answer "Y" to the "Use This Expression" prompt. When done, the field you selected to replace will now contain the "Loan To Value" percent.
6. Run Query: Press F6 for the "Query Descriptions" menu, and press F3 for "New Query". Select the "Replaced" field used in step # 3 above, and select the query condition you wish to use.

Example: You want all Loan To Value amounts greater than 80%, and the field you put it in was ASSD_VAL, then the query expression would end up being "ASSD_VAL Greater than 80". You'll probably want to save the query, so be sure to give it a descriptive name (e.g., for the example here, "Loan To Value Percent > 80").

Using the "Loan To Value" expression and replacing either a numeric field whose length is less than 8 or a character field.

See "Farm File Layout" Techfax for information on field types.

Please note that the field names listed below as replaceables, are not given as suggestions (it's up to you to determine which fields are vital or not) but as examples.

To replace a Numeric field with a length of 2 (such as PCNT_IMP) with the "Loan To Value" rate, use the following expression in Global Replace:

(VAL(STR(IIF(TFR_VAL>0,((FIRST_TD/TFR_VAL)*100),0),2)))

STR(xxxx, 2) converts numeric expression (shown by xxxx ) into a string (character expression)

The 2 limits conversion to 2 characters

(note: when limiting the length to 2, values of +100% will appear as zero!)

VAL() converts string expression into a numeric

This may seem a little awkward, but program will not let you stuff a larger numeric value directly into a smaller field. small office, home office, soho, soho software, real estate agent software, realtor software, mortgage broker software, real estate broker, real estate tax data software, tax roll data software, property data software, real estate property software, real estate database management software, real estate listing software, real estate software, real estate management, lending software, lender software, mortgage software, contact management, listing management, cma sofware, cma report software, closing management, escrow management, escrow software, closing software, closing manager, escrow manager, campaign manager, financial calculator, financial software, refinance software, refinancing software, loan qualification software, amortization software, scheduler software, scheduling software, real estate spreadsheet, settlement sheets, bulk mailing, client management, telemarketing software, real estate database software, mls software, multiple listing service software, scheduler, spreadsheet, settlement sheets, INSTANT IMPACT! GOLD, INSTANT IMPACT!, instant impact gold, Information Products, salesimpact, sales impact, the lenders edge, lenders edge, lender's edge, INSTANT IMPACT! GOLD, INSTANT IMPACT!, instant impact gold, Information Products, salesimpact, sales impact, the lenders edge, lenders edge, lender's edge