Alert user if data they entered into a parameter doesn't exist in the database 1

[cpjeffm]

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.

 

[tomcat24]

https://stackoverflow.com/questions...-crystal-report-to-lookup-a-record-in-a-table

Else display "Error Message

 

[lbass]

Create a command object in a new report. Set the command up like this, but adjusting it for the punctuation and syntax specific to your 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

 

[cpjeffm]

Thanks lbass but I've never dealt with commands before so I'm gonna need some additional help here.

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!

 

[lbass]

You haven't identified your database--please do that.

You could also provide a copy of the SQL query ("Show SQL Query).

-LB

 

[cpjeffm]

Here's the SQL query. Not sure what you mean by identify your database. It's a Cache database with an OCBC connection to it.

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

 

[lbass]

Is this SQL Query a copy from "Show SQL Query" in CR? I am not familiar with Cache, but ordinarily I would expect to see punctuation of some sort around database fields. I need to know this to be able to help with the command.

-LB

 

[lbass]

Also can you verify whether you have the report working with the pasted string and using the split function?

-LB

 

[cpjeffm]

That query is from Database --> Show SQL Query inside CR. I do have the report working with the pasted string and split function.

 

[cpjeffm]

Ok I have an idea. If I split the stock numbers (that were entered/pasted in the parameter separated by commas) into individual fields using multiple formulas like...

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

 

[lbass]

Create the following command in a separate report. Create a string parameter within the command and do NOT set it up for multiple values. Add the Inventory.Stock field to the detail section of the report.

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},',') 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},",") in y) and
not(split({?StockNum},",") in x) then
x := x + split({?StockNum},",")+","
);
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

 

[cpjeffm]

Ok that got me 99% there. I'm now seeing stock numbers that I entered in the parameters that aren't in the database. However, I'm now being prompted to enter my stock numbers twice in two different parameters...one from the main report and the other from the new subreport. I'm sure this is just a linking problem but I haven't figure it out yet.

 

[lbass]

In the main report, go to edit->subreport links, and move your parameter to the right and then in the lower left corner, you will see {?pm-?StockNum}--DO NOT USE this. Instead, use the dropdown located right there to select {?StockNum} instead.

-LB

 

[cpjeffm]

Still having the same problem. Here's how the subreport links are setup.

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}

 

[cpjeffm]

I got it. Had a brain fart. lbass...I don't know who you work for or how much they pay you but my friend, you deserve a raise!!! Kudos!!!

 

[lbass]

I would change the warehouse parameter link, too.

-LB

 

[cpjeffm]

Done. One last question. If I wanted to add the warehouse number in the {@not in db} formula so it reads: "Not Found in the Database for {Inventory.Warehouse}:" , is that much more complicated? If so, it's not a huge deal as I'm displaying the warehouse number from the parameter in the report header already.

 

[lbass]

Two things:

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

 

[cpjeffm]

Never mind....I just pulled the text out of the formula and put the text and the warehouse number in a separate text field and placed it above the formula in the report footer. Thanks again for the help!

 

[cpjeffm]

I pulled everything out of the subreport's selection expert per your instructions above.