Is it possible to generate a report to identify say 2 or 3 criteria that may appear in a custom field? In other words if we could specify two or three terms with an OR function.
In the example above should I specify multiple lines with the “does not contain” function it can give me the result I need but means entering many lines to eliminate all the “does not contain” terms to eventually reveal only the data of interest.
For example if I wanted to identify data for “Ben and Jim” I will need to add many “Where lines” for Serviced By filtering “does not contain” for all the other service staff members to produce a result. So what I am trying to say is if the serviced by field could be filtered and act in a way on say search terms that “contain” say “Ben” OR “Jim” it would be great. I do not seem to get this working with the functions available. Could someone point me in the right direction please?
I see no way to accomplish what you want, @compuit. The Where … entries are filters. They are executed sequentially like Boolean AND operations. So, in your example, once a record does not contain Ben, it is no longer considered. You cannot filter a record out and still ask whether it contains Jim.
Another way to look at this is that the Where … filters progressively reduce the number of transactions being considered until those to be displayed remain.
Likewise, if you use a contains > Ben filter, only records with Ben are passed through. So you cannot then ask if one of the remaining records contains Jim.
Your suggestion is really asking the program to run two separate queries and merge the results. The program is not that sophisticated. So, the easiest way to identify data for Ben and Jim is to define two separate reports.
Yes two reports are what I have been running. As mentioned the “does not contain” works recursively and delivers but has potentially a lot of place for error due to the many “does not contain” entries one has to input.
Two reports seems much easier than one report with many does not contain instructions, especially since you can clone custom reports. All you have to do is get one set up, clone it, and change the name from Ben to Jim.
@compuit, the point I was making earlier was that it is a long way from being able to construct a query with progressive filtering and being able to merge the results of two distinct queries, both with progressive filtering. (That is what your concept of OR amounts to.) And, as soon as a capability to do two was added, somebody would want three, four, five, or ten. That quickly gets to be fairly sophisticated programming.
So there is basically currently and implied AND at the end of each where clause line. Could it be exposed such that folks could change from an AND to an OR.
The one complexity is that the OR implies grouping
i.e. we currently have
WHERE
FIELD 1 = “ABC”{implied AND}
FIELD 2 = “XYZ” {implied AND}
FIELD 3 = “NO”
If we could expose the AND operator and allow the user to change to and OR it would be slick
WHERE
FIELD 1 = “ABC” AND (explicit)
FIELD 2 = “XYZ” OR
FIELD 3 = “NO”
The one complexity is that the we really have
WHERE
FIELD 1 = “ABC” AND (explicit)
(FIELD 2 = “XYZ” OR
FIELD 3 = “NO”)
Anyway the ability to change see and change the IMPLIED AND operator would be fantastic. Anyway, just an “IDEA” if there isn’t one out there already.
