×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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.

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

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

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

(OP)
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!

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

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

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

(OP)
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

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

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

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

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

-LB

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

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

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

(OP)
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.

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

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},',')[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

(OP)
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.

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

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

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

(OP)
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}

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

(OP)
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!!!

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

I would change the warehouse parameter link, too.

-LB

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

(OP)
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.

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

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

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

(OP)
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!

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close