County Field

[BBF]

We have a county field on the account form that we like to keep populated

How would you suggest that we use VDT to manage this field so it gets populated to the county that goes with the zip code on the account . 

I have a way to do this today, but would love to have a way to use VDT to populate 

The data is currently stored in a spreadsheet.

[AnthonyValidity · Accepted solution]

Hi @BBF ,

The only way I can think of doing this with DemandTools 5.x is by using Modify.  You'd have to put everything in you spreadsheet into a nested formula.  Here's a short example:

IF(OR(${BillingPostalCode}=08817, ${BillingPostalCode}=08837, ${BillingPostalCode}=08857), "Middlesex", IF(OR(${BillingPostalCode}=08844,${BillingPostalCode}=08853,${BillingPostalCode}=08869), "Somerset", ""))

It'll take some time to write it all out but then it'll be automatic if you schedule it moving forward.

This one would be used if you're using more than five digit zip does - you'll just only match to five digits:

IF(OR(LEFT(${BillingPostalCode},5)=08817, LEFT(${BillingPostalCode},5)=08837, LEFT(${BillingPostalCode},5)=08857), "Middlesex", IF(OR(LEFT(${BillingPostalCode},5)=08844,LEFT(${BillingPostalCode},5)=08853,LEFT(${BillingPostalCode},5)=08869), "Somerset", ""))

If this is for every State I'm not sure how practical this would be.  Another option would be to export the records that have blank data in the county, use a VLOOKUP with your reference spreadsheet, and then update using the Import module.  

Hope this helps!

[BBF]

I do the vlook up today and will continue. However the formula will come in handy!!!!

Thank you for explaining how to do this!

[AnthonyValidity]

My pleasure!