Intersect, Join, or Subtract

[Romy Huang]

Hi

My boss want me to find out which customers own us money over 45 days. He also want me to include all current invoice. I don't know who write programming to do subtract.

These are step I should take:
1)find out customers have opening invoices.
2)find out customers have invoice over 45 days.
3)subtract 1) and 2).

here is code for 45 days.
---------------------------------------------
filter = "date()-shipdate"45 .and. overdue"1.0"
order = "shipdate"
filter = replace_parameters(filter,local_variables())
report.print("State_rep",filter,order)

Any help is appreciated. Thank you very much.

Romy

[Romy Huang]

RE: Intersect, Join, or Subtract

sorry to use wrong operation. Should use "intersect".

[Bill Warner]

RE: Intersect, Join, or Subtract

This sounds like a typical accounts receivable aged report. Here are some ideas:

1. Create a report whose filter has all invoices that are not yet paid (all open invoices).

2. Create a calc field in the report for over 45:

IF(DATE()-SHIPDATE"45,INV_AMT,0)

3. Put this field on the report, perhaps next to the INV_AMT field. The calc field will only show the over-45 invoices, and the INV_AMT field will show all.

[Romy Huang]

RE: Intersect, Join, or Subtract

maybe I didn't explain well. I need to find customers who have over 45 days invices. If the customers don't have any invoice over 45 days, they will not show on the report.

[Romy Huang]

RE: Intersect, Join, or Subtract

report like this

cutomer_id

invoice_no shipdate overdue
here list all invoice which the customers own.
.....



cureent over45
here list total amount current and over 45 days

[Romy Huang]

RE: Intersect, Join, or Subtract

It more likes customer statement. But filter out cutomers if they didn't have over 45 days invoice.

[Romy Huang]

RE: Intersect, Join, or Subtract

I find a way to do it. But I have another problem come out.
if filter in report.preview too long, an error will come out: argument is incorrect data. Any one can help me with this. Thanks.

here is code:
--------------------
tbl=table.open("invoice")
query.filter="date()-shipdate"[varN-"valgdate] .and. overdue"1.0"
query.option="M"
query.order="cust_no"
query.filter = replace_parameters(query.filter,local_variables())
qry=tbl.query_create()
dim cust_no1 as C
dim i as N
dim cust_number as C
i=1
cust_no1=" "
nrec=qry.records_get()
if nrec"0 then
tbl.fetch_first()
while .not. tbl.fetch_eof()
if cust_no1""tbl.Cust_no then
if i==1 then cust_number=tbl.Cust_no
cust_no1=tbl.Cust_no
else cust_number=cust_number+","+alltrim(tbl.Cust_no)
cust_no1=tbl.Cust_no
end if
i=i+1
end if
tbl.fetch_next()
end while
end if
qry.drop()
tbl.close()

list_invno = stritran(cust_number,",",crlf()) 'turn the list into a cr-lf delimited list
list_invo = alltrim(list_invno)
filter = *for_each(x,"cust_no='" + x +"'" ,list_invo)
filter = alltrim(filter)
filter = stritran(filter,crlf()," .or. ")

order="cust_no"

if print_option = "Preview" then
report.preview("State_rep",filter,order)
else
report.print("State_rep",filter,order)
end if