formula/sandbox help

[GrokWhy]

Hi,

I'm working on a formula to convert postal code to region. I have an entity I've created with all the postal codes and their associated region.

I've put this in the formula sandbox

record\findOne('ZipCodesWI', 'zipCode' , 'asc', 'zipCode=','53006')

No syntax error, it says it ran successfully.

How do I see the result?

The tooltip on the Output section says... Use output\printline .

What is this trying to tell me?

Thanks!
Russ

[espcrm]

Hi GrokWhy,

I'm still on v6 so I can't test the FormulaSandbox... but if there is no syntax error, why not just test it live now and see if it work?

And I'll be keeping an eye on this thread! It very annoying at the moment having to remember Postcode numbers when I write the City.

[GrokWhy]

espcrm,

This is just the first step of a more complex process. I want to understand the formula sandbox as it will be very helpful in troubleshooting.

[espcrm]

GrokWhy sound good, look like you got a big plan. Hope to see your progress.

[Kharg]

You have to press the run button and use your formula like this:

$test = record\findOne('ZipCodesWI', 'zipCode' , 'asc', 'zipCode=','53006');
output\printLine($test);​​

[GrokWhy]

Kharg,

That got it!

It returns the record id... now to figure out how to get the actual data.

Thanks!

Russ

[GrokWhy]

FYI, so far.

This returns the region name based on the postal code

$zipId = record\findOne('ZipCodesWI', 'county' , 'asc', 'zipCode=','53006');
$county = record\attribute('ZipCodesWI', $zipId, 'county');
output\print($county);

In my Entity, I have 2 fields; county (the region) and zipCode (postal code). The entity contains a complete list of postal codes and regions for the area of concern that I imported.

I need to

• add logic to pass the postal code to the function
• return the region
• limit the postal code passed to the first 5 characters
• verify the postal code is found, otherwise return 'unassigned'

[GrokWhy]

This appears to be working. This is a formula on the Entity Contact. It fires when the record is saved.

ifThen((county == '_Find From Zip' || county == '_Unknown' || county == 'Not Set') || (county == '' || county == null),
(
$recCount = record\count('ZipCodesWI','zipCode=',addressPostal Code);
ifThenElse($recCount == 1,
(
$zipId = record\findOne('ZipCodesWI', 'countyName' , 'asc', 'zipCode=',addressPostalCode);
county = record\attribute('ZipCodesWI', $zipId, 'countyName');
),
(
county = '_Unknown';
)
);
)
);

The region (county) is a dropdown on the Contact screen. It's an Enum of the region list. It defaults to Not Set. If the dropdown value is _Find From Zip, _Unknown, or Not Set, when the record is saved, the formula looks up the countyName (ZipCodesWI Entity) based on the postal code. If found, the county is updated, otherwise set to _Unknown.