Calculating One Value from Three Possible Values

[Former Member]

I want to generate a list of customer phone numbers to call from a database. A customer may have a primary phone number, a business phone number, and a cell phone number. I only want one to call one phone number from each customer. If the primary phone number is available, then it should be on the list. If the primary number is blank or 999-999-9999, then list the business phone number instead. If the business phone number is blank or 999-999-9999, then call the cell number instead. If the cell number is blank or 999-999-9999, then don't list the customer at all.

Also, only list distinct phone numbers so that the same phone number isn't called more than once.

I've tried various approaches, but can't get this work. Any ideas on how I should attack this?

Thanks,

Eric

[Former Member · Accepted solution]

For the 1st question... A formula like this should do the trick:

IF NOT IsNull({TableName.PrimaryPhone}) 
AND {TableName.PrimaryPhone} <> "" 
AND {TableName.PrimaryPhone} <> "999-999-9999" 
THEN {TableName.PrimaryPhone} ELSE
IF NOT IsNull({TableName.BusinessPhone}) 
AND {TableName.BusinessPhone} <> "" 
AND {TableName.BusinessPhone} <> "999-999-9999" 
THEN {TableName.BusinessPhone} ELSE 
IF NOT IsNull({TableName.CellPhone}) 
AND {TableName.CellPhone} <> "" 
AND {TableName.CellPhone} <> "999-999-9999" 
THEN {TableName.CellPhone} ELSE
"No Phone Number Available"

As for your 2nd question...

Also, only list distinct phone numbers so that the same phone number isn't called more than once.

This is a function of your data. If you have multiple customers that share the same phone number, then it stands to reason that this 1 phone number would legitimately apply to multiple customers. I could see not wanting to call the same home number multiple times for different customers who live under the same roof but what about different customers who work for the same company?

You need determine exactly what you want to happen in the event there are matched numbers, including which customer takes priority in terms of which one keeps the numbers and which ones get eliminated.

Jason