07-20-2023 05:01 AM
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.
Solved! Go to Solution.
07-20-2023 05:25 AM
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!
07-20-2023 05:25 AM
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!
07-20-2023 05:53 AM
I do the vlook up today and will continue. However the formula will come in handy!!!!
Thank you for explaining how to do this!
07-20-2023 12:31 PM
My pleasure!
Get industry news, expert insights, strategies, and hot tips, served up fresh every week.
Visit the Validity blog