cancel
Showing results for 
Search instead for 
Did you mean: 

County Field

BBF
Contributor

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.

 

1 ACCEPTED SOLUTION

AnthonyValidity
Validity Team Member
Validity Team Member

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", ""))

AnthonyValidity_0-1689855322664.png

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!

Anthony Lardiere Jr
Senior Customer Success Manager

View solution in original post

3 REPLIES 3

AnthonyValidity
Validity Team Member
Validity Team Member

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", ""))

AnthonyValidity_0-1689855322664.png

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!

Anthony Lardiere Jr
Senior Customer Success Manager

BBF
Contributor

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
Validity Team Member
Validity Team Member

My pleasure!

Anthony Lardiere Jr
Senior Customer Success Manager