Talis Developer Network - how to exclude borrowers with a particular standard message from a report - Comments

Excluding borrowers with a message

lcartwright — Mon, 1 Sep 2008 10:59:44 +0100

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

Excluding borrowers with a message

Brian Crampton — Thu, 28 Aug 2008 17:57:25 +0100

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

lcartwright — Tue, 26 Aug 2008 17:37:47 +0100

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

Brian Crampton — Tue, 26 Aug 2008 15:48:30 +0100

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

how to exclude borrowers with a particular standard message from a report

lcartwright — Tue, 26 Aug 2008 13:17:24 +0100

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.