Joined: 07 Jun 2010
Online Status: Offline
Posts: 40
Topic: How do I do this? Posted: 04 Apr 2014 at 5:02am
I need to create a report where I have to show a list of customers who do not have invoices.
The invoice table links the customer table using an unique customerNo field, i.e. {invoice.customerNo} -> {customer.customerNo}. In the report I just need to show a simple list of Customer records where it cannot find an Invoice record with a matching customerNo. How would I go about doing this? I assume that I would simply add the 2 tables in to the report and not have them linked. Then I need to somehow suppress customer records where finds an invoice record but I'm not sure how to execute that part... or is there something that I would put in the select expert instead?
Joined: 07 Jun 2010
Online Status: Offline
Posts: 40
Posted: 04 Apr 2014 at 6:29am
No, I'm displaying Customer.CustomerName because the CustomerNo is just an internal ID that does not make sense. Tried changing to display Customer.CustomerNo but that doesn't make a difference either... and before you ask... there are definitely customers without invoices in the system
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 04 Apr 2014 at 6:40am
do you have any other tables in the report?
Remove your select statement and place the name fom customer beside the id fom invoice and see if youa re gettin ght expected data set (all client records regardless of matching to invoice records).
Joined: 07 Jun 2010
Online Status: Offline
Posts: 40
Posted: 04 Apr 2014 at 7:21am
No, just have Customer & Invoice tables. I found out what the problem was, there was something else in the select statement but for some reason it didn't show up in the select expert unless if I went into the formula editor!!
Joined: 07 Jun 2010
Online Status: Offline
Posts: 40
Posted: 08 Apr 2014 at 5:46am
The only other thing with this report is that I need to add filters for invoice dates so that the report returns customers who have not had any invoices between those date filters. There are other filters for customer type, status etc but are all filtered against the customer table.
This is what I have the select expert: (isnull({invoice.invoiceNo}) or ({invoice.invoiceDate} < {?fromdate} or {invoice.invoiceDate} > {?todate})) and {customer.customerType} = 'New' and {customer.customerStatus} = 'Active'
Essentially I'm trying to get the report to return customers without any invoices at all or customers with invoices that do not fall between the time frame that I'm filtering for. This seems to work fine for customers without invoices but for those that have multiple invoices with some that fall within the {?fromdate} to {?todate} period, the report will pick up earlier invoices from these customers and therefore still show them on the report. How do I get them to not show up on the report at all?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
