Statement-Print Store Location then Invoices
Hi all,
I've created an A/R Statement that works just fine. But, request has
been made to add the store location for each invoice. That's easy enough to
do but don't want to repeat the same store location for multiple invoices.
Here's the way the report prints now for the detail band.
STORE LOCATION
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
STORE LOCATION
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
etc.
The problem is that if the Store Location is the same for multiple
invoices, don't want to keep printing it again, just print it if it changes,
i.e. this is the way I would like to make it print:
STORE LOCATION A
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
STORE LOCATION B
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
STORE LOCATION C
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
etc.
I designed the report with subreport which contains the invoices info.:
date, invce#, amt., paymt, bal. The subreport prints in the detail band.
The report is grouped by customer and store locations table is related to
the customers table in a many-to-one relation, i.e. one customer my have 4
locations.
Basically, if I could do some kind of comparison to see if the store
location changed from one invoice to another, then print the store location
else don't print it if it's the same as the previous invoice.
Tks any help,
Don
I've created an A/R Statement that works just fine. But, request has
been made to add the store location for each invoice. That's easy enough to
do but don't want to repeat the same store location for multiple invoices.
Here's the way the report prints now for the detail band.
STORE LOCATION
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
STORE LOCATION
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
etc.
The problem is that if the Store Location is the same for multiple
invoices, don't want to keep printing it again, just print it if it changes,
i.e. this is the way I would like to make it print:
STORE LOCATION A
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
STORE LOCATION B
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
STORE LOCATION C
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
DATE INVOICE# INVOICE AMT. PAYMENT BALANCE
etc.
I designed the report with subreport which contains the invoices info.:
date, invce#, amt., paymt, bal. The subreport prints in the detail band.
The report is grouped by customer and store locations table is related to
the customers table in a many-to-one relation, i.e. one customer my have 4
locations.
Basically, if I could do some kind of comparison to see if the store
location changed from one invoice to another, then print the store location
else don't print it if it's the same as the previous invoice.
Tks any help,
Don
This discussion has been closed.
Comments
If you detail dataset is ordered by customer, then store location, you could
simply place a group inside your detail subreport on store location and
place the store location in the group header. This would only print the
store location when the group breaks (changes).
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
This is actually the way the tables are linked for this report:
CUSTOMERS
- INVOICES
- SALESORDERS_TRANS_HDR
-SHIPPING_ADDRS (Store Location)
I think I get the gest of what you're saying. I tried creating another
subreport in the Detail band and called it subRptStoreLoc. That didn't
work, just printed the first Store Loc. I think what your are saying is in
the Detail Band I have a SubReport called subRptInvces. I need to click on
the subRptInvces tab and where I have the fields defined, make room above
the dbTxt fields and place a variable where the Value:=
SHIPPING_ADDRS['ADD1'] + ' '+SHIPPING_ADDRS['ADD2'}+etc, call it
varStoreLoc. Then, set a Group on SHIPPING_ADDRS set on custom field
variable varStoreLoc. I'll try that. The way the tables are linked in the
database (FireBird), Invoices link to Customers via Cust_ID, SO_Tran_Hdr
linked to Invoices via Invce_ID and Shipping_Addrs linked to SO_Tran_Hdr via
Cust_Ship_ID. I'll play with this and see if that get's the behavior I'm
looking for.
Tks,
Don
What you said makes sense but problem. I drilled down to the
subRptInvces tab and added a variable which value= Store Address
concatenated together. No way to assign a group within the subreport to
Shipping_Addrs. Only tables available are INVOICES->fieldname. I tried
adding a dbTxtfield: table- Shipping_Addrs, Field-Cust_Ship_ID and made it
invisible and set a Group Custom Field on the dbTxtCust_Ship_ID field.
Then, only the variable containing the Store Address showed. The behavior
is as if no group at all is set. I get this:
Store Loc. A
INV#5
Store Loc. A
INV#6
Store Loc. B
INV#7
What I want is:
Store Loc. A
INV#5
INV#6
Store Loc. B
INV#7
I'll do some more experimenting, maybe something I just not seeing.
Tks,
Don
I'm a bit confused about how your data is arranged. Perhaps if you give me
a simple example of what your tables/queries look like before you generate
the report will help me come up with a solution for you. Currently this is
what I envisioned...
Master Table...
CustID Name
01 Mick
02 Keith
03 Ron
04 Charlie
Detail Table...
Invoice# CustID StoreLocation
100 01 01
200 01 01
300 01 02
400 02 02
500 03 01
600 03 02
700 04 02
Linked on CustID with the main report linked to the master and the subreport
linked to the detail. The detail table ordered by CustID and StoreLocation
so that when the subreport is grouped on the StoreLocation, only one value
is shown in the group header.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Okay, I think I get what you are getting ready to tell me. I think I
need to change my data setup and link the shipping addresses to the invoices
table. Currently, this is the way the data is setup:
Customers(CustID, CustCode) - Master/Header Table
1 K88210 KFC
2 W88210 WENDY'S
Invoices(InvceNo, CustID, ShipCode)
10034 1 K82134
20055 1 K82134
30033 1 K82135
40021 2 W88210
SOTransHdr(InvceID, InvceNo, CustShipID)
1 10034 5
ShipAddreses(CustShipID, CustID, ShipCode)
5 1 K82134
So, think I need to change the link of ShipAddreses to link to Invoices
via the ShipCode field. Currently, ShipAddreses is linked to the SOTransHdr
via the CustShipID.
I'll try that.
Tks,
Don