Double Reading a table.

[Chris Kelleher]

Hi Peggers.
MFG/Pro 8.6e progress 8.3a04

here we go. I am writting a new business report. This report consists of new items sold to a customer for during the current month. I have built a database that I connect to my production database that contains data for the customer and product, when they entered the program for the part and how long they will be monitored for. one stipulation is that th customer must not have bought the part within the past 18 months. The first thing I do is read the ih_hist and idh_hist to get the invoices for the month. Then I compare them to my new business file to see if they are already on the program. This part works fine. The next step is confusing me now. At this point, I need to go back and read the idh_hist for custoler/part that are not in the program to see if the part was purchased over the last 18 months. Can I go back and search through the idh_hist, or do I need to create a temp table to write my initial "good" records to? Can I just fo a find to see of a record exi!
sts in the 18 month period? Does anyone understand wat I'm asking, because I'm kinda doubtfull that I understand me. Please help, Thanks.


David L. Knowles
Database Administrator
Captive Plastics, Inc
Partners in Plastics Packaging

 

[Chris Kelleher]

David,

If I understand correctly, you'll read the ih_hist and ihd_hist files to look for sales during the current month, and then read them for the 18 months prior to the start of this month to make sure they haven't purchased the part(s). It seems to me that you effectively have to read the ih/idh combo twice. I'm not sure I understand why you want to keep a temp table with your "good" records, but I guess that would depend on your report's design. I'm guessing that you're trying to read the ih_hist/idh_hist files for the 18 month period within the loop that looks at ih_hist/idh_hist for this month and you're getting an error there. If this is the case, you can create a buffer for ih_hist/idh_hist to do the second read.

I don't know if this makes things clearer or if they muddle the water a bit more. If you send the basic structure of your program (just the repetitive loops) maybe we could help a bit more.

HTH


Carlos

 

[Chris Kelleher]

How about...

<BLOCKQUOTE><font size="1" face="Arial, Verdana">code:</font><HR><pre>
FOR FIRST ih_hist NO-LOCK WHERE ih_cust EQ vCustomer
AND ih_inv_date GE (TODAY - 540) ,
FIRST idh_hist NO-LOCK WHERE idh_inv_nbr EQ ih_inv_nbr
AND idh_part EQ vItem :
MESSAGE "Sorry, you don't qualify".
RETURN ERROR. /* or whatever */
END.
[/code]

David Takle
Software Consultant
Find First Consultant, Inc.

 

[Chris Kelleher]

You can use buffers and a temp-table. This code should, in fact,
do what you're asking (formatting a valid report is left to you):

<BLOCKQUOTE><font size="1" face="Arial, Verdana">code:</font><HR><pre>
def var bought_one as logical.

def buffer tt_ih for ih_hist.
def buffer tt_idh for idh_hist.

def temp-table cust-part
field cust like ih_hist.ih_cust
field part like idh_hist.idh_part
index cust-part cust part.

for each ih_hist no-lock where
ih_inv_date >= today - 30,
each idh_hist no-lock where
idh_nbr = ih_nbr and
idh_inv_nbr = ih_inv_nbr:
find first cust-part no-lock where
cust = ih_hist.ih_cust and
part = idh_hist.idh_part
no-error.
if available cust-part then next.
bought_one = false.
for each tt_idh no-lock where
tt_idh.idh_part = idh_hist.idh_part,
first tt_ih no-lock where
tt_ih.ih_inv_date >= today - 540 and
tt_ih.ih_inv_date < today - 30 and
tt_ih.ih_cust = ih_hist.ih_cust:
bought_one = true.
if bought_one then leave.
end.
if not bought_one
then do:
create cust-part.
assign
cust = ih_hist.ih_cust
part = idh_hist.idh_part.

display
ih_hist.ih_cust + " has not bought " idh_hist.idh_part
+
" in the last 18 months" format "x(60)"
with down frame xx.
end.
end.

/* End Program */
[/code]
Reid

 

[Chris Kelleher]

Hello,

I am not sure that I understand your question but it seems to me that it
is only a matter of programming technique. If you need double reading a
table, you could define a second buffer for idh_hist:

<BLOCKQUOTE><font size="1" face="Arial, Verdana">code:</font><HR><pre>
define buffer buff for idh_hist.

for each idh_hist where <criteria>:
find first buff where buff.idh_part = ...
if available(buff) then do:
display idh_hist.idh_part buff.idh_part.
<routine if second buffer found>
end.
end.
[/code]

But I don't know if there is any issues regarding the performance with
the above technique. Hope this helps.

regards,
Michael