Invoice / Customer Datasets question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Microsoft Newsserver - 14 Jul 2008 10:31 GMT

Hi Guys,

I am trying to create an invoice report. Essentially, there are two datasets
in use, the invoice set itself, with the main fields such as invoice number,
invoice items etc and the dsCustomer which contains the customer addresses.

I have the invoice details fine, but I want to be able to extract the
customer address details from the dsCustomer for each invoice in the
invoiceDetaisl Data Region and I cant work out how to do this. How can I
fill/filter/lookup List 1's fields
based on the customerID in List 2. Yeah, I could make one huge Datasetwith
all the details repeated a billion times, but there has to be an intelligent
way to do this. Am I just making life difficult for myself ? A different
approach ?

Any help is appreciated ! - Thanks

[***************** List1 - dsCustomer *******************]
[ CustomerID
[ CustomerAddress
[******************************************************]

[**************** List2 - dsInvoice ************************]
[ CustomerID
[ InvoiceNumber
[ Invoice Date
[
[ *************** List 3 Invoice Details ************
[
[*******************************************************]

Reply to this Message

toolman - 15 Jul 2008 17:14 GMT

> Hi Guys,
>
[quoted text clipped - 26 lines]
> [
> [*******************************************************]

Since the datasets have a common field (CustomerID) I'd probably do
this with a subreport.

First create a main report with a table attached to dsCustomer.
Insert CustomerAddress in the detail row and group by CustomerID.
Insert CustomerID into the group header and add a second detail row
below the first.

Now create a 2nd report (this will be your subreport) with a table
attached to dsInvoice. Insert InvoiceDate into the detail row and add
as many additional detail rows as needed to accomodate the invoice
details. Group by InvoiceNumber and insert InvoiceNumber into the
group header.

Back in your main report table, insert your subreport into the second
detail line.

You'll probably have to do some manipulating but this should get you
close.

HTH
toolman

Reply to this Message