topic Re: County Field in DemandTools

County Field

BBF — Thu, 20 Jul 2023 12:01:31 GMT

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.

Re: County Field

AnthonyValidity — Thu, 20 Jul 2023 12:25:35 GMT

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!

Re: County Field

BBF — Thu, 20 Jul 2023 12:53:10 GMT

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

Thank you for explaining how to do this!

Re: County Field

AnthonyValidity — Thu, 20 Jul 2023 19:31:50 GMT

My pleasure!