Filtered Views

[TattyDon]

I have what I guess is a fairly simple issue. I am a recent convert to Filemaker having used MS Access in the past. What I am stuck with is how to filter records.

I have a list of transactions and a calculated field which works out which month they were conducted in. In access I would have set up a drop down menu which would populate from that months field. I would then have a button which would take the value selected from that and return the found records.

In filemaker I don’t seem to be able to set up a drop down (or pop up) menu like this. As soon as I try to select a value from the month field, it tells me that I cant change the value since its a calculated field (quite right!) However, I don’t want to change the value – only to use that selected value to present some records.

Do I need to load the layout in find mode, populate the month drop down menu and then have a button which submits that?That works but is that the best way – I’d like to be able to go from normal mode and enable the user to select a month then press find. With the solution above they can’t do that as until they are in find mode they can’t select a month!!

Cheers

Tatty

[bcooney]

There are several approaches to this, and which you choose depends on the interface you desire.

So, if the scenario is that you wish to have the user in a list view of transactions, and ONLY narrow the list to the desired month:

1. Use the right click Find Matching popup menu.

or

2. Have a global field gMonth at the top of the list formatted as a popup menu, with a QuickFind button next to it. Set gMonth to a value list of month names. User selects a month and clicks QuickFind. A script runs. It enters find mode, sets your calculated month field to gMonth and performs find.

3. Have an array of buttons for each month that perform a scripted find for the month. Same script, different script parameter.

My vote, however, is to provide a separate layout dedicated to Find.

1. User clicks Find, and your find script starts.

(pseudo-code)

Set Error Capture On

Abort Off

Go to Layout (Find)

Pause

Perform Find

If Get (FoundCount)=1

 go to form view

elseif Get(FoundCount)>1

 go to list view

else // no records found

 show dialog "Sorry, no records match..."

 Go to layout List

 Show All Records

endif

The reason I vote for the full dedicated Find routine is that I predict you'll eventually need it. And..don't they need to not only find January, but January 2008? So, searching by just month seems a bit odd.

Another technique for a Filtered View of data is to create a "filtered portal."

Edited November 21, 2008 by Guest

[TattyDon]

Thanks, and thanks for pointing out the obvious point that I had completely missed. Am migrating an excel solution (where a new spreadsheet is created every year) to Filemaker so it wasn't an issue before - can't believe I hadn't thought of that!

Cheers

Tatty

[TattyDon]

Thanks for your reply. I'm being really thick but how do I pass the current variable into the find routine? i.e. user has selected month and I want to return all records with that month in?

My difficulty is I cannot see how to pass the variable from the month selection to the find script. I can run a find script that pulls back the records where month = 2. What I cant work out how to do is run a script that pulls back the records where month=monthchoice (where monthchoice is the input field)

Edited November 24, 2008 by Guest

[bcooney]

2. Have a global field gMonth at the top of the list formatted as a popup menu, with a QuickFind button next to it. Set gMonth to a value list of month names. User selects a month and clicks QuickFind. A script runs. It enters find mode, sets your calculated month field to gMonth and performs find.

The key is that the field that the users enters their month choice is a globally stored field. Let's say it's a value list of the month names. You'd have a calc field in your table that is Month (date).

Script for button next to gMonth is:

Enter Find Mode

Set field MonthName; gMonth

Perform Find

Go to Layout List

or

Go to related Record (Transactions to Transactions by gMonth)using current layout.

Overall, there are several ways to pass variables in FM.

1. Global fields. Useful for building relationships based on a user choice or filtering portal views.

2. local variables.

3. global variables.

4. scriptparameters (which can be all of the above).

Just saw that you've already gotten this answer: Duplicate Post

Edited November 24, 2008 by Guest
Duplicate Post