Filter records on a form - user selected

[stemie]

Im creating a filter on records using a form.
I would like to create an option to filter using two fields - name and year - but I would like the user to be able to select the year and the name.
I am using the actions in the query section.

Essentially I require a two step query using drop down boxes where users selects name and year which then go on to query the results.

[Ted Giles]

Re: Filter records on a form - user selected

Form
..Query Genie
...Select Fields
....Specify a Field Value from the Form
.....Save the Query
.......Add the Query to a Button using Action Scripting
.........Press Button

Alternatively, search Dynamic Tree on the Forum. There are a few examples and they work brilliantly.

[stemie]

Re: Filter records on a form - user selected

Hi thanks for the reply but Im clearly retarded or something because I cannot find the query genie when Im in the form - Ive looked in the toolbox, supercontrols, form section on the top menu.
I know how to create a button and then create a query based on an action but Im trying to filer records based on two fields that the user selects, they must no be predefined values.
This is obviously possible but all I can manage to get is the filter according to a specified value which is not helpful for my purposes.
g.PNG

[stemie]

Re: Filter records on a form - user selected

Ok I found the query genie it was in the form view not form design.
My problem is that I cant get the values to be filtered, to appear in a drop down box so the user can select them.
The only option Im getting is to set a predefined value.

For example I want the user to select a year from the drop down box currently all it does is specify a given year with no choice.
Surely this is common functionality it can't be hard to do.

[Ray in Capetown]

Re: Filter records on a form - user selected

Steve
1.
Where do you want the query to appear?
Would it be like on that YR field on the screensample whilst completing the form > or to select before you open up the form, or for a report ?? or for a user to select from to filter a browse?
2.
What years must be available for the user to select from? - any year ? from a table containg possible years ? filtered to certain records?.

There are many many options - you could put a drop down list box on the form, on the fieldrules of the table, in a separate dialog.

[Tom Cone Jr]

Re: Filter records on a form - user selected

With a bit of end user training query by form can be used by them to do this easily.

Otherwise, tell us the exact sequence you'd like for the user to follow. You may be thinking of a multi-step sequence in which theuser is presented with choices for each filter component, following which your script combines them into a legal filter expression and then runs a query. Or, you may be thinking of something else entirely. More details please. There are lots of different ways this might approached.

[stemie]

Re: Filter records on a form - user selected

Hi Ray, I would like the user to select the year and name from two drop down boxes in the actual form and then the query will be based on these inputs.
The years available are all the years in the table (user selects a year) same for name.

I have tried to put in drop down boxes on the form but either nothing appears when I view the form or it only allows me to select one year.

[stemie]

Re: Filter records on a form - user selected

Hi Tom, I have two fields that I want the user to select to create the query for the form. They select a name and a year, both of these fields values come from two respective tables with this data. So a drop down box with all years and drop down box with all names then a query/filter based on these inputs generates results in the browse view (F8 when viewing the form)

[Tom Cone Jr]

Re: Filter records on a form - user selected

I have tried to put in drop down boxes on the form but either nothing appears when I view the form or it only allows me to select one year.

There's a big difference between a filter that uses a single year, and a filter that involves multiple years. More details please. Try to be specific. At times you indicate you want the filter to include a single year and a name, and now it is beginning to sound like you want to be able to filter based on a name and several years. Color me confused.

[Ray in Capetown]

Re: Filter records on a form - user selected

Hi Ray, I would like the user to select the year and name from two drop down boxes in the actual form and then the query will be based on these inputs.
The years available are all the years in the table (user selects a year) same for name.

So the form you show is to be used to set a query?, that takes us back to query by form or not?
Do you want to present a filtered list of horses based on the year and name in a parental chain?
If so, that depends on your design, whether they are all in a single table.
I think you will get a quick answer if you submit a zipped copy of your database so we can see what you do.

[Ted Giles]

Re: Filter records on a form - user selected

Just when I thought I had the answer.
Steven, see if the attached has any value. (Might not now that you have elaborated.)
As Tom comments, there is a big difference between a Name in a Year, and a Name in a span of Years. It's not difficult, but the specifics need to be agreed before you get applicable suggestions I suspect.

[stemie]

Re: Filter records on a form - user selected

Tom, a user will view a form, they need to filter/query results by name and year. So there are two drop down boxes on the form one for name one for year.
When the user clicks a drop down box it lists all the values ie all the years and all the names which come from the table.

"At times you indicate you want the filter to include a single year and a name" - those are the only results I get when I try to do it, single year and single name, which is what I do not want.

[Tom Cone Jr]

Re: Filter records on a form - user selected

Ok, maybe this screencast will get you going in the right direction.

http://screencast.com/t/5U1Q3VseV

My example does not use drop down boxes. I simply run an action script that prompts for the desired values and runs the query against the current form.

----------------------

Later on

My screencast is limited to 5 minutes so I didn't get a chance to explain how the compound query was defined. Basically I let the filter builder genie do it for me. The filter expression looks for customer_id values that match the value in the variable I populated from the user selection AND records that have the selected Year value in their DATE field. The genie built the expression for me. Harder to describe than to do. However, if this is new ground for you let us know. I can do a screencast walking through this last sequence if that would be helpful.

[stemie]

Re: Filter records on a form - user selected

Ray the db is 50mg big I presume thats bit large to upload here.

The problem I have with query by form is that is too complicated for the end user, surely there is something more simple for this simple task.
I want a simple user experience when they have the whole dialog box with all these other options that aren't necessary for their task, it just creates confusion.

Im clearly missing something because what Im asking for is surely super basic.

A user selects two fields to filter the results - they select the name and year from a list of all possible choices from the table or set not just one name and one year.
They are using a form because it is more simple for them to work with than browsing tables.
The form is used to add data to the database but also to check if data exists (hence the need for this query/filter)

[Tom Cone Jr]

Re: Filter records on a form - user selected

Steven,

If your dropdown boxes are bound to the table fields, then that explains why they only contain a single field value. They're bound to the field values in the current record, only, and will never display all the field values from all the records in the table. If I've guessed correctly then beware of doing it like that. Even if you force other values to be available in the dropdown controls the mere act of selecting a value from the list will change the field value in the current record. Much better to bind the control to a variable and then populate the list dynamically. That's the approach I've taken in my example.