Query via form?

[jlockley]

I have a data base which contains a total of 200 fields, most of which are logical. I would like to enter my searh criteria in a query by way of a form rather than through the query bar. Forms built around/attached tp a query will seek out the attached table for information rather than just the query.

I understand that there is a site with instructions somewhere on the web, but I have not been able to find it.

Note that I am not adept at coding, but can figure it out if I have to.

(Example: I want to list all candidates who live in the southeastern quadrant of the US and have knowledge of volume production and have formal training, then run the query. If I use the query view i have to scroll along five widths for some fields, unless there is a way to wrap a query. a form with the various criteria groups displayed in the pages of a notebook would make the situation much easier.)

All suggestions appreciated.

 

[Langley]

I think I understand what you are trying to do.

You can do this in OPAL by assigning values to variables based on the data entered into your form. For 200 fields it will be a lot of repetitive coding to check each one and assign a variable value or 'blank', so it's a good thing you can copy and paste. You can insert the basic query into the OPAL code using 'Paste From' under the 'Edit' menu, then add your tilde variables to each field that might be queried.

i.e. attached to the pushbutton method of a button object
to check the value of a checkbox field named FormalTraining.

----code---------------------
var
my1stVariable string
qVar query
endvar

my1stVariable = FormalTraining.value ('True' if checked)

qVar =
Query

:myAlias:MyTable.DB | Name | Formal Training |
| Check | ~my1stVariable |

EndQuery

if not qVar.executeQBE("riv:answer.db&quot
then errorShow()
endIf
----end code----------------

Obviously you would need to repeat the variable validation for each field on the form. Also, the query would have to include any field that came into play for the query (either checked or used to limit the query).

Also, interactively you can use the Ctl-R combination to rotate the query fields in the query window.

Mac

 

[jlockley]

Thank you.

I see what you mean. It's a great method for only one or a few fields, but as almost all of the 200 fields at one point or other would be queried, the task looks enormous. (I have an executive search firm with about 10,000 candidates logged according to skill, experience level, specialty, etc..)

Rotating the query works, but the skill catalogue encompasses something like 90 fields, which means a lot of rotating.

What I am trying to figure out is how to create a full screen front page to simplify search.

My candidates are chefs, so I need to know in what kind of venu the person has experience (hotel, club, large restaurant, prestige restaurant..there are about 40 possibilities) possible culinary specialties (Italian, Asian, banquet), System abilities (only about twenty five variables), plus current level, stability factor, geographic constraints. Any particular search can include a combination of about four variables. For instance, I am looking for a specialist in regional Mexican at the Executive level to go to Florida for a corporate level position. I need to cull "multi unit" from one set of differentials, "Mexican" OR "Hispanic" from the cuisine set, FLA or SE from location and Georgraphic preferences, RESTAURANT from type. The form attached to the answer database then brings up each possible candidate's name, telephone number, current job and position, a short list of his past position and blob resume file.

If I could, for instance, wrap the #@#@#$% query view to scroll down the screen and just past in TRUE for all of the fields I am seeking out, it would save hours. This, I have figured out, is not possible. Any other ideas?

Thanks again.

 

[Langley]

Personally, I would re-think your table structures - but you don't have time for that now. The only thing that comes to mind without restructuring the whole database is to use Tcursors and a scan loop. Since you are only looking for a few variables about any particular candidate it would be fairly simple. It won't run quite as fast a query, but the set-up time will be reduced to seconds instead of however long it takes you to build a query.

I'm assuming that most of your fields are logical. So, I need to know the content of the non-logical fields to help with the speed. If one or more of those fields can be included in a small query, we can limit the size of the table to be scanned, improving the speed of the process.

Let me construct a quick model and I'll post it in a few minutes (I have to get some coffee).

Mac

 

[Langley]

Okay here's my idea. There are other ways to do this which require dereferecing but because I think you might want to mix and match some of the categories this way will probably work best. This sounds long winded, but bear with me.

Restructure your table to add one extra field at the end of it, name it 'Select' and make it a numeric field.

You should already have a form with all the relevant checkboxes on it attached to your table. Make a copy of the form and remove the table from the data model of the copy. This leaves you with a new form that has all your fields; they are now undefined, but still retain the correct object names (which are your field names unless you changed them on the original form - if you did, change them back). This form will now be your query screen. Drop a new unbound edit field on the form called 'Rank' (should be Integer format), and a button on the form.

All the code will be in the pushbutton method of the button. Now comes the coding, which will take a bit of time but it should work.

First if you can reduce the size of the table by doing a preliminary query, then you should do so. I'm going to give some made-up examples which you'll have to correspond to your situation.

You will need to make a query before you start writing code. That query should be a straight blank query (checked) of all fields in the table. No need to run it, just save it as something you'll remember (i.e. chefs.qbe).

Assume there are two data fields (not logical fields) called Continent and Chef_Type, which you can use to pare down the initial data.

The first part of the pushbutton method should incorporate your newly created query. First grab values from the Continent and Chef_Type fields on your form and plug them into variables. Then paste in your query, adding the tilde-variables in the correct columns. Execute the query and the result will be an answer table that is a pared down version of the original table - including that 'Select' field you created earlier.

This is the table you will work with using your tcursors. The scan loop will be the most time consuming process to write but you can cut and paste a generic if/then loop and use find/replace to speed the process since it is repetitive.

First open the Answer table and place it in edit mode using your tCursor. Next scan the tCursor. Inside the scan loop you are going to compare the values on the form to the corresponding values in the table. You are going to have to use that numeric Select field to rank the results.

Remember that 'Rank' edit field on the form you added? You should enter the number of criteria checked in logical fields. For example, if you checked ten fields true for the query, put a 10 in the Rank blank (you can also do this programatically, but that takes more time - plus you may not want the ranking to always be exactly the same).

Let's make up some more sample names for logical fields to use in this example: Mexico, Sauces, and Corporate.

Okay, the scan loop works like this:

scan tc:

if Mexico.value = true
then if tc."Mexico" = True
then tc."Select" = tc.Select+1
endif
endif

if Sauces.value = true
then if tc."Sauces" = True
then tc."Select" = tc.Select+1
endif
endif

if Corporate.value = true
then if tc."Corporate" = True
then tc."Select" = tc.Select+1
endif
endif

Repeat for every logical field.

endscan

When the loop ends, your Select field will have a number value in each record.

(Now you run a second query against the answer table, linked to the original table )

qvar =

(make a linked query between the answer table and your table with a join on the key field, paste it here and pull the number from your Rank field into a STRING tilde variable and place it in the Select category - you can even add a '>= ' to the front of the variable in case you want to find lesser matches)

execute the query

The resulting answer table will have only those chefs who meet all the criteria (or some lesser ones if you used a lower number in the Rank field). Use this the same way you would have used the result from your manual query.

(I can email you some sample code to make all this clearer, but this message board does not lend itself to lengthy code structures).

Hope this helps,

Mac

 

[jlockley]

It helps a lot. I will try to get to it tomorrow and see how it works.

It seems to me though that in not making possible a simple front page for queries, Corel has missed a beat.

The other fields are mostly alphnumeric, one date and one incremental. They include name, location, zip (numeric) various telephones. The table includes one blob (the resume) field plus three memo fields (one for resume content, one for reference names, one for menu and one for notes.


Thanks!

 

[Langley]

You're welcome - anytime.

Mac