How do I know which fields to choose in query? I know what information I need to report on, but not how to pull it.
Queries are all about fields. Remember when you entered a birth date on the Personal tab of a constituent record? That’s the Constituents: Birth date field in a query. There are a lot of fields available in Query, as query is just a list of all the fields on all the different types of records in the program. Ultimately, you just need to know where to look in the list. Date of revenue for constituents? Constituents > Revenue: Date.
Finding the Correct Field
There are a couple of tips that will help you identify the correct field.
Start with the correct source view
The source view of a query answers the question “What type of information or records are you looking for?” There are several source views to choose from when creating a query, and each source view will have different fields available to choose from.
A good example would be building a query to group ticketing records. Do you want to know who purchased the tickets or do you want to know how many and what types of tickets were purchased? If you are looking to find out the “who” you would start with a Constituent source view and would browse for fields in Sales Orders. However, if you are looking for the “how many” and “what types,” you would start with a Sales Orders source view and browse for fields in the Sales Orders.
To get where you need to go, it is important to always start in the right place.
Know exactly what you are looking for
There are many fields in Altru to choose from. In order to make creating your query painless, make sure that you know exactly what information it is that defines the records you are looking to group in the query. You could start building your query by knowing that you are looking for membership information, but you will probably end up spending extra time piecing all of the criteria together in an illogical manner. However, if you start your query process off by laying out all of the different pieces of information you are looking for, you will save time. Knowing that you need to see all primary members with a family level membership with an expiration date between July 1st and December 31st and a membership transaction date in the month of May gives you a much clearer indication of where you will need to browse for fields. In this case, most of the needed fields are found in the membership transaction.
In most cases, you are going to be much more familiar with the way you see Altru on a day-to-day basis than you are with what you see in Query. Looking for fields the way that you are used to seeing them can help you identify the same field in query. Let’s say that you are looking to group together all constituents who have a relationship of “Golf Partner” and who that relationship is with, so that you can include those people in the invitation to your annual golf outing fundraiser. You know that when you enter that information in to Altru, you find the constituent record, select the Relationship tab, and click Add individual under relationships.
You also know that within the Add a relationship screen you are able to define who the related individual is, as well as the type of relationship they have with the constituent.
This information will help you determine that to build the query you will need to start from the Constituent source view and then browse for fields in Relationships, and you will need to select the field Relationship Type and include records where Relationship Type is equal to Golf Partner to correctly group your records.
Find Field Feature
The Find field feature in query allows you to search fields by keywords. This can be very helpful, but it is important to note that what you have selected in the Browse for fields in section of a query will change the results of the Find field feature.
Process of Elimination
There will be times where you cannot find the field that you are looking for and if all of the other tips have not worked resorting to pen and paper can be useful. Start by writing down the Browse for fields in sections you think the field might be in. Open up a query and start to search for the field. You may have to add additional sections underneath your original section before you can find the field. If the field that you need is not in the section you thought, cross that section off of your list. Continue this process until you have one or two sections listed. Test and see if the fields in those sections provide you with the results you were expecting.