Solved

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

Posted on 2014-09-16
13
463 Views
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.
0
Comment
Question by:j_rameses
  • 8
  • 5
13 Comments
 

Author Comment

by:j_rameses
ID: 40326033
Here is an image of what my report generator looks like:  Report generator image
0
 
LVL 24

Accepted Solution

by:
Will Loving earned 500 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 ]
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
 

Author Comment

by:j_rameses
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?
0
 

Author Comment

by:j_rameses
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.
0
 
LVL 24

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….
0
 

Author Comment

by:j_rameses
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

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.
0
 

Author Comment

by:j_rameses
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.
0
 
LVL 24

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.
0
 

Author Comment

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

Author Closing Comment

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

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  :-)
0
 

Author Comment

by:j_rameses
ID: 40333285
NICE!!!!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now