Re: Please help, Report Prob
- From: ShallowRacer <ShallowRacer@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 3 May 2006 18:46:01 -0700
Hi again,
here's another quick question.
How can i separated different items according to delivery address into
another con note but view in single page? do i have to use group_id? please
help.
many thanks
--
ShallowRacer
"Cindy Winegarden" wrote:
Hi ShallowRacer,.
First, a few tricks. your code :
if .not. used('product')
use product in 0
endif
select product
set order to prod_code
can be reduced to
Use Product in Select("Product") Order Prod_Code
There is no need to Select the table if you're going to open another one
right away in another work area.
Second, forget about relating tables. Select _ALL_ the data from the
appropriate tables exactly the way you want to see it in your report. Some
fields such as the address (since you're grouping products delivered to the
same address) will be repeated for several rows. In your report controls use
MyField only and not curI_ITEM.MyField. After the SQL runs the result is
automatically the current work area and the record pointer is positioned at
the top of the cursor (no need to Go Top).
In your SQL below you include the Customer table and it is joined to the
Invoice and Sales tables, yet you do not include any fields from the
Customer talbe in your Select clause. I assume the Customer table has the
address you want to send the items to, you will group on some Customer
field, and the address will show in the Group Header of the report.
-----------------------------------------------------------
select sales.inst_name, ;
sales.inst_st1, ;
sales.inst_st2, ;
sales.inst_sub, ;
sales.inst_state, ;
sales.inst_pc, ;
sales.cust_code, ;
sales.inst_spec, ;
i_item.inv_no, ;
i_item.unit_meas, ;
i_item.item_desc, ;
i_item.qty, ;
invoice.con_no, ;
invoice.con_date ;
from i_item, sales, customer, invoice ;
where invoice.inv_no = i_item.inv_no ;
and sales.sales_no = invoice.sales_no ;
and sales.cust_code = invoice.cust_code ;
and customer.cust_code = invoice.cust_code ;
and customer.cust_code = sales.cust_code ;
and empty(invoice.con_no) ;
and customer.transport = "GMK" ;
order by sales.inst_name, ;
sales.inst_st1, ;
sales.inst_st2, ;
sales.inst_sub, ;
sales.inst_state, ;
sales.inst_pc, ;
sales.cust_code ;
group by sales.inst_st1, ;
sales.inst_st2, ;
sales.inst_sub, ;
sales.inst_state, ;
sales.inst_pc, ;
sales.inst_name, ;
sales.cust_code ;
into cursor curI_ITEM
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@xxxxxxx www.cindywinegarden.com
"ShallowRacer" <ShallowRacer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9F21486C-0B50-401A-ACC2-55B8CF6F5710@xxxxxxxxxxxxxxxx
Hi there,
After trying out the report sometimes, i've successfully show the
delivery address and cust_code. Items are listed in the required area too,
however, the listed items are not listed accordingly to the delivery
address and cust_code. Kindly find below are my codes. FYI, i've created
a data grouping in the report and have the group expression of
invoice.inv_no = i_item.inv_no. Does it affect the items listed
incorrectly?
Should i create the data grouping?
-----------------------------------------------------------
select sales.inst_name, ; .....
As i mentioned before, i've to create the con_no and con_date once
the items are listed correctly accordingly to the cust_code and delivery
address, the con_no & con_date currently is empty in the table. I
would really appreciate if you can help me with this issue.
1. I don't see any relation between the invoice table and the customer
table.
2. you don't care about the customer info ?
select * from i_item where inv_no in (select inv_no from invoice where
empty(con_no) == .t. and deliv_add1 in (select deliv_add1 from invoice)
sort
by deliv_add1)
as a start ... then try isnull() as well.
3. then what about incrementing con_no for printing? ? i understand
consolidation, but suggest you assign the number PRIOR to printing it,
using
what ever business rule you use for consolidation. Usually a
consolidated
weigh bill has a unique number - its ok if you are using incremental #'s
for
that, but i suggest 2 more tables - one for the consolidation #'s - 1:1
and
another for storing invoice to consolidating #'s (many to many, store
keys
where cons_key can represent numerous invoice keys.) If you need further
help, contact me via email. [look for email addys in the sites below]
Am currently doing a consignment note.deliv_pc,
Use 4 major tables:
- customer(cust_code, transport)
- sales
- invoice(inv_no, deliv_add1, deliv_add2, deliv_sub, deliv_state,
con_no)the
- i_item(inv_no, unit, item_desc, qty)
Aim is to grab the items which has empty con_no and are going to the
same
deliv_add + display multiple results in single page and also
incrementing
con_no in the new page header.
- Follow-Ups:
- Re: Please help, Report Prob
- From: Cindy Winegarden
- Re: Please help, Report Prob
- References:
- Re: Please help, Report Prob
- From: swdev2
- Re: Please help, Report Prob
- From: ShallowRacer
- Re: Please help, Report Prob
- From: Cindy Winegarden
- Re: Please help, Report Prob
- Prev by Date: Re: Please help, Report Prob
- Next by Date: Re: Please help, Report Prob
- Previous by thread: Re: Please help, Report Prob
- Next by thread: Re: Please help, Report Prob
- Index(es):
Relevant Pages
|
