Filtering results in a Query

Options
Thomas Traegler
Thomas Traegler ✭
Ninth Anniversary Photogenic First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion
Hello community,


I attempted to search for this answer, but did not find it, so here I go...


I am attempting to write a Query to get some donor data. One of the fields I need is an alias. We have multiple Alias types set up and I want my results to include all Constituents and output this specific alias field (no matter if the field is filled in or not). My problem is that if I add teh field as a criteria, I only see the constituents who have data in that field, and if I do not, I get duplicates if there are constituents with multiple Aliases. I can filter this in an export, but as I am using this for ImportOmatic's export feature, it only calls for Query and not an export.


Any suggestions are appreciated.


-Tom
Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    That's how the alias field (a one-to-many field) will work in query.  As you said can filter in export.  Do you have this alias type on records with blank-no data entered?  If so, maybe you could do a criteria for that alias is blank.  Then merge the two queries into one: Alias 1 is not blank query AND Alias 1 is blank query.    Don't know how Import-o-Matic integrates but merged query may give you data you need.  Or it may pull every record.  My RE just ran into all kinds of errors and is shutting down so I can't test it but... maybe...

     
  • Thomas Traegler
    Thomas Traegler ✭
    Ninth Anniversary Photogenic First Reply
    Thanks. Yes, the Alias is on the Constituent record and may or may not be filled in. I am looking for 1 row with all constituents and teh results from that one Alias field (regardless of it is filled in or not). I never tried a query within a query, but I can experiment with that.
  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    Thomas - it's not a query within a query but two separate queries that you determine if the result is all records from both queries, only the records in both queries, records in query A but not B. 


    Here's several KB solutions on it: https://search.blackbaud.com/#q=what%20are%20merged%20queries&sort=relevancy


    I'm still a little confused - do you truely have blank aliases?  In our database the only way an alias gets on a record is by user entering that type.  So you have users who entered alias type and never put in the data or it was deleted? Does the alias default in when you create your constituent records?
  • Nicole S
    Nicole S ✭
    Ancient Membership Facilitator 3 Name Dropper Photogenic
    Hi Thomas -- I may be misreading your question, but it sounds like you are putting the field in criteria which will look for records that match that info. If you want all records then leave the criteria blank and on the output  tab put the alias field. Then on the reults tab you will see all the records and info on that field regarless of whether it is blank or not.


    Just a reminder though -- query is a grouping tool, not an exporting tool wink You will see dupes because it is a one to many field.
  • Thomas Traegler
    Thomas Traegler ✭
    Ninth Anniversary Photogenic First Reply
    Here is an example:
    Sample Data:

    Const_A               Alias 1 = AAA       Alias 2 = XXX

    Const_B               Alias 1 = BBB       Alias 2 = YYY

    Const_C               Alias 1 = CCC       *

    *Const C has no alias type 2

     
    Unfiltered results

    Const_A               AAA

    Const_A               XXX

    Const_B               BBB

    Const_B               YYY

    Const_C               CCC

     
    Expected results: (all constituents and Alias 2)

    Const_A               XXX

    Const_B               YYY

    Const_C               **

    ** Const C should be listed, but have a blank Alias 2 result


    Now, I understand that a query is not usually used for this, but using ImportOmatic's export feature, Query is the only option I have for grabbing the data. And I am versed in SQL, so basically I am looking to do the equivelent of a SQL outter joint to grab all constituents and only aliases where alias equals type X. I was just hoping there was some sort of work around in RE to get this filtered.
  • Ryan Hyde
    Ryan Hyde ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    I don't think there's a workaround for the way you've entered data. I would suggest that you reformat the data yoru database entirely. Whatever you're using Alias for, it may be better to save that as an attribute or something else. You could reformat the data pretty easily by querying for anyone with an alias, exporting constituent ID and each alias type, and then using that query to import back in attributes (for instance) with specific descriptions. Globally cleaning up the old aliases may be difficult - not sure, I've never tried it. But converting them to attributes shouldn't be exceedingly difficult. It'll just take some time to set up your tables correctly and make sure your import is set up appropriately. 
  • John Heizer
    John Heizer ✭
    Ancient Membership Facilitator 3 Photogenic
    You could get the result you're looking for using SQL to directly query the RE database through Blackbaud's Read Only Database Assistance module (comes for free with upper levels of support), but I don't think that would work with OMatic.  You're probably going to have to go to OMatic's tech support to get this one solved.

Categories