Alert user if data they entered into a parameter doesn't exist in the database
Alert user if data they entered into a parameter doesn't exist in the database
(OP)
I'm building a report with a parameter for stock numbers. The parameter allows multiple custom values. If the user enters a bad stock# that doesn't exist in the database, I want to alert the user that the stock# they entered doesn't exist. Is this possible?
Building a dynamic parameter isn't an option as the field in the database contains thousands of records.
Building a dynamic parameter isn't an option as the field in the database contains thousands of records.
RE: Alert user if data they entered into a parameter doesn't exist in the database
Else display "Error Message"
RE: Alert user if data they entered into a parameter doesn't exist in the database
Create the parameter WITHIN the command object (on the right), and select multiple values.
select {?stockno} as StockNo,'Parm' as Type
from `table`
union
select `table`.`stockno`,'Stockno'
from `table`
where `table`.`stockno`={?stockno}
Next place the StockNo field in the detail section and insert a group on it. The Type field isn't needed except for any troubleshooting that may become necessary.
Then create a formula and place it in the group header and suppress the detail and all other sections:
if count({Command.StockNo},{Command.StockNo})=1 then
"No Match in Database"
Save this report as "Parameter Value Alert" and then insert it into your main report in the report header. Link the subreport to the main report on {?stockno} by using the dropdown in the lower left of the subreport linking expert to select {?stockno}, NOT the default {?pm-?stockno}.
This should generate a list of all selected parameters and also identify any values that do not exist in the database.
If you have any issues with implementing this solution, please identify your database, and clarify whether the stockno is a string or a number so we can troubleshoot this.
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
I created a new command object report, entered a parameter called StockNum and set it to multiple values. I then modified your formula as follows (StockNum is the parameter name, Inventory is the database and Stock is the field in the database:
select {?StockNum} as Stock,'Parm' as Type
from `Inventory`
union
select `Inventory`.`StockNum`,'Stock'
from `Inventory`
where `Inventory`.`Stock`={?StockNum}
However, when I click OK, I get the following error:
Failed to retrieve data from the database.
Details: 42000:[Cache ODBC][State : 42000][Native Code 1]
[C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.]
[SQLCODE: <-1>:<Invalid SQL statement>]
[Location: <Prepare>]
[%msg: < IDENTIFIER expected, ` found^select ( :%qpar(1) , :%qpar(2) ) as Stock , :%qpar(3) as Type from `>] [Database Vendor Code: 1 ]
Also, for the existing stock parameter, I'm not using a multi-value parameter because I want the ability to copy and paste a list of stock numbers, separated by a comma, into the parameter field and let the record selection split them out as follows:
totext({Inventory.Stock}) in split ({?StockNum}),",")
As a work around (not ideal as I would really like to alert the user as to which stock numbers didn't match between the parameter and the data), I'm simply counting the number of stock numbers entered in the parameter with a formula of:
whileprintingrecords;
stringVar array x;
x := split(left({?StockNum},100000),",");
count(x)
And then comparing it to the total stock numbers in the report with a formula of:
count({Inventory.Stock})
I have each count in the header and if the numbers don't match, I'm simply changing the text to red instead of black.
Now, with all this in mind, any further help would be greatly appreciated!
RE: Alert user if data they entered into a parameter doesn't exist in the database
You could also provide a copy of the SQL query ("Show SQL Query).
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
SELECT Inventory.Warehouse, Inventory.Stock, CustInfo.CompanyName, Inventory.DateEntered, Inventory.Description, Inventory.Location
FROM SQLUser.Inventory Inventory INNER JOIN SQLUser.CompanyInfo CompanyInfo ON Inventory.Warehouse=CustInfo.Warehouse
WHERE Inventory.Warehouse=134
ORDER BY Inventory.Stock
RE: Alert user if data they entered into a parameter doesn't exist in the database
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
RE: Alert user if data they entered into a parameter doesn't exist in the database
//Formula Name: StockNum_Entered_1
Dim x(1) As String
x = Split ({?StockNum}, ",")
formula = x(1)
//Formula Name: StockNum_Entered_2
Dim x(1) As String
x = Split ({?StockNum}, ",")
formula = x(2)
...and then somehow compare the StockNum_Entered_1 to one of the stock numbers returned on the report, that might work.
The only problem I see with this method is I have no idea how many stock numbers will be entered or pasted into the parameter so I won't know how many formulas I'll need to split them into.
RE: Alert user if data they entered into a parameter doesn't exist in the database
SELECT Inventory.Warehouse, Inventory.Stock
FROM SQLUser.Inventory Inventory
INNER JOIN SQLUser.CompanyInfo CompanyInfo ON
Inventory.Warehouse=CustInfo.Warehouse
WHERE Inventory.Warehouse=134 and
Inventory.Stock in ({?StockNum})
ORDER BY Inventory.Stock
Add a formula {@in db} to the detail section and suppress it:
whileprintingrecords;
stringvar y;
numbervar i;
for i := 1 to ubound(split({?StockNum},",")) do (
if
(
totext({Command.Stock},0,"") = split({?StockNum},',')[i] and
not(totext({Command.Stock},0,"")in y)
) then
y := y + totext({Command.Stock},0,"")+","
);
y
Add a formula {@not in db} to the report footer of the subreport:
whileprintingrecords;
stringvar y;
stringvar x;
numbervar i;
for i := 1 to ubound(split({?StockNum},",")) do(
if not(split({?StockNum},",")[i] in y) and
not(split({?StockNum},",")[i] in x) then
x := x + split({?StockNum},",")[i]+","
);
if len(x)>1 then
"Not Found in Database:" +" "+
left(x,len(x)-1) else
"Not Found in Database:" +" "+x;
Then save the report and insert it as a subreport in the report header of your original report. Link the subreport as explained before, using the dropdown to select {?StockNum} as a linking field. If the warehouse number is a parameter in your main report, create the same parameter within the command and replace 134 with that. If it is a string parameter, put single quotes around the parameter like this '{?Warehouse}', but do NOT do this for {?StockNum} because you are entering an array, not one value. Then also link the subreport on the warehouse parameter, again using the dropdown.
Using the subreport allows you to display selected parameter values and the alert about values in the report header of your main report.
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
RE: Alert user if data they entered into a parameter doesn't exist in the database
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
In the main report, I have two parameters: {?Warehouse} and {?Stock Numbers}. If I go to Change Subreport Links, under the fields to link to (on the right), I have both of those parameters included.
At the bottom for ?Warehouse...on the left, I have ?Pm-?Warehouse selected and on the right, the "Select data in subreport based on field:" is checked and I have Command.Warehouse selected.
At the bottom for ?Stock Numbers...on the left, I selected the empty cell in the dropdown and on the right, the "Select data in subreport based on field:" is checked and I have Command.Stock selected.
The select expect formula in the subreport is:
{Command.Warehouse} = {?Pm-?Warehouse} and
{Command.Stock} = {?Stock Numbers}
RE: Alert user if data they entered into a parameter doesn't exist in the database
RE: Alert user if data they entered into a parameter doesn't exist in the database
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
RE: Alert user if data they entered into a parameter doesn't exist in the database
1-Yes, as long as the warehouse is a singlevalue parameter, you can just change the string to "Not Found in Database for "+totext({?Warehouse},0,"")+":" in both places.
2-If you used a command in the subreport, you should not be seeing anything in the selection expert that references the link, since you would have built the parameters right into the command--creating the parameters within the command screen and then referencing them in the command itself. You should remove the link references in the selection expert.
-LB
RE: Alert user if data they entered into a parameter doesn't exist in the database
RE: Alert user if data they entered into a parameter doesn't exist in the database