Lookup Vs. Blank Parameter

R

Rio38

New Member
I am working on a custom report that prompts the user for a range of locations, range of departments, allows the user to enter a list of "Hot" parts, a range of dates, etc.

My prblem is that if the user blanks the parts list parameter out, I would like the report to find all the parts matching the other criteria and ignore the part criteria. If the parts parameter is populated, I perform a lookup based on those values. I store the list in a temporary work table which I base a lookup on in the WHERE clause.

I am having problems with the logic to make the part work if the parts parameter is blank. I still get only the parts in the hot list.

The part in red works fine for the lookup when the parts list parameter is populated, but I need to add logic to find all parts if the parameter is blank.

Here is what I have so far:




Code: 
   FORM
        loc         COLON  20     loc1      COLON 45 LABEL {t001.i}
        dept        COLON  20     dept1     COLON 45 LABEL {t001.i}
        SKIP
        upartlist VIEW-AS EDITOR SIZE 50 BY 3
                    COLON  20                        label "Part List"
        SKIP
        "             Separate parts with a comma"
        SKIP
        ly-n        COLON  20                         label "Update List"
        edate       COLON  20     edate1    COLON 45 LABEL {t001.i}
        SKIP(1)
        excelfile   colon 20 label "Excel Output File"
        SKIP (1)
        SPACE (10)
        "NOTE: If Excel Ouput field is not blank then an Excel input file"
        SKIP SPACE (10)
        "      will be created."
       SKIP (1)
    WITH FRAME A SIDE-LABELS WIDTH 80.
    find usrw_wkfl where usrw_key1 eq "YIELDREPORT"
                         and usrw_key2 eq "CORE" NO-ERROR.
    if not avail usrw_wkfl then
    do: create usrw_wkfl.
            assign usrw_key1 = "YIELDREPORT"
            usrw_key2 = "CORE".
    end.
    upartlist = usrw_charfld[1].


UPDATE
                loc
                loc1
                dept
                dept1
                upartlist
                ly-n
                edate
                edate1
                excelfile
             WITH FRAME a.
     {mfselbpr.i "printer" 132}
     {mfphead.i}
     if ly-n then
    ASSIGN usrw_charfld[1] = upartlist.

 
FOR EACH op_hist NO-LOCK WHERE op_site >= loc AND
                                   op_site <= loc1 AND
                                   op_date >= edate and
                                   op_date <= edate1 and
                                   [COLOR=red]lookup(op_part, usrw_charfld[1]) <> 0[/COLOR] and
                                   op_dept >= dept and
                                   op_dept <= dept1 and
                                   op_type = "LABOR"
 BREAK BY op_site BY op_dept BY op_part BY op_date:
 
Try checking for an empty string?

Rio38 said:
Code: 
FOR EACH op_hist NO-LOCK WHERE op_site >= loc AND
                                   op_site <= loc1 AND
                                   op_date >= edate and
                                   op_date <= edate1 and
                                   [COLOR=red](lookup(op_part, upartlist) <> 0[/COLOR] [COLOR=Red]or [/COLOR][COLOR=red]upartlist[/COLOR][COLOR=Red] = "") [/COLOR]and
                                   op_dept >= dept and
                                   op_dept <= dept1 and
                                   op_type = "LABOR"
 BREAK BY op_site BY op_dept BY op_part BY op_date:
Click to expand...
 
If you use lookup or OR then it blows your index.

There are several ways of doing this.

Have you tried taking the logic out of the loop?

Something like:

FOR EACH op_hist NO-LOCK WHERE op_site >= loc AND
op_site <= loc1 AND
op_date >= edate and
op_date <= edate1 and
op_dept >= dept and
op_dept <= dept1 and
op_type = "LABOR"
BREAK BY op_site BY op_dept BY op_part BY op_date:

if upartlist <> "" and lookup(op_part, upartlist) = 0 then next.

but that might interfere with the BREAK BY.

Alternatively, try a conditional loop:

IF upartlist <> "" THEN FOR EACH op_hist NO-LOCK WHERE op_site >= loc AND
op_site <= loc1 AND
op_date >= edate and
op_date <= edate1 and
op_dept >= dept and
op_dept <= dept1 and
op_type = "LABOR"
BREAK BY op_site BY op_dept BY op_part BY op_date:
ELSE FOR EACH op_hist NO-LOCK WHERE op_site >= loc AND
op_site <= loc1 AND
op_date >= edate and
op_date <= edate1 and
op_dept >= dept and
op_dept <= dept1 and
op_type = "LABOR" and
(lookup(op_part, upartlist) <> 0 or upartlist = "")
BREAK BY op_site BY op_dept BY op_part BY op_date:

and include the procesing in an internal procedure.

Lastly, you could set up a temp-table containing the elements in upartlist and use something like

for each t_list:
delete t_list.
end.
if upartlist <> "" then do this_loop = 1 to num-entries(upartlist):
create t_list.
t_list.op_part = entry (this_loop,upartlist).
end.
else do:
/*Fill up t_list from a default list of parts */
end.


FOR EACH t_list no-lock, each op_hist NO-LOCK WHERE
op_site >= loc AND
op_site <= loc1 AND
op_date >= edate and
op_date <= edate1 and
op_dept >= dept and
op_dept <= dept1 and
op_type = "LABOR" and
op_part = t_list.op_part
BREAK BY op_site BY op_dept BY op_part BY op_date:

This shouldn't break the index, but might be slower when every part is being used, depending on the number of parts.

Personally, I don't use BREAK BY very often in reports, I tend to create a report temp-table, fill it up and then output it using a single BREAK-BY. That way, I can put my processing in and not worry about whether the BREAK BY is being broken by a NEXT command.

Horses for courses, though.
​
 
zee said:
Try checking for an empty string?
Click to expand...

That does not work for some reason. I need to select all parts if the field is left blank, but the saved work table could still have values in it. I want to ignore the work table if the field is left blank.
 
You should have something like this :

for each ...... no-lock
where (if logicalShouldCheckParts then table-name.parts-field = <value> else true)
and (if logicalShouldCheckDates then table-name.date-field = <value> else true)
and ..... :
do something with data.
end.
 
Irregardless of the method you choose, one thing you will need to make sure you're doing is using the variable upartlist rather than the field usrw_charfld[1].
 
zee said:
Irregardless of the method you choose, one thing you will need to make sure you're doing is using the variable upartlist rather than the field usrw_charfld[1].
Click to expand...

It was as simple as that Zee. Thank you very much. The simple things are often overlooked.
 
You must log in or register to reply here.
Back
Top