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

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.
j_ramesesInfo Sys MngrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

j_ramesesInfo Sys MngrAuthor Commented:
Here is an image of what my report generator looks like:  Report generator image
0
Will LovingPresidentCommented:
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 ]
Loop
   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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j_ramesesInfo Sys MngrAuthor Commented:
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?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

j_ramesesInfo Sys MngrAuthor Commented:
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.
0
Will LovingPresidentCommented:
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….
0
j_ramesesInfo Sys MngrAuthor Commented:
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?
0
Will LovingPresidentCommented:
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.
0
j_ramesesInfo Sys MngrAuthor Commented:
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.
0
Will LovingPresidentCommented:
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.
0
j_ramesesInfo Sys MngrAuthor Commented:
Will, you were correct.
Once I made all fields global, the problem with record locking disappeared.
thank you for everything.
0
j_ramesesInfo Sys MngrAuthor Commented:
Will was very helpful.
Next time I will send him a link to help me with future issues.
0
Will LovingPresidentCommented:
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  :-)
0
j_ramesesInfo Sys MngrAuthor Commented:
NICE!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.