When something is not true

P

Patrick659

New Member
Hello -

Our inventory management system (IMS) stores items in "trays". When an item is requested, a "history" record is created for that item. What we're trying to do is identify trays whose items have never been requested. Since the IMS doesn't have any such reporting, we're trying various one-off scripts in the dev environment. While this initially seemed relatively simple, the scripts so far have been doing the exact opposite - showing only trays with related requests or all trays of a given category.

The relevant code looks more or less like this:
Code: 
 for each tray no-lock where tf-own = l-cus and tf-type >= l-type1 and
tf-type <= l-type2 and tf-delete-date = ?,
  each item no-lock where if-tray = tf-tray and if-delete-date = ?
               break by tf-tray:
                if last-of (tf-tray) then
             find first hist no-lock where hi-itm = if-item-no no-error.
              if not available (hist) then
              if last-of (tf-tray) then
                disp tf-tray.
        end.
(As I often say, - what am I missing?)

Any ideas would be much appreciated ....

- pat O'B
 
Last edited by a moderator:
try
Code: 
for each tray no-lock where tf-own = l-cus and tf-type >= l-type1 and
tf-type <= l-type2 and tf-delete-date = ?,
each item no-lock where if-tray = tf-tray and if-delete-date = ?
break by tf-tray:

if last-of (tf-tray) then do:
   find first hist no-lock where hi-itm = if-item-no no-error.
   if not available (hist) then do:
      disp tf-tray.
   end.
 end.
end.
 
Last edited by a moderator:
For the sake of people you want to read and review your code, please enclose code in [ code ] tags and indent appropriately.

E.g.:
Code: 
for each tray no-lock where tf-own         =  l-cus 
                        and tf-type        >= l-type1 
                        and tf-type        <= l-type2 
                        and tf-delete-date = ?,
  each item no-lock where if-tray        = tf-tray 
                      and if-delete-date = ?
  break by tf-tray:

  if last-of (tf-tray) then
    find first hist no-lock where hi-itm = if-item-no no-error.

  if not available (hist) then
    if last-of (tf-tray) then
      disp tf-tray.
end.
 
Rob Fitzpatrick said:
For the sake of people you want to read and review your code, please enclose code in [ code ] tags and indent appropriately.

E.g.:
Code: 
for each tray no-lock where tf-own         =  l-cus
                        and tf-type        >= l-type1
                        and tf-type        <= l-type2
                        and tf-delete-date = ?,
  each item no-lock where if-tray        = tf-tray
                      and if-delete-date = ?
  break by tf-tray:

  if last-of (tf-tray) then
    find first hist no-lock where hi-itm = if-item-no no-error.

  if not available (hist) then
    if last-of (tf-tray) then
      disp tf-tray.
end.
Click to expand...
I've added the code tags, but I'm not sorting the indents ;)
 
My inclination would be:

Code: 
if last-of (tf-tray) then do:
    find first hist no-lock where hi-itm = if-item-no no-error.
    if not available (hist) then disp tf-tray.
end.
 
Thanks all -

OpenEdge 12.2.17
linux redhat7

Unfortunately, none of the foregoing worked - still keeps reporting trays with request history. However, some more knocking around seems to at least omit such trays. But now it seems to be stopping short. For example, where we know of 45 trays within which 2 have history, it should be reporting all of the other 43, but stops at around a dozen or so. Also curiously, if we expand the range, it still quits with that first ~dozen.

Here's what the code looks like now:
Code: 
  for each tray no-lock where tf-own = l-cus and tf-type >= l-type1 and
 tf-type <= l-type2  and tf-delete-date = ?,
 each item no-lock where if-tray = tf-tray and if-delete-date = ?
 break by tf-type by tf-tray:

              find first hist no-lock where hi-itm = if-item-no no-error.
              if available (hist) then assign l-hist-count = l-hist-count + 1.
              if l-hist-count = 0  then do:
              if last-of (tf-tray) then
              disp tf-own tf-tray l-hist-count label "HIST".
      end.
    end.
 
Last edited by a moderator:
Hate to be a nag, but CODE tags are like this:
[ code ]
// your code goes here
[/ code ]

Remove the spaces before and after the word "code" in the start and end tags. You can use the Preview button in the top right corner of the post editor to see the effect of the tags before you post your replies.
 
Rob Fitzpatrick said:
Hate to be a nag, but CODE tags are like this:
[ code ]
// your code goes here
[/ code ]

Remove the spaces before and after the word "code" in the start and end tags. You can use the Preview button in the top right corner of the post editor to see the effect of the tags before you post your replies.
Click to expand...
Code tags fixed :)
 
Try this

Code: 
FOR EACH tray NO-LOCK 
  WHERE tray.tf-own          = l-cus
    AND tray.tf-type        >= l-type1
    AND tray.tf-type        <= l-type2 
    AND tray.tf-delete-date  = ?
  ,
   EACH item NO-LOCK 
  WHERE item.if-tray        = tray.tf-tray
    AND item.if-delete-date = ?

  BREAK BY tray.tf-type
        BY tray.tf-tray:

  IF FIRST-OF(tray.tf-tray) THEN l-hist-count = 0.

  IF CAN-FIND(FIRST hist WHERE hist.hi-itm = item.if-item-no) THEN
    l-hist-count = l-hist-count + 1.

  IF LAST-OF (tf-tray) AND l-hist-count = 0 THEN
    DISPLAY
      tray.tf-own
      tray.tf-tray
      l-hist-count.
END.

(and what's wrong with prefixing the fields with the tablename? I really hate this MFG-disease)
 
Alternative approach:

Code: 
#Tray:
FOR EACH tray NO-LOCK 
  WHERE tray.tf-own          = l-cus
    AND tray.tf-type        >= l-type1
    AND tray.tf-type        <= l-type2 
    AND tray.tf-delete-date  = ?:

  FOR EACH item NO-LOCK 
    WHERE item.if-tray        = tray.tf-tray
      AND item.if-delete-date = ?
    ,
    FIRST hist NO-LOCK
    WHERE hist.hi-itm = item.if-item-no:
   
    NEXT #Tray.
  END.

  DISPLAY
    tray.tf-own
    tray.tf-tray.

END.
 
Many thanks Patrick T.! - both work very well. We're also able to add other conditions to the ANDs, and they continue to work well. Right now we're scaling up testing (e.g. "tray" population of 21,000), and so far so good.

Thanks again and best to all for your help!
 
You must log in or register to reply here.
Back
Top