How to Query on keywords

[Tanman]

Hi there,

I am in the process of finishing my first project in Alpha Five ( a publication database). I have my data, my keywords are organised correctly but I have now hit a snag in that I am unsure how to query my keywords.

I have used the query genie and we can search on a single keyword, but not on multiple ones. Comes up with blank results. I would also like to have a form that the users can search on. I have my database with some dummy data in it attached, and would greatly appreciate any of the gurus here to take a look at it.

Thanks to those people that have given their ideas such as Peter Wayne, Mike Wilson and others that gave me ideas...especially keywords and auto fill in for fields. It has been a great help and with this final piece of the puzzle, and the menu cosmetics it will be complete. Many thanks to those that helped.

[Tom Cone Jr]

Re: How to Query on keywords

There are several different ways you can do this kind of thing, and getting the interface right for the user will dictate which direction you go. A simple solution is to create an inverted set, in which your keywords table is primary and your archive is child, linked 1:1 on paper_id.

Then create a form based on this table and teach the user to use Query by Form. QBF is very powerful and easily accommodates multi-key searches. Just use the comma operator. Check the help file topic on "Query by Form" for details (there's a section there explaining the syntax to use when typing in search keys).

Example attached, unzip to an empty folder.

Open Toms_Form, place the form in Query by Form mode, type "microsoft" then a comma then "windows" and run the query. You'll get hits for each article that has a "microsoft" keyword OR a "windows" keyword. (omit quotation marks when typing the search keys)

[Tanman]

Re: How to Query on keywords

Thank you very miuch time for replying back. This is exactly what I was looking for. Just a quick query...does the QBF also accomodate a search where I want to search for microsoft and windows?

Many thanks.

Kind regards,

Wei-Yen

[Tom Cone Jr]

Re: How to Query on keywords

Off hand I don't know if QBF can handle an "AND" query when each record contains only a single keyword. I suspect you'll need a different approach if that's what you need. As I said there are several different ways of going about this. You need to decide how your users are going to want to search...

[Tanman]

Re: How to Query on keywords

Hi,

it would seem that the client is wanting to use the 'and' function in their searches. They would like to include 'and' in their search criteria, instead of OR as specified in QBF. I would very appreciative if you let me know what are the other ways that I could use AND?

[Tanman]

Re: How to Query on keywords

I just had a rethink about this and the user would also like to use the publication data entry form to be used to show the filtered record set.Is it possible transpose the filtered ecord set into another form? Many thanks.

[Tom Cone Jr]

Re: How to Query on keywords

I would very appreciative if you let me know what are the other ways that I could use AND?

I cannot think of a really easy way to do this effectively using your table structures. I can imagine a dialog which prompts the user for a single keyword. A query is run. The user is prompted to end search or supply a new keyword, which is used to refine the query by filtering the returned records only. The user is then prompted to end search or refine further. This will work well, but requires you to do some custom programming. I'm not sure how much of this could be done using action scripting. I have misgivings about this approach in a multi-user setting. Its foreseeable that users might be running searches from different workstations at the same time. I don't recommend it except for situations where only one user will be searching at a time.

An alternative would be to set business rules for how the user will specify search criteria and build your own custom search routines to handle each possibility. Your script would then parse the input from the user, decide which search "type" to run, and then run it for the user. Again, this will require custom programming.

At present you have two tables. "Archive" (which stores identifier information about publications), and "Keywords" (which contains one to many records for each Archive record) with each record containing only a single keyword. You use a set with Archive primary and Keywords linked 1:N. An extreme alternative, which I don't recommend but which might merit further study, is to abandon this arrangement in favor of storing all the keywords for a single archive record in a single field in the archive record itself. You could then use string search functions to filter the archive table based on inputs that are received from the user, parsed by your script, and then used to query the table. Again, this will require custom programming.

[Tanman]

Re: How to Query on keywords

I have misgivings about this approach in a multi-user setting. Its foreseeable that users might be running searches from different workstations at the same time. I don't recommend it except for situations where only one user will be searching at a time.

Thank you Tom. This is not a problem in this particular situation because what they want to do is to have a database as read-only and distribute it to everybody in this case with CDs that has attended the conference.

A single user setup will work fine and your solution would work.

Basically my client will be doing the editing and addition of the records. Everyone else will be just be viewing and querying it producing reports/viewing the filtered sets.

I am a real rookie when it comes to programming this stuff,but i am happy to learn. Again any guidelines or example of code will be helpful. Many thanks, WY

[Tom Cone Jr]

Re: How to Query on keywords

WY,

Maybe others will chime in to offer better ideas for you. The approach I was imagining of running successive queries, each narrowing the range of records displayed for the user will not work if run from a CD. The sequence I was thinking of goes something like this:

a) prompt user for keyword
b) run query
c) if records were found, ask if finished
d) if not finished prompt for next keyword
e) "mark" the records found thus far
f) run query against the "marked" records to filter them by the second keyword
g) loop back to step (c) and continue.

