PDA

View Full Version : Dialog Builder Syntax ?


ABC123

seidel1
09-28-2004, 01:49 PM
We are working with the Dialog Builder to create some menu based reports. We have come up against a wall, and we can't find any documentation to walk us around the problems:

1) We are basing our report on a Set, called "Site". In the Set we have 3 tables:
Site -
Property
Client

We are finding that we cannot filter based on a field or a variable that is in Property or Client. Yet this filtering works fine without any hitches in regular A5

2) We have been able to get some of our filtering to work. But now we want to create multiple filters in 2 different flavors;

a) How do we filter on variable1 "and" variable2 without using the .and. function. It seems the WAS will not accept .and.

b) How do we combine a filter with a logical. i.e.

Filter = "parea_list ="+s_quote(convert_type(db1,"C")) "- This filter works, but only if the report is in the property set, but will not work in the site set (where we need it). Note parea_list is a field in property.

Now we want to add an additional logical -" edctodo = .t. which is the syntax in regular A5. Note EDCtodo is also a field in the property table. This code can be hard coded in the filter string rather than making it a selection at runtime.

Keith Hubert
09-30-2004, 12:35 PM
Hi Jim,

Have you had a look at the Dialog_wCascadingLookup.a5wcmp example?

Just a thought?

Regards

Keith Hubert

Guild Member
London.

Sarah Mengesha
11-18-2004, 07:46 AM
Hi Jim,

Are you filtering from an ADO database? I had the same problem where the dialog builder filter would not accept .and. and when I looked at the xbasic I saw that the filter was an SQL statement where you have to use "AND" between two filter variables. My solution to this was to write my own redirection code in the aftervalidate editor in the dialog component. You have to first establish a connection to the ADO you are using (if this is the case) and then specify the record set with the SQL filter. I believe Selwyn had an detailed response to this problem as well. I will try and find the post.

Also remember that with SQL characters are denoted with single (not double!) quotes. This caused me a big headache.

Sarah

seidel1
11-18-2004, 07:48 AM
Thanks for the response. Currently we are accessing an A5 DB, however we are expanding our WAS implementation and will be interacting with and SQL DB. So if you find the reply, please send me in the right direction. Thanks again

Sarah Mengesha
11-18-2004, 08:49 AM
Hi Jim,

Here is the relative part of Selwyn's reply concering accessing an ADO database.

Here Selwyn uses an Access database as an example, just replace the connection string specifying the SQL ODBC with your own connection string and your good to go.


Selwyn wrote:

Let's assume that you have an Access MDB file
called c:\data\users_names.MDB.


Assume that this database contains a table called
"UserID_Password", and that the table contains fields "UserID" and "Password".

The dialog component has a field called 'user_name'
that is entered by the user.

You will need to use ADO to get the data out of
the User_names.MDB file.

Here is the code you can use:

dim cs as c

cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\user_names.MDB;Persist
Security Info=False"

'DIM an object that is an 'ADO Connection'

dim cn as ole::adodb.connection

'DIM an object that is an 'ADO Recordset'

dim rs as ole::adodb.recordset

'Specify the SQL query to get the password for the specified User_name

dim sqltxt as c

sqltxt = "select Password from UserID_Password where UserID = '" + user_name +
"'"

'Open the connection to the Access Database

cn.open(cs)

'Execute the query, creating a recordset which holds the results

rs.open(sqltxt,cn)

if rs.eof = .t. then

correct_password = ""

else

correct_password = rs.fields.item(0).value

'note: you could also have said: correct_password =
rs.fields.item("password").value

end if

'Close the connection to the Database

cn.close()

Lenny Forziati
11-18-2004, 10:00 AM
1) What is the specific filter you are trying to use. You should be able to use any filter expression that works in Alpha Five itself.

2a) .and. is perfectly valid in the Web Application Server. What leads you to belive that it is not?

2b) First, you should not use s_quote() or quote() in a web app unless you are absolutely sure that the data being quoted will never contain a " character or a crlf. If the data does, these functions will quote the data using a syntax that is specific to Xbasic and it will not work in your browser.

Other than taht, the same Alpha Five syntax applies, so you would use ".and. edctodo = .t."


-Lenny