I'd like to select, in this example, an email address from a set of fields by using a weighting system.

Let's say I have three fields which each contain an email address, but I only want one address returned. I have an order of preference in terms of which field is more reliable than the next, and I want to select only the most reliable email from the list of potentials.

In pseudo code:

if(email1 exists) {
    return email1
} else (if email2 exists) {
    return email2
} else if(email3 exists) {
    return email3
} else {
    return nothing
}

Can this be achieved through query/export ? 

Though I have a background in database/code I'd rather keep the number of steps/separate operations to a minimum. Exporting and then manipulating data externally isn't something I really want to have to do with an application that costs as much as RE does...

 
Thanks,
Pete
 

Pete, here's a copy of a posting of mine from 2007.  Is this of interest to you?

We want to conduct email solicitations to email addresses that are contained within Raiser’s Edge and to use a process similar to the mailing process to identify one appropriate email address per constituent or contact. This process could be utilised from Export or Administration. In our case, we have three defined email addresses of Email Personal, Email Business, and Email Preferred (for when there is more than one). The ideal would be for Raiser’s Edge to have a facility similar to the mail and export address processing feature i.e, Individual records Use preferred address, 1st selected email address, if not 1st choice use 2nd choice, if not 2nd choice use 3rd choice, if no address – omit from run. Organisation records Use contact 1st choice email address, if not 1st choice use 2nd choice, if not 2nd choice use 3rd choice, if no contact email address found, use organisation preferred 1st choice email address, if not 1st choice use 2nd choice, if not 2nd choice use 3rd choice, if non found omit from run. This has now been filed as a suggestion and can be found at solution ID BB332234. If this interests you please add your name to the list.

I don't think the solution ID is still valid, perhaps because of a poor response.

Regards

Alan

Heh, yes. It's of great interest to me, but as you say it no longer exists. 

Anybody got a transcript?
 

Anybody? :)

P 

Hi Peter - this should give you what you are looking for.

In this example I'm assuming the order of importance of email types is:

Email 1
Email 2
Email 3

1. Create a new constituent query. Filter on:
Addresses, Preferred Address, Preferred Phones, Preferred Specific Phones, Email 1, Email 1 number is not blank.
Save the query.
This produces constituents that have Email 1.
Export data to excel or other application.

2.  Create a new constituent query.  Filter on:
Addresses, Preferred Address, Preferred Phones, Preferred Specific Phones, Email 2, Email 2 number is not blank.
And Addresses, Preferred Address, Preferred Phones, Preferred Specific Phones, Email 1, Email 1 number is blank.
This produces constituents that have Email 2 and excludes constituents with Email 1.
Export data to excel or other application.

3. Create a new constituent query.  Filter on:
Addresses, Preferred Address, Preferred Phones, Preferred Specific Phones, Email 3, Email 3 number is not blank.
Save the query. 

Create a new constituent query.  Filter on:
Addresses, Preferred Address, Preferred Phones, Preferred Specific Phones, Email 1, Email 1 number is not blank.
OR Addresses, Preferred Address, Preferred Phones, Preferred Specific Phones, Email 2, Email 2 number is not blank
Save this query.

Merge the first query with the second query using the subtract operator.
This produces constituents that have Email 3 and excludes constituents that have Email 1 or Email 2.
Export data from merged query to excel or other application.

Is that really the easiest option?

I think the Mail tab options include a system close to what I want, where I can select preferred address, and if no match is present choose an alternative address.

Rather than use the method above, I'd be more tempted to filter where (Email1 not blank or Email2 not blank or Email 3 not blank), export each three emails along with my other data, pull into Excel (for example) and create a field in Excel which does something like:

IF(NOT(ISBLANK(A1, A1, IF(NOT(ISBLANK(A2, A2, A3))))))

I just hoped that RE would offer this level of sophistication within the select/filter stage :/

Pete
 

Why not use export?  You can export the 3 types and sort it by phone type.  Then use your formula when you open the spreadsheet.

Helen

Pete, the Mail module may work depending on which fields need to be included in your data file.  Using Query and Export will give you access to all fields in the database, whereas the fields in Mail are limited.

Helen,

That's my best option so far. What I was hoping for was the ability to remove the need for any post-processing, like the formula I posted. I had hoped RE would give me some kind of option to do this, I may post it as a suggestion.

Kevin,

I was only using Mail as an example of the system existing. What I really want is the option to do it within Query and/or Export. 

Thanks for your answers guys. 

I feel like there is an answer possible using Export and exporting 2+ phone numbers restricted to the e-mail address types and specifying an Order By. Theoretically, if we can identify the correct Order By sequence, you will always end up with your desired e-mail in the first slot. I think there is still a bug/feature where if you only choose one output, it will be random, but with 2 or more, it uses the Order By sequence.

The problem is that I can't identify how Order By is going to work in this context since you aren't given the ability to specify your preferential order. I was thinking that maybe "Phone Type" would work if you either put them in the preferred sequence in the table or name them alphabetically in the sequence you desire. However, that doesn't seem to be the case. On my system, no matter what I do, when I Order By Phone Type, they come out in 2, 1, 3 sequence. I've even looked at the TableEntries table in RODBA and can't figure out why this would be the preferred sequence.

???

Mitch

Thanks Mitchell, but I'm not sure if you've posted an answer or a question..

Using Order By will only allow me to put one phone type before another, but still maintain a list of three (or however many I choose) addresses in the output.

What I want is to distill three inputs into one output based on an order of preference (Select Email1,Email2,Email3 and get in return only one Email field) , rather than simply select all three and have one appear before the next.

Anybody got any clues? 

Pete 

Also, referring to your point:  "I've even looked at the TableEntries table in RODBA and can't figure out why this would be the preferred sequence." 

Do the entries for each phone type run in ascending order of their table ID (unique identfier) ? Or of their phone name?

Failing that, then it's likely to be the natural DB order, which will be based on what order they were adde din and also whether other entries have been removed in the meantime.

 
P 

Actually, I hoped it was an answer with just a bit more to figure out

I was thinking that, even though you would be getting more than one e-mail address in the output, the first one would be the one you actually wanted and the others could be ignored or deleted - depending on the usage.

Regarding the TableEntires - that's the weird thing. In Export, they come out in 2, 1, 3 order. In the table they are sequenced 1, 2, 3 and their table id is in 3, 2, 1 order! Oh, and alphabetically, they should be in 1, 2, 3 order as well.

None of the other order options seem likely to help. Looks like a blind alley. Sorry

Mitch

Thanks for your time anyway Mitch.

"I was thinking that, even though you would be getting more than one e-mail address in the output, the first one would be the one you actually wanted and the others could be ignored or deleted" - sadly, it would order them by their field name, and not by their existence, so Email1,2,3 would always be held within the same vertical column (so I'm aware).

In fact, I'm just pleased that my last post took me from a Top 500 contributor, to a Top 200 contributor. Time to eye up the Top 150 scalp methinks..

;)

P