From time to time our Rate tables need updated, which sometimes brings a pretty high stress level. Here is a method which will allow new underwriter rates to be applied to SoftPro ProForm Classic preventing the update from deleting all the current information. SoftPro distributes a .txt file which the lkupimex.exe utility uses to apply lookup table updates. How nice it is to test the new rates before everyone is using them.
The first step is to copy the file and rename it with an .mdb extension. Here are original file is CTCO85_15.txt which we want to keep as is. The copied file has been renamed CTCO85_15 Testing.mdb. There are a few ways to copy a file all beyond the scope of this posting.
Note that the first 2 letters of your file will be your underwriter, in this case it's Chicago Title. The next 2 characters are the State the rates are for followed by the split. This is a naming convention in use by SoftPro for many years. We'll need those underwriter letters later. (CT)
Open the new file in Microsoft Access
If you get the security warning you can safely click OK. The 2 tables we are interested in are RATETABLE and UNDERWRITER_POLICYCODE.
Let's start with the Rate Table, open it and press <Ctrl>F to bring up the Find Replace Dialog.
Set the Find What to the 2 letter Underwriter code followed by the 2 letter state code.
Set the Replace With to a unique 2 letter code that isn't going to match an underwriter code. I used "DV", it seemed safe as does things like 11,ZZ,Q1. Numbers should be OK, I wouldn't use special characters like "#$" they could really throw things out of whack. Follow that with the 2 letter state code.
Set the Look in to Current Field.
Set Match to Start of Field.
Press Replace All.
Access will respond with
At this point you can see the changes in the background, Click Yes if the one's you can see look good. Close the Find and Replace Box and Close the RATETABLE table.
Note, don't not use the state code in the find and replace boxes. Don't add extra characters in the replace, it's a one for one replacement only.
Let's move on to the more complex UNDERWRITER_POLICYCODE table. The table itself is more complex what we're doing changes little. Double click to open the table and press <Ctrl> F to bring up the Find Replace dialog. We're doing the exact same replacement with a change to the Look In field.
Fill out the form as we did above, setting the Look In field to Current Document.
Press the Replace All button, and Yes on the can't undo dialog.
We're done with data changes, so close the Microsoft Access windows and close Access.
Rename the CTCO85_15 Testing.mdb file as a .txt file
And use the lkupimex.exe utility to bring the new test rates into the system.
Here's what the lookup table looks like once it's imported. I sized the columns to show some important columns, your lookup table will look different.
Use the new DV* policies to test the new rates. Once you have verified that the test rates are OK apply the original CTCO85_15.txt file to bring in the correct information.
You can leave the test rates on your system since they are named out of the way. Or you can delete them using ProForm, don’t forget to delete the test rate tables also. Or if you have a SQL it's a quick command to delete records that start with 'DVCO'. Tell the admin to delete all records that start with DVCO in the ProFormLookups Database Underwriter_Policy Lookup code table for the column Underwriter_PolicyCode. Also from the RateTable table for the column RateTableCode.The SQL command would look like this: Delete * from RateTable where RateTableCode like ‘DVCO%’
Maybe SoftPro will start creating a special underwriter SP so they could distribute test rates at any time.