To "mark" records found in previous queries requires being able to both read and WRITE to the database. This may not be possible with CD media.

I'm out of time and won't be able to respond further. Hopefully someone else will lend a hand.

Actually, the more I think about this the more I am coming to believe that you will find it easier to handle both single and multiple-keyword search requests if you store all the keywords for a given publication in a single field. Often a memo field is used for this, since it can handle variable length text. I'll be interested to see if others have better ideas for you.

-- tom

[Tanman]

Re: How to Query on keywords

Ah yes, the install files will be on the CD but will be installed on the computer. Thank you Tom for your help. I will follow this up, the guidelines are great and I will certainly follow it up and let the group know of the details.

[ctham]

Re: How to Query on keywords

I am new to Alpha 5. I am working with Tanman on the conference paper project. As it is our first attempt, it has been trying to say the least. Your helps are timely and most appreciated. Thanks for your prompt suggestions and effort in helping us out.

With your help and through trial and error I discovered that if I open Archive_keyword_set form, do a query on keyword using Genie, run the query. Change to another keyword, run query again. This time round it prompts for

<Search all records>
<Search currently selected records>
<Extend selection of records>

Select the Currently selected records. And it works as it should.

Thanks guys.

[Tom Cone Jr]

Re: How to Query on keywords

Con, that's good news. Keeps things simple. I feel a bit foolish not remembering that capability but I rarely use the Genie to do queries, so I was unaware of (or had long since forgotten) its ability to easily search the currently selected records. Good luck with your project and the conference. -- tom

[Tim Kiebert]

Re: How to Query on keywords

I have been playing with this during the day and came up with this which uses the xbasic version of what Con describes. And then automates it a bit. All the functionality is in the embedded xdialog on the publications_Find form. Single click on each keyword in the left list to choose or unchoose (is that a word?). The choices appear in the the right list. The three buttons are self explanatory I think.

You could expand this and get fancy by adding a control so the user could choose between AND and OR. Depending on the users choice you would branch the code to do a different query.


EDIT: To test try the keyword xxx. you should get three records. Then zzz should also return three records. Now try xxx and zzz. this should return only two records.
EDIT2: Forgot to say that I restricted enters in the form properties which has nothing to do with the queries but keeps the form from going into enter mode when scrolling past the last record.

[ctham]

Re: How to Query on keywords

Tim, thanks for your prompt posting for automating my manual procedure with Xdialog. I had a quick play this morning, it is exactly what I wanted.

I had not done any Xdialog programming. So it would educational for me to dissect your Xdialog coding. It will make a lot more sense on something I have been working on.

Could you please tell how I could copy your Publication_find form to my conference database? Thanks heaps.

[Tanman]

Re: How to Query on keywords

Thank you very much for your reply Tim, and the time that you spent on it. I have a query...I was wondering if that super control can be filtered on the current record set? What we werr thinking of was using with the results of the QBF results. It would be brilliant if this can be done. Many thanks.