Who to run a filter using multiple options from combo-box in FileMaker?

Posted on 2014-09-16
Medium Priority
Last Modified: 2014-09-19
I am running FM 13 Advanced.
I created a layout as a Reprot Generator, being dynamic.
In other words, a wuser select different options for generating a report.
I have every option working except for the combo-box.
The report generator works if I choose one option, but if I choose two options, it does not work.
It only provides me with values for one of the selected options in the combo-box.
I tried a loop structure but that did not work.
Your assistance with this would be greatly appreciated.

Thank you.
Question by:j_rameses
  • 8
  • 5

Author Comment

ID: 40326033
Here is an image of what my report generator looks like:  Report generator image
LVL 25

Accepted Solution

Will Loving earned 2000 total points
ID: 40326161
I'm presuming that you are entering Find mode and presenting this layout. The issue with using a checkbox like this is that when someone checks more than one option, it will only Find records that match that configuration, meaning that have both entries in that field. It's an AND search rather than an OR search.

To make this work you need to parse out the multiple values in the Products field (a checkbox simply adds the checked item to a return-separated list within the field) and create additional Find requests for each of them.

1. Create a new field called ProductSelect_g and make it a global field.
2. Add that field to your layout with the checkbox formatting
3. Revise your "Generate Report" script along the following lines:

Set Variable [ $Counter ; 1 ]
   Set Field [ Products ; getvalue( ProductSelect_g ; $Counter ) ]
   Set Variable [ $Counter ; $Counter + 1 ]
   Exit Loop If [ $Counter > ValueCount( ProductSelect_g ) ]
   Duplicate Record/Request
End Loop
Set Field [ ProductsSelect_g ; "" ]

Open in new window

So what this is doing is counting the number of Products in the global field and then if there are multiples, it creates a Find Request for each product. All other Find criteria are left the same except for the Products field. When the Counter variable exceeds the number of values in the ProductsSelect_g field the loop ends and the Select field is cleared.

Author Comment

ID: 40326537
Will, thank you for your response, I was actually running that layout in 'Browse' mode.  Is it possible in browse mode?
The reason it's in browse mode is so that users can select different options with error-checking.
For example, they can only enter either an 'exact date' or a range date-but both values for the range must be included(from and to).  That is one example. Another is, when they select a name, that is a drop-down that only populates with active names only, it excludes the inactive records.

If it's possible in browse mode, do I place the global variable in the same table as the other items? Where should I exactly place it?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 40326546
Will, forgot to mention that the generator when it creates a report it breaks down the items as per the 'type' that it is.  For example, type = Plastic or type = metal, or type = wood.
That's an FYI.
LVL 25

Expert Comment

by:Will Loving
ID: 40326583
It's actual a global field rather than a Global variable and yes you can place it in the same table though since it's global, it really doesn't matter what table you place it in. With global fields you can reference them anywhere without worrying about whether you have a valid relationship.

You create a text field and the set the "Storage" option to "global". You would simply change your layout to have that field be where Products is - just create the field and then double-click the Products field on our layout and switch fields. If you're working in Browse Mode then I would have assumed that all your field were globals.

I'm assuming that the "Generate Report" script is taking the values entered, going in to Find Mode and generating a Find Request for what was specified in the layout. The only difference then is that you would need to move the script steps down to below the Enter Find Mode script step. They might require a small amount of editing but should mostly be correct.

With regard to the date vs date range, what I tend to do is use two date fields (again globals if you're in Browse Mode). If it's a single date they just populate the first field and if it's a range, they populate both fields and the range if from the first to the second. That way you can just let the built-in error checking for a valid date work rather than having to have fancy error checking in Browse for whether what's entered is a valid date OR date range (a date range being something that would be seen as invalid in Browse but not in Find.)

I don't see a Type field so I'm assuming that breaking out Type is simply a sorting and sub-summary issue….

Author Comment

ID: 40328272
Will, thank you.
It worked!!!
It now filters only the items selected.
I was beating my head before trying to get it to work.
I was reluctant to use Experts-Exchange hoping I can figure it out on my own, but was unsuccessful.

Yes, the script takes the values on the layout and then goes to a the report layout and does a find there.
Yes, the code snippet you provided is below the "Find Mode" script step.
Yes, the Type field is a sorting and sub-summary.  It turned out well.

Your idea regarding the date range sounds good.
Just trying to get a better idea in the coding for it.
Right now I have three fields for date: exactdate, datefrom, dateto: all three fields are date format not global.  If I am to use only datefrom and dateto and turn them into globals thenI won'thave to do error checking then, correct?
Will I use the same formatat as for the combobox?
Not certain on the coding for that mechanics?
LVL 25

Expert Comment

by:Will Loving
ID: 40328353
This is sort of a separate question (which should be posted separately on EE) but I'll answer it here.

The date error checking will occur whether the field is set to Global storage or not, it's still a Date field and will still validate entries as such. In using two fields I would generally label them something like:

"Exact Date or Range Start" ____________
"Range End Date" ____________

For coding, I'd use something like this:

Enter Find Mode
Set Field [ someDateField ; Case( not isempty( DateFrom )  and not isempty( DateTo ) ; DateFrom & "…" & DateTo ; DateFrom )  ]
Perform Find [ ]

This calculation simply looks at the DateFrom and DateTo fields and if both have values, it concatenates them into a Range string, e.g.  "1/1/2014…12/31/2014". Otherwise it uses only the value in DateFrom, if one exists.

Author Comment

ID: 40330540
Good morning Will.
I had our users play around with it and they like it.
I did do the date range changes, and that made it much better in cleaning up the lines of code, thank you.
There is one issue we did hit, that only one user can use it at a time.
Is there a way to allow multiple users to use this layout?

Thanks a million.
If you prefer me to create a new post, I can do that.
Pls. advice.
LVL 25

Expert Comment

by:Will Loving
ID: 40330647
What you're hitting is a record-locking issue. It might work if every field on the layout is global fields because those are unique to each user session, but I can't remember how FM deals with record-locking in that situation. This is the downside of doing it in Browse Mode in that only one user at at time can edit a record.

If using all Global fields doens't prevent the record locking - and I think it won't - then you'll probably need to create a separate table with all your search fields in it - when a user goes to run the report, a new record is created and you then let them see only that record. Once they are done with their entries you perform the Find, generate the report and then delete their search record.

Author Comment

ID: 40333169
Will, you were correct.
Once I made all fields global, the problem with record locking disappeared.
thank you for everything.

Author Closing Comment

ID: 40333173
Will was very helpful.
Next time I will send him a link to help me with future issues.
LVL 25

Expert Comment

by:Will Loving
ID: 40333268
Thanks j_rameses, you accepting my answer as the solution just pushed my FileMaker specific point total over 1,
000,000. I'm quite pleased at that  :-)

Author Comment

ID: 40333285

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question