here are the scenarios,
i will be filtering the record on whatever choice i made from the dropdowns, it can be any combination like (fruits/All/office) so the criteria would be the options the user selected
any thought on how to do the work around with this
can i have anymore solution? or other way of doing this. or how can you add a criteria in Perform Find function dynamically, just like adding a WHERE CLAUSE in sql
Hi @marke1415
We'll be better able to provide useful answers if we have a little more background on where you are at with this. I know that you mentioned that you just adopted FileMaker Pro recently, so here are some questions that you might answer that will help add some context to this thread:
Excellent context. Thank you @marke1415.
Next questions:
A very common pattern is that filter field such as what you've got are implemented by using fields which have a global storage type. The global storage type is helpful because it allows the user to enter data into this "global" field without actually changing the record data.
can i just do.
Perform Find -> first dropdown
then
Perform Find -> second
then
Perform Find -> third
but it seems like its not right.
thank you sir for patiently replying
Hi @marke1415
I think a good next step for you to take would be to watch or read one or two tutorials on how Find Mode works in FileMaker.
I just did a search, and I see that there are a number of hits for material that I think would be a great starting point. Avoid tutorials about "QuickFind"; the tutorials that will help will be about "working in find mode" and "find requests".
Since we are talking FIND I thought I'd drop this little item in. QuickFind will search an off-layout (that parking area for things off to the right side) merge field. Another Matt Petrowsky gem.
can i just do.
Perform Find -> first dropdown
then
Perform Find -> second
then
Perform Find -> third
Yes, you can do it this way, sort of. @steve_ssh is pointing you in the correct direction (as usual). It's important to grasp the Find tools within FileMaker. Take a look at Found Sets Script Steps Here to get a feel for some of the steps you'll need to understand.
Also look at the Duplicate Record/Request script step. When in Find mode it's used to create a new Find "Request", when in Browse mode it's used to duplicate the record (a bit confusing at first, but notice that when in Browse mode your top menu shows "Records" and when in Find mode it changes to "Requests").
The user experience you're describing can get a bit messy (to script) if many values are available in your selection value lists. And it also depends on your data structure. For example, I took your example to mean that Fruits, Vegetables, Cars, Bicycle are "categories" so I lumped them together (at least when creating new records). And I took Home, Office to mean "location". This can work, but it's not very scalable and I'd want to look to other ways to do this. Filtering by "pick list" is fine, but by allowing any combination (and any order) of selection it can get convoluted.
IF the first two are truly separate filters and not shared categories things could get more manageable. For example the wildcard "*" could be used to find "All" in each.
Here's a crude demo file for you to look at. The "filter" script is rather verbose (i.e. messy and nearly 60 lines, yikes) and there are probably other, cleaner ways to get the user experience you're looking for. There are "tricks" one can do with portals and relationships, but to me they're more advanced methods and one should learn the basics of "Find" first.
The purpose of sharing this file is not to suggest it's elegant or my suggestion to follow it, but hoping that it may provide a working illustration of what steps could be used to accomplish this and how they work. If you carefully read through each step in the script (and step through it using the Script Debugger) perhaps it will provide some guidance.
HTH
Edit: Oops! Deleted a needed conditional in the script while testing. Added it back and replaced the file.
FilterByDropdowns.fmp12 (320 KB)
By the way, @marke1415 , this could be simplified if the user was required to select in order from list 1, list 2, then list 3. There are ways to control such behavior if that's a U/X option. My example allows for selecting in any order and that's where it gets a bit "messy".
I chose to apply the script to each selector, using the OnObjectModify script trigger, but it could be done with a button once the selections are made (hence my inclusion of such a button).
so you mean 1button for each dropdown?
or is there a way, (i know there is), reading all 3 criteria then do the
if
add to variable
else if
add to variable
end if
then assign to variable to perform find criteria
@marke1415, in my sample file the script fires each time the selector is modified, so no button is needed. Alternatively, one could simply run the script once the selections are made. In which case we'd use a combination of Enter Find mode; set field, (maybe) duplicate request, ... perform find. If the three lists are for distinctly different fields (see my note above about categories), the Find would just be to Enter Find mode; set field 1, set field 2, set field 3, perform find. (We'd use some conditional, like "If dropdown1; not isEmpty; Set field.."
You'll see some of that in my script in the sample file.
Note: I replaced the sample file just a moment ago because I had inadvertently removed an "Else" conditional. Please download it again if you don't have that newest one. Sorry, I didn't rename the file as version 2, I'm multitasking (badly).
One way is that you could start your script by setting variables to each of the dropdown field values ā one variable for each selector. Then enter find mode and set the appropriate fields to search. NOTE: If you want to find two things in the same field, you'd use Duplicate Record/Request (as in my script) to search for two different items within the same field.
As in:
Enter find mode
Set field: myTable::foodType ; $value1
Duplicate Record/Request
Set field: myTable::foodType ; $value2
Perform Find
(Edited to correct error)
That's why I took that approach, so that you'd see it in action.
Edit to add, since the system doesn't allow another reply:
Here is a new sample file, @marke1415, which employs the button to filter and a simpler Find solution on the List View 2 layout. This example also used the Duplicate Request script step to find two values in the same (category) field. If your solution has three distinct fields for corresponding to your value lists, then you can just set the fields without the Duplicate request.
Also, you could probably skip the "If [$variable]..." test to see if the variable has a value, as if the variable was empty due to nothing in the selector, nothing would be set to the field for the find. BUT since we don't know your data structure I added the "IFs" as a precaution (and good practice).
FilterByDropdowns_v2.fmp12 (340 KB)
And by the way: this rushed second version employing the button breaks when the second selector is set to All because if finds all of Fruits, Vegetables, Bicycle, and Car. That needed to be controlled by adding conditionals like in the Trigger | Filter List script (using Duplicate Request). I just wanted to give you a start to a simpler approach before I need to run off to dinner.
its great, but sometimes it breaks down when on the 2nd dropdown, 1st column show everything.
just to give you an idea, here are my 3 tables and sample data. just want to filter the records and count the result.
by the way, i learned a lot from your code. Thank you
Great, @marke1415 ! I'm glad there was something useful for you.
Yes, there may be some combinations that break until the conditions are worked out. Seeing that your three fields upon which you filter are separate makes some difference, so the logic just needs to tinkered with a bit. I was in a rush when I prepared the samples for you, so didn't have time to provide a sample using three distinct fields (rather than lumping "categories" together of 1st and 2nd drop-downs (as mentioned above).
I'm tied up at the moment, but I'll try to come back with another look at it. It helps to see the example of your design.
Okay, @marke1415 , here's a new file that I think is closer to what you need. Your last explanation and image help to clarify. It's much simpler since your three selectors are for unique fields, and not combined categories as I had originally assumed from your post.
On "List View 1" layout, the find script fires with each modification of a selector "dropdown".
On "List View 2" layout, the script is fired by the button at top only. However, there is a script trigger on the selectors to clear the selector field if "None" is selected.
Let me know if this is closer. I smoke tested it some and didn't see errors as I checked => [> sometimes it breaks down when on the 2nd dropdown, 1st column show everything].
The "All" and "None" basically return the same result. I had just added "none" before as a way to easily clear the field for testing and depending on your user needs.
FilterByDropdowns_v3.fmp12 (328 KB)
i will thoroughly test it later, and will inform you
thank you very much
your v3 is flawless, im trying to put it on my app, the 2nd dropdown is not responding, i will check what i am missing
Iām glad the example file works for you.
If the second selector is not responding/working, check carefully for typos in spelling or copy/paste errors where a field name was not updated, etc.
Or check that the script trigger (if using that method) is properly set.
