Need help in filtering the data with various condition and filling in scroll window GP
- by Rahul
Hi all,
I am filtering the data and displaying in scroll window. There are many combination to display this data by customer id, customer id and itemnumber, customer id, itemnumber, work and history condition. And from date and To date condition. My query is when I am selecting the customer id and work or history table it should display the corresponding data. Like select * from price history where customerid=’custid’ and name=’Work’. It should display in scroll only these values none other it the same way history condition should work. Work and History are in check box. In my case whatever range I am selecting whether Work and History always loading with entire data, so it’s not filtering properly.
My second problem is if I select from date and keep empty to date …in this case all the data should display from selected from date to end of table data. But I am not getting….pls somebody help me here is my entire coding:
if empty('Customer Number') then
warning "Select Customer ID";
focus 'Customer Number';
abort script;
end if;
if '(L) RadioGroup4' of window Window1 of form 'Customer Pricing Inquiry'=1 then
if empty(Date) then
abort script;
focus Date;
end if;
end if;
if not empty('(L) Date') then
if '(L) Date'
{if not empty(Date) and empty('(L) Date') then
warning"Please enter To Date";
focus field '(L) Date';
abort script;
end if;}
range clear table Display_Pricing_Temp;
clear field 'Customer Number' of table Display_Pricing_Temp;
range start table Display_Pricing_Temp;
fill field 'Customer Number' of table Display_Pricing_Temp;
range end table Display_Pricing_Temp;
remove range table Display_Pricing_Temp;
range clear table Display_Pricing;
if '(L) Checkbox0' =true and '(L) Checkbox2'=true and empty('Item Code' of window Window1 of form 'Customer Pricing Inquiry')
and str(Date of window Window1 of form 'Customer Pricing Inquiry')="0/0/0000" then
{range clear table Display_Pricing;}
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and ("+
physicalname(Name of table Display_Pricing)+ "='History' or "+
physicalname(Name of table Display_Pricing)+ "='Work')";
{range clear table Display_Pricing;}
end if;
if '(L) Checkbox0' =true or '(L) Checkbox2'=true then
{{Only Item No is there}
if not empty('Item Code') and '(L) Checkbox0' =false and '(L) Checkbox2'=false and str('(L) Date')="0/0/0000" then
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"'";
end if;
}
if empty('(L) Date') and not empty(Date) then
{date work hist item}
if not empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and ("+
physicalname(Name of table Display_Pricing)+ "='Work' or " +physicalname(Name of table Display_Pricing)+ "='History')and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102)" ;
range clear table Display_Pricing;
end if;
{date work hist }
if empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and ("+
physicalname(Name of table Display_Pricing)+ "='Work' or " +physicalname(Name of table Display_Pricing)+ "='History')and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102)" ;
range clear table Display_Pricing;
end if;
{date,work,item code}
if not empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=false and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and "+
physicalname(Name of table Display_Pricing)+ "='Work' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102)" ;
range clear table Display_Pricing;
end if;
{date history item code}
if not empty('Item Code') and '(L) Checkbox0' =false and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and "+
physicalname(Name of table Display_Pricing)+ "='History' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102)" ;
range clear table Display_Pricing;
end if;
{date,work}
if empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=false and not empty(Date) then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname(Name of table Display_Pricing)+ "='Work' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102) ";
range clear table Display_Pricing;
end if;
{date history }
if empty('Item Code') and '(L) Checkbox0' =false and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname(Name of table Display_Pricing)+ "='History' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102)" ;
range clear table Display_Pricing;
end if;
end if;
if not empty('(L) Date') and not empty(Date) then
{Only Item No is there and work}
if not empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=false and str(Date)="0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and "+
physicalname(Name of table Display_Pricing)+ "='Work'";
range clear table Display_Pricing;
end if;
if not empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=true and str(Date)="0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and ("+
physicalname(Name of table Display_Pricing)+ "='Work' or "
+physicalname(Name of table Display_Pricing)+ "='History')";
range clear table Display_Pricing;
end if;
{date work hist item}
if not empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and ("+
physicalname(Name of table Display_Pricing)+ "='Work' or " +physicalname(Name of table Display_Pricing)+ "='History')and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) between convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102) and convert(datetime,convert(varchar(20),'"+ str('(L) Date' of window Window1 of form 'Customer Pricing Inquiry') +"'),102)";
range clear table Display_Pricing;
end if;
{date work hist }
if empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and ("+
physicalname(Name of table Display_Pricing)+ "='Work' or " +physicalname(Name of table Display_Pricing)+ "='History')and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) between convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102) and convert(datetime,convert(varchar(20),'"+ str('(L) Date' of window Window1 of form 'Customer Pricing Inquiry') +"'),102)";
range clear table Display_Pricing;
end if;
{date,work,item code}
if not empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=false and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and "+
physicalname(Name of table Display_Pricing)+ "='Work' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) between convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102) and convert(datetime,convert(varchar(20),'"+ str('(L) Date' of window Window1 of form 'Customer Pricing Inquiry') +"'),102)";
range clear table Display_Pricing;
end if;
{date history item code}
if not empty('Item Code') and '(L) Checkbox0' =false and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and "+
physicalname(Name of table Display_Pricing)+ "='History' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) between convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102) and convert(datetime,convert(varchar(20),'"+ str('(L) Date' of window Window1 of form 'Customer Pricing Inquiry') +"'),102)";
range clear table Display_Pricing;
end if;
{date work}
{date,work}
if empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=false and not empty(Date) then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname(Name of table Display_Pricing)+ "='Work' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) between convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102) and convert(datetime,convert(varchar(20),'"+ str('(L) Date' of window Window1 of form 'Customer Pricing Inquiry') +"'),102)";
range clear table Display_Pricing;
end if;
{date history }
if empty('Item Code') and '(L) Checkbox0' =false and '(L) Checkbox2'=true and str(Date)<"0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname(Name of table Display_Pricing)+ "='History' and convert(datetime,convert(varchar(20),"
+physicalname(Date of table Display_Pricing)+"),102) between convert(datetime,convert(varchar(20),'"+ str(Date of window Window1 of form 'Customer Pricing Inquiry')+
"'),102) and convert(datetime,convert(varchar(20),'"+ str('(L) Date' of window Window1 of form 'Customer Pricing Inquiry') +"'),102)";
range clear table Display_Pricing;
end if;
end if;
{Only Item No is there and hist}
if not empty('Item Code') and '(L) Checkbox0' =false and '(L) Checkbox2'=true and str(Date)="0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname('Item Number' of table Display_Pricing)+ "='"+ str('Item Code')+"' and "+
physicalname(Name of table Display_Pricing)+ "='History'";
range clear table Display_Pricing;
end if;
{for only work table }
if empty('Item Code') and '(L) Checkbox0' =true and '(L) Checkbox2'=false and str(Date)="0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname(Name of table Display_Pricing)+ "='Work'";
range clear table Display_Pricing;
end if;
{for only hist table }
if empty('Item Code') and '(L) Checkbox0' =false and '(L) Checkbox2'=true and str(Date)="0/0/0000" then
range clear table Display_Pricing;
range table Display_Pricing where
physicalname('Customer Number' of table Display_Pricing) + "= '" +
str('Customer Number' of window Window1) + "' and "+
physicalname(Name of table Display_Pricing)+ "='History'";
range clear table Display_Pricing;
end if;
get first table Display_Pricing;
if err() = OKAY then
repeat
copy from table Display_Pricing to table Display_Pricing_Temp;
save table Display_Pricing_Temp;
get next table Display_Pricing;
until err() = EOF;
else
clear window Price_Scroll of form 'Customer Pricing Inquiry';
end if;
else
clear window Price_Scroll of form 'Customer Pricing Inquiry';
end if;
fill window Price_Scroll table Display_Pricing_Temp by number 1;
