how to exclude borrowers with a particular standard message from a report
Submitted by lcartwright on Tue, 2008-08-26 13:17.
I am a very new user so please excuse me if this is simple
I want to produce a list of borrowers with long overdue loans and to exclude from the list borrowers who have a particular standard message. if I use a query filter that excludes this message I still get borrowers with this message included in the output I guess because they also have other standard messages. including or excluding the standard message from the output makes no differenc. if I include all standard messages in the output and then use a report filter I get the same results.
Excluding borrowers with a message
If you are using a query filter
Standard messages in list Block Borrower
then it should only pick up the borrowers with that message. With the report filter it is more complicated because you are just restricting the row with the message and not the other message rows for a borrower.
However the other objects used may be causing a problem. Could you list the objects used in the query panel for both reporting and conditions. Can you include the classes the objects come from and what operands and values are used in the conditions.
Brian Crampton
Developer, Talis
Excluding borrowers with a message
in the query panel for conditions I am using
from the circ universe, borrowers
object: borrower type
operand: in list - selected borrower types to include
from borrowers, conditions I am using
object: standard messages
operand: not equal to 'selected standard message'
from Loans, due date reporting
object: due date
operand: less than or equal to 'selected date'
filter:limit to current issues
in the reporting panel I am currently using only
object:borrower details, barcode
Just so we are clear I am trying to EXCLUDE from the list borrowers who have a particular standard message from the list
Excluding borrowers with a message
The reason for this is that the data returned has a separate row for each message that a borrower currently has. While you can exclude the row that has the message it is difficult for SQL to act across rows.
You need to approach this in a different way.
One approach would be to do two queries. One with all borrowers and then one with borrowers that have the message. By synchronising the queries and using report filters you can bet a list. There is an article Synchronising queries in Talis Decisions that discusses the theory of joining queries.
The other approach is to use a subquery within the main query. The sub query returns borrowers with the message but then the main query says to exclude the borrowers returned from the subquery in the main query results. The article on this has just been published at Using subqueries in Talis Decisions
There are advantages and disadvantages in using either and the best approach has to be judged within the context of the actual document required.
Thanks
Brian Crampton
Developer, Talis
Excluding borrowers with a message
Hello Brian
thanks for the replies
the subquery solution works fine and takes about 3 mins to run
I can't get the synchronised solution to work but I suspect that this is still because a borrower can have multiple messages,
just wanted to let you know how impressed I am by the support via the TDN, thanks
Lin