Apache OpenOffice Community Forum

Looking for help figuring this out as I am not sure where to start. What I need is a button that can run a form based filter on a specific field. So instead of going and dealing with the filter function someone can just push the button, enter in the name (the record for the field the filter will be run on), and be brought to that record.

You can use a Text Box or a List Box to set a filter (save the search criteria to a Filter Table). Then press a button on a SubForm to display the matching record(s).

I have tried to figure out how to do what you are suggesting, however even after reading about the filter tables, am I unsure. Could you please explain in more detail? Also will I just be using a relationship to link the data from the Filter Table to the main table?

AGoodwin wrote:...will I just be using a relationship to link the data from the Filter Table to the main table?

Well yes and no. Yes an 'ad hoc' relationship is implied by SubForm linking, but you don't need a formal/permanent relationship between the Filter Table and your table for this purpose.

Here's some key statements outlining the filtering function of SubForm links:

• Arineckaig wrote: As I understand it, an SQL SELECT query is applied to the sub form that has the effect of a WHERE clause based on the current content of the Main Form's Link Master Field. That content is used as the parameter for the WHERE clause so that only those records are shown in the Sub Form whose Link Slave Field matches this parameter.
  
  In effect the SQL query has the following format:
  SELECT [RequiredFields] FROM [SubformDataSource] WHERE [LinkSlaveField] = [ContentOfLinkMasterField]

I understand you're trying to use a Form to search for a record(s) while avoiding the toolbar's search funtions. Here's what's possible without macros:

• 1. If you're users would be willing to select preset criteria from a scroll-able Table Control, then you can eliminate the need for a Filter Table and for a push button because any change of MainForm record triggers the SubForm link/filtering mechanism. Since a Table Control is a window on your data, clicking on any record in the Control will move the record pointer. In this case, both the MainForm (with Table Control) and the SubForm can be based on your table.
  2. If you want a List Box with preset criteria derived from your table, then you'll need the Filter Table and a push button. Since no record change is involved with a List Box selection, the button is used to trigger the SubForm link/filter mechanism using a 'Refresh [sub]form' action.
  3. If you want to type-in the search criteria (in whole or in part, with or without case-sensitivity), then you'll need the Filter Table, a push button, and some boilerplate SQL from an example.
  4. ...?

Which route do you prefer?
How many columns will be searched?
If more than one column will be searched, will you search all columns with one criteria, or each column with its own criteria?
Can you post a sanitized example of your Table and your proposed Form layout?

Option 4 will need to be used, only because the amount of options could become a bit much for use in a list box. For search criteria, only one column will be used. I basically just want them to be able to enter in the "Name" and be taken to that corresponding animal. In this instance, name is not the primary key, however it is unique as the names are for animals, not people and will not be repeated. Ideal implementation would be the label for the Name field is replaced with a button. You push button, text box pops up, you enter name of the animal whos record you want to jump to and hit enter.

The content on the left is for the form the search would be performed on, the two sections on the right are subforms from separate tables.

AGoodwin wrote:Option 4 will need to be used

There's no option 4 above...do you mean you'll need another approach? I guess you could base your MainForm on a Parameter Query (WHERE "Name" = :Enter_Name) which should force a popup each time you 'Refresh form' with a push button...(untested, thinking out-loud here)...?

If you mean option 3, then consider that manual typing could return no records, or one record or many records. That's why typing manually into a List Box (option 2) that's populated from your table might be a faster and more precise input mechanism for your animal name. When you type into a sorted List Box, the relevant entries jump to the cursor for immediate selection, similar to auto-complete. There may be a practical limit to the efficiency of this List Box feature, but I've employed this method with over 10,000 entries without experiencing noticeable lag (as the List Box searches among it's own display field entries). The List Box can save either the display field (Name) or the associated record ID to the Filter Table in this case. If you have over 10,000 records, it's probably better to save an indexed field (ID) to the Filter Table for SubForm performance reasons. Just make sure your Filter Table data-type matches your search criteria (Name = Varchar; ID = Integer).

AGoodwin wrote:For search criteria, only one column will be used.

No problem either way.

AGoodwin wrote:I basically just want them to be able to enter in the "Name" and be taken to that corresponding animal.

Option 3 will work if you know the name and spell it accurately for each search -- but that's very similar to the workflow offered by typing into a List Box which gives immediate feedback for spelling errors.

With options 1-3, your current MainForm will need to become a SubForm (drag&drop in the Form Navigator) linked by Name field to a new MainForm based on a Filter Table. Only the Name field (option 2: List Box or option 3: Text Box) will be moved to the MainForm. The MainFOrm dedicated to the Filter Table should have the following properties on the Data tab:

• Allow additions = No
  Allow modifications = Yes
  Allow deletions = No
  Add data only = No

You'll then need a Push Button with 'Refresh Form' action added to the first SubForm (your old MainForm). You can label the Push Button "Search".

sorry, I meant option 3. Also, when you mentioned list box, I was thinking a drop down, but if I can do it without drop down and have the entry typed, then that should work fine.

So, one last question before I attempt this. Although each name is unique, not every animal will necessarily have a name. On my main form, I use a reference ID with each animal as the primary key, so I would like to have the same option available to pull up a record by reference ID as I am trying to do with the name. Will this work having both fields on the new main form or will I need to find another way to accomplish this?

Well a List Box does drop-down (if enabled) but that's a necessary part of the function here (AFAIK). You can limit the drop-down to 1 or 2 as necessary, but you'll need to select the Name (or ID) from the drop-down as soon as it pops into view while typing into the List Box.

You can 'bind' as many different List Boxes to the same 'Data field' in the Filter Table as you need. Just make sure the Boundfield is always "ID" (from your table) in the List Content SQL (as shown below), to allow for blank "Names" in your table. BTW, you can Concat multiple fields together in the Display Field of the List Box as well.

Here's some examples of List Content perhaps across multiple List Boxes (Boundfield = 1):

• List Box 1: SELECT "Name", "ID" FROM "MyTable" ORDER BY "Name" ASC
  List Box 2: SELECT "ID", "ID" FROM "MyTable" ORDER BY "ID" ASC
  List Box 3: SELECT "Name" || ' - ' || "ID", "ID" FROM "MyTable" ORDER BY "Name" ASC
  List Box 4: SELECT "ID" || ' - ' || "Name", "ID" FROM "MyTable" ORDER BY "ID" ASC

Table: FILTER

• ID (INTEGER, PRIMARY KEY) (disable AutoValue; seed with '0' manually)
  CRITERIA (INTEGER) (to be used as the 'Data field' of your List Boxes)