{"id":389,"date":"2013-02-04T15:35:14","date_gmt":"2013-02-04T20:35:14","guid":{"rendered":"https:\/\/danvanfleet.com\/?p=389"},"modified":"2013-03-11T13:33:02","modified_gmt":"2013-03-11T18:33:02","slug":"proform-enterprise-county-lookup-table-updates","status":"publish","type":"post","link":"https:\/\/danvanfleet.com\/index.php\/proform-enterprise-county-lookup-table-updates\/","title":{"rendered":"ProForm Enterprise County Lookup Table updates"},"content":{"rendered":"

Today a client asked if we could add $7.00 to the first page of all Mortgage Recording fees in the state of California.  There are 58 counties in California, some of them have multiple entries for varying costs. <\/p>\n

<\/p>\n

Doing this sort of update manually is a huge pain.  There are a number of different ways to mass update data in the lookup tables.  I’m not a fan of using ODBC connections in Excel or Access to update lookup tables, there are slight differences in data formats which can cause information to disappear during the update.  So it’s SQL Server Management Studio to the rescue.<\/p>\n

The only catch with using SQLSMS to do the update is the data is stored as a nvarchar() format, which is all good, but viewed as a string rather than a decimal.  So a conversion of the data in the column needs to take place during the update.<\/p>\n

In this client’s case (and many others) the column that needs updated is called “Mortgage_Cost”.  ProForm uses a translation table to connect Fields to database columns.  To find the correct column name first it’s necessary to find the field name that needs updated.  In this case it’s MTRTE1 visible on the 1200 formula screen.  Next bring up the lookup table and Modify Table, to show the Create\/Edit Lookup Table screen<\/p>\n

\"image\"<\/a> <\/p>\n

Find the field in question and notate the the value to the right of the equal sign, that is the column header in the table.  The Table name itself is listed at the bottom of the screen.<\/p>\n

For this update, the table name is County, and the Column name is Mortgage_Cost.  let’s make the SQL statement that will update the table.<\/p>\n

\n
\n
UPDATE<\/span> [ProFormLookups].[dbo].[COUNTY]<\/pre>\n
<\/p>\n
   SET<\/span><\/pre>\n
<\/p>\n
      [Mortgage_Cost] =  (Cast<\/span>(Mortgage_Cost as<\/span> Decimal<\/span>) + 7.00)<\/pre>\n
<\/p>\n
 WHERE<\/span> STATE<\/span> = 'CA'<\/span><\/pre>\n
<\/p>\n
GO<\/pre>\n
<\/div>\n<\/div>\n
 <\/p>\n
Click the Execute button and the table will be updated.  Now I highly recommend doing this on a test table before doing it on your live data, make sure you know precisely what the command is going to do prior to going live.<\/p>\n
Notice that even though we are adding an even $7, there are 2 decimal places used.  ProForm likes to see 2 decimal places and by using “7.00” rather than “7” we maintain those 2 places.<\/p>\n
If you fail to use the Cast as Decimal SQL will concatenate the strings together putting something like “40.007.00”  in the field, which of course will not evaluate in ProForm properly.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today a client asked if we could add $7.00 to the first page of all Mortgage Recording fees in the state of California.  There are 58 counties in California, some of them have multiple entries for varying costs.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[23],"tags":[28,21],"class_list":["post-389","post","type-post","status-publish","format-standard","hentry","category-softpro-2","tag-lookup","tag-proform","entry"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4wyVb-6h","jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/389","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/comments?post=389"}],"version-history":[{"count":2,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/389\/revisions"}],"predecessor-version":[{"id":508,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/389\/revisions\/508"}],"wp:attachment":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/media?parent=389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/categories?post=389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/tags?post=389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}