Querying on multiple solicitor types

Rob Crenshaw 2

I'm trying to figure out if there's a way to create a dynamic constituent query in RE that will work to query on multiple solicitors by solicitor type. We use three different solicitor types: Primary, Secondary, and Ambassador. I want to build a dashboard that will show a list of constituents that have shared solicitor assignments, but with different solicitor types.


For example:
(Assigned Solicitor Name equals Jane Smith AND Assigned Solicitor Type equals Primary) AND

(Assigned Solicitor Name equals John Jones AND Assigned Solicitor Type equals Secondary)


When I try to build a query with criteria that uses this structure, I get zero results even though there should be some. I know I could probably get this to work via merged queries, but I want a dynamic query so I don't have to refresh it every time there's an assignment change. Anyone have any workarounds?

shani traum

Only asking as I've done this :-)


Are you sure you're entering the names as the system wants them?  FirstName MiddleInitial. LastName


So, Shani D. Traum in my case. (And it does want middle initial only followed by a period)


I you don't type it the correct way, it will not return any results.


Because your query looks correct, I believe.


Have you tried running it with just one half of the criteria (Solicitor name is Jane Smith and Solicitor Type is Primary) to see if you get any results?


Good luck!


 

Rob Crenshaw 2

I've just been using the "Last Name" field for the solicitors, and I've triple-checked to make sure they are spelled correctly. ☺


I can get the query to work if I just add the solicitor type once (as "equals" or "one of"), but it seems to break when I add the second set of criteria with another name and solicitor type.

Alan French

When you add the second set of criteria, RE is applying it to not only every constituent but to each of their solicitor relationships. Since no relationship can simultaneously be for two difference solicitors or two different types, it gives zero results.


There is a feature with the "one of" operator that might help you get closer to the right result. Ticking the box labelled "each record must meet all specified criteria" should change the query so that it is looking for all constituent records that have relationships with both those solicitors/solicitor types. I've just had a quick test and it was fairly close but did include some records I didn't want, because solicitors 1 & 2 were the right name but solicitors 3 & 4 were the right types.

Tammie Cach Abdu

Did you try changing your And to an OR : (Assigned Solicitor Name equals Jane Smith AND Assigned Solicitor Type equals Primary) OR

(Assigned Solicitor Name equals John Jones AND Assigned Solicitor Type equals Secondary)



 

John Heizer

I find the "Assigned Solicitor Name" field to be almost useless for criteria (but okay for output).  Any slight change in the record (changing a middle initial) and it breaks.  Instead I use the "Specific Record" field.