Lookup Vs. Blank Parameter

[Rio38]

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:

   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:

 

[zee]

Try checking for an empty string?

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:

 

[it@flude.co.uk]

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.
​

 

[Rio38]

Try checking for an empty string?

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.

 

[doreynie]

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.

 

[zee]

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].

 

[Rio38]

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].

It was as simple as that Zee. Thank you very much. The simple things are often overlooked.