Digital Workshop

I initially responded to a question in the basics section - but I think it needs to be in the advanced....
I am working on a new project that draws data from an excel or access database.

For some reason - I couldn't get the excel database to be "seen" when setting up the DSN - Microsoft Excel Driver
But the Access one is working fine - so I am using that for now.

I have replicated the sample pub - drawing information from the database and displaying images using variables etc.

However - I want to be able to provide a "search" box to list records based on one of the fields in the database.
Does anyone have any experience in doing this.
I am now reading through the Opus Script help - but was hoping someone may have already done this?

Appreciate any help/advice
regards
Pete

Regards

Pete

In the hope that this might help others (as well as getting help myself) I will try and document my progress.

I have now got the search kind of working by adding a text input box where the text entered is saved to a new variable called NAME
Then added a submit button with a database action query:
Using the SQL Wizard - I chose a field from the database in the "where field" drop down box.
Operators - I have tried to select "Is LIKE" because I just want to match a name from the search box and compare it to the NAME variable that I created.

However - unless I type the full information into the the search box (so that it matches exactly with the name field from the database e.g Jones, R as opposed to just Jones) - it will not find and display what I have searched for.

When I go back to examine the Database Query using the SQL Wizard - it seems to keep reverting to the Operator "Is Equal To" - Is that normal???

The next problem is - Assuming I have more than one Jones - I want to be able to click "next" to view the other Jones's... but the Submit button obviously keeps returning me to just the first one that it finds....

Again,
Thanks in advance
Regards
Pete

Just remembered that there is a series of tutorials in the .... guess where... tutorials sections of the forum I will work through these now too!!
Regards
Pete

OK - thanks to the wonderful Mr Garrett - Have got the Search function working to show all my Jones records ... but still have an issue where I have to have the exact contents of the Name field entered into the search box.

i.e the "like" appears not to work - even though the SQL wizard shows that I appear to have like selected:

SELECT * FROM `98DATABASE`
WHERE `Names` LIKE <NAME>'


Ideas???

Has this part of the problem been solved, or morphed...? I thought I read previously your last post and that was part of the issue (maybe you Edited your last post, or am I going crazy?).

Is this 'SQL Wizard' within Opus, or somewhere else? I seem to remember an entry area in Opus standard Actions for some SQL expression or construction -- if so, does it really allow/facilitate designating an Opus variable to match (eg., <Name>)?

I would guess about a couple possible outcomes or obstacles.
One is that multiple rows are generated by the SQL query, but that only one row or only one data element is handled by Opus (eg., the first or the last).
The other is that quote-marks become part of the problem -- my thinking is more aimed at a statement that is scripted and may not apply to your Pub... so requiring a combination of single- and double- quote-marks. Also, in scripting, the Opus variable name is appended or included with a '+' operator.

Hi - and thanks for your post...

Yes - sorry I did edit my original post, having gleaned from the tutorial how to get the next and previous button to use the same query as the search query.
You obviously read it in the hour that I discovered the solution.

The second question you were asking re


I used the instructions from Robin Garretts tutorial
http://www.digitalgrapevine.info/viewtopic.php?f=13&t=295
Specifically these steps:

19) Click on SQL Wizard and remove the tick from the Select Data for Updating/Deleting (you can experiment with this later) and click Next >
20) From the table drop-down list, select the table in which your required data resides, ensure that all of the field names are ticked, then click Next >
21) This page allows you to apply SQL operators to filter the data. Set the "Where Field" setting to the first field in your database (NAME)
22) Select IS LIKE as the operator
23) Set the Compare To option to "Variable", then select SEARCH from the drop-down menu

But as I said - it will only succeed in returning a result - if I type in EVERYTHING that is in the name field - i.e "Jones, R" as opposed to just "Jones"

So - I still need to resolve this - or manually extract the surname only from the database records and create and insert into a new field ????

Regards

Pete

In google-ing, checking SQL in general, it seems that a WHERE xyz-field IS LIKE 'somestring%' part of the statement needs to have a Wildcard ( %, _ ) or two in it. ??

If that's true, question is: How does DW / Opus intend you to do that?
I suppose you need do something 'like':

Thanks again for your response.

As I used the Opus SQL wizard for this - I think it would be good to get a response from DW?

IF I dont see a response over the weekend, I will email them directly. Be good to get a solution that benefits everyone however,

Regards

Pete

In the meantime you can try, with standard Actions to 'tweak' your name-selecting variable to include the wildcard character. (actions >>> set Variable).

Hi Pete,

Lar's right on both accounts. You need to use a wild card in your search term for the SQL statement to return LIKE matches. This is standard.

You can either get the end user to enter the search term with the wild cards or you can automate it when you click the button. IE

1. There is a input box that stores the value in a variable called 'mySearch'

2. There is a button that runs the Database Query Action.

3. When the button at 2 is pressed - it copies the value from 'mySearch' and concatenates it with the wild card into a new variable called 'myLikeSearch'. It is this variable that is used by the database query. Something like:

myLikeSearch = "%"+mySearch+"%"

If I enter 'a' into the search box the value passed to the Database Action would look like this %a%

This would find the following names:

abbey
paul
david

But not:

sue
lucy
fred

It really depends on your end user. Personally, I prefer to use the LIKE statement but have the user manually enter the wild card as it gives them more power of the search.

Mack

Hi guys,

Many thanks - that makes great sense and I will give it a go.
However, it appears that the SQL wizard is not doing this automatically - so I will probably also contact DW so that they are aware of the problem.

Will report back on my findings...

Thanks again. As always - you guys are the best

Regards

Pete

Hi Mack,

Can I just ask you to clarify the code for me again

I am assuming I add a script action to the button - but I dont think I am putting the right code in

Regards

Pete

Another general question - as I am using an Access database, does Access have to be loaded on the playback PC?

Regards

Pete

Hey Pete,

Here we are in the No-Hem, moving into the depth of winter. And there you are soaking up all the sunshine.

I think the answer is 'No'. The client machine does not have to have the full Application Program installed (in order for Opus to use a .mdb file... Access DB file). I think if you searched the forum you would find some kind of confirmation of that. Ditto for Excel.

I'll let Mackavi come back and work on that, but to keep you moving on it consider it this way. You ask the User to input a partial search string. [Mack: "...stores the value in a variable called 'mySearch'"]
You take that and add a Wildcard at the end (assuming you want the SQL 'LIKE' operation to find with matches beginning with that partial string). Of course you can have wildcards before, after, or both... also within. etc etc. [Mack: "...concatenates 'mySearch' with the wild card character '%' into a new variable called 'myLikeSearch'.] e.g, 'jones' input variable becomes 'jones%' as the variable SQL sees or uses.
You use that last variable 'myLikeSearch' as part of your SQL Statement.

Here's what I think you are looking for:
Place this code in the Action tree to execute before you execute the specific DB Query action (I am remembering, or assuming, the Query is triggered by a Button that is Clicked-on --- and you can do the wildcard concatentation right there in that sequence).
You can use scripting as Mackavi has outlined, or you can accomplish the same thing with a Standard Action... specifically the set Variable action (under Programming tab). You should of course have both variables established as Pub or Page variables to do this.

Hi Lars,

Thanks again for your post.
Yes we have had some superb weather of late... but not too great last couple of days!!!

I have got around the "LIKE" problem for now by creating a separate field for the Surnames only - because I could not get the code to work.
I am hoping Mackavi will be able to provide a solution to overcome that.

However - I am still having an issue getting the database to show records on the playback machine.

I published the sample access pub to the end user machine (that does not have access installed) and that worked fine
So I have checked all the pub properties - but cannot see what I am doing wrong - as I cannot see any obvious differences
And - as I wrote earlier - other PC's on my network are working fine???

What I did notice however, was that when I run the sample pub on the end user pC - it creates an .ldb file in the published folder (and closes when the pub is closed)
When I run my pub on the end user PC - no .ldb file is being created

Appreciate any advice you can give

Warm (well apart from the last couple of days) Regards

Pete