1 2 Previous Next 18 Replies Latest reply on Apr 1, 2013 12:08 PM by hanstrager

    Sorting and filtering records

    hanstrager

      Title

      Sorting and filtering records

      Post

           I'm making a small application so I can keep track of my receipts - over the years I'm probably going to have a few thousand receipts (records). Making it easier for my accountant and for myself to keep track on spending, I need to be able to both filter, ommit and sort these records via various fields. What is the best way to do that..?

        • 1. Re: Sorting and filtering records
          philmodjunk

               Are you familiar with how to perform finds and sort records in FileMaker?

               This can be done by selecting options from the Records menu or by clicking icons in the tool bar. It can also be scripted.

          • 2. Re: Sorting and filtering records
            hanstrager

                 Now I got 6 different fields: date, Country, category, season, VAT, price etc...

                 What i want to do is create different layouts in table view, and have so there will be some sort of way of sorting these records in browsemode. So fx. I can Filter records between 2 dates, show everything from only one country and then sort them first by date and then by season..? at the same time have different subtotals for VAT and Price fields. Is there a way to do this in browsemode without combining search find and sort..?

                 I was thinking having a drop down list for fx. each field where you can what values you want to have displayed. and 2 fields where i can put 2 date ranges..? 

            • 3. Re: Sorting and filtering records
              philmodjunk

                   Yes, but do you know how to do this manually? If you can do it by selecting options from the Records Menu or by clicking icons in the tool bar, we can talk about how to script this.

                   To find your records on a given layout, you can click the Find button in the tool bar, enter search criteria in different fields, then click the perform find button. You can then select Sort Records... from the records menu to sort them into the order that you want. To find all records in a given range of dates, you can enter two dates, separated by ... while in find mode. Example: 1/1/2012...1/31/2012 would find all records for the month of January, 2012.

                   You can create any number of new layouts in your database, so you can set up several different ones designed to produce different reports. I recommend that you take a look at setting up summary reports in list view for this.

                   Here's a tutorial thread on summary reports: Creating Filemaker Pro summary reports--Tutorial

                   If you can manually find and sort your records, you may find this thread on scripted find examples helpful: Scripted Find Examples

              • 4. Re: Sorting and filtering records
                hanstrager

                     I works perfect - thank you for the link,

                     I've used the your description to make a check box to filter my categories, is it possible to have these checkboxes in a drop down menu, since I have quite alot of options..?

                • 5. Re: Sorting and filtering records
                  philmodjunk

                       A drop down list or pop up menu are simple options, but there is no built in setting for giving a scroll bar to a set of radio buttons or check boxes. You can produce such an object that looks and functions like a set of check boxes with a scroll bar, but this requires a portal to a related table, buttons, scripting and conditional format expressions to pull off.

                  • 6. Re: Sorting and filtering records
                    hanstrager

                         Might be a bit too much...

                         Is there way that you can search on multiply names in a search field so for instance you would find everyone with the name paul and/or john..?

                    • 7. Re: Sorting and filtering records
                      philmodjunk

                           Remember that thread on scripted finds? I recently added a new post there that provides an example script that loops through a list of values and performs a find like you describe here. You could click checkboxes for paul and John and the script finds all records for Paul OR John.

                      • 8. Re: Sorting and filtering records
                        hanstrager

                             My problem is, I might have 50, or more names, so there's gonna be a lot of check boxes, I'm looking for a way to get around that...? Im trying to have a search field where I can search for both John or Paul but I can only have it search for one of the names..? But not sure that is the best way to do it..?

                        • 9. Re: Sorting and filtering records
                          philmodjunk

                               Did you see the example of the looping script at the end of this thread? Scripted Find Examples

                               It can use a return separated list of values to perform that type of find. The number of names chosen, be it 1 or 51 will not matter, the script will handle it. By creating a separate find request for each listed value, it does an "OR" type find like you are describing here.

                               While a check box group is the simplest way to set up a field where the values selected are stored in a return separated list. It's not the only way.

                               I've set up a portal to a table in my Known Bugs List Database, where you can select a different keyword on each row of a portal from a drop down list. A script uses the List function to copy a return separated list of the user specified keywords into a variable before entering find mode and then the script loops through these values setting up a separate find request for each selected keyword.

                          • 10. Re: Sorting and filtering records
                            hanstrager

                                 I've made a check box for filtering some of my records. But when I uncheck the boxes it dosen't "unfilter" again. Is there some way to make it show the records that i uncheck in the check boxes again, without show all records and start over..?

                            • 11. Re: Sorting and filtering records
                              philmodjunk

                                   Checking the boxes won't filter the records, Neither will unchecking them "unfilter" them. Checking or clearing a checkbox, by iteself, is just data entry in a field. The script that uses the values you have selected to find records is what is doing that for you.

                                   If you have a script that you perform to find the records after selecting one or more check box value, you can perform that same script after clearing a check box field and it should produce an updated found set of records.

                                   You may want to experiment with the OnObjectModify script trigger as it will perform your find script each time a checkbox value is selected or cleared.

                              • 12. Re: Sorting and filtering records
                                hanstrager

                                     I've tried a few different things but seems not to be working at all. This is what I have so far:

                                     Set Variable [ $CategoryList; Value:Globals::Category ]
                                     Enter Find Mode [  ]
                                     Set Variable [ $K; Value:1 ]
                                     Set Field [ Zips::Category; GetValue($CategoryList ; 1) ]
                                     Set Error Capture [ On ]
                                     Perform Find [  ]
                                     Loop
                                     Set Variable [ $K; Value:$K + 1 ]
                                     Exit Loop If [ $K  >  ValueCount($CategoryList) ]
                                     Enter Find Mode [  ]
                                     Set Field [ Zips::Category; GetValue ($CategoryList ; $K) ]
                                     Extend Found Set [  ]
                                     End Loop
                                      

                                      

                                • 13. Re: Sorting and filtering records
                                  philmodjunk

                                       I would have used the simpler version of the script--the one that does not use extend found set, but what you have posted looks correct--which raises the question as to whether your data is correct or not.

                                       What you show still fail if Category does not have the global storage field option specfied. (Defining a field in at table named Globals doesnt' give it global storage.)

                                       It will also fail if the value in Globals::Category do not match the values in Zips::Category. or if the two fields are not defined to have the same data type--presumably text.

                                       If none of that reveals the issue, you can use FileMaker Advanced to run this script with the script debugger enabled and watch to see where it fails step by step. If you do not have Filemaker Advanced, you might put a pause/resume script or Show custom dialog step right after the perform find and extend found set steps to see what data is present in the Globals Category, $K and $categoryList variables each each point.

                                  • 14. Re: Sorting and filtering records
                                    hanstrager

                                         Sorry was one of my globals that where wrong...

                                         My main problem is though that i have all different search fields so I can filter my records. I have one for date, one for season, a category (check boxes), Seller, Payer.

                                         My problem is if I want to first filter/search on all categories at the same time, then it only searches on the latest box i've put put something into..? Isn't there a way so I can combine all the different boxes..?

                                    1 2 Previous Next