11 Replies Latest reply on Dec 14, 2016 3:06 PM by sansae

    Filter records using three fields WITHOUT a portal

    sansae

      Hi,

       

      It seems most of the posts regarding filters deal with portals. I'm not using a portal in my layout.

       

      My goal is to filter my zipcode records based on 1) a date range and 2) a service type.

      What I currently have is a layout with 3 fields: date_begin, date_end, service_type.

      My service_type field is currently a drop-down list that contains various values/services.

       

      What's working is the filter by date range. I'm not sure how it works. It just does. I've used data viewer to track my variable $listZips by changing the date ranges, and the values returned are exactly as expected.

       

      What's not working is the filter by service type. What I want is, after filtering by date, I would like to see the zipcodes that provided a particular service type. So if I choose "plumming" from the drop-down, and in my records there are 5 zipcodes that provided this service from <date range>, then I should expect to see (in the data viewer) only those 5 zipcodes after selecting plumming.

       

      Can anyone help me? General pointers/tips on how to do this type of filter?

      (I would love it if anyone can point me to a resource that talks about the topic of filtering records in filemaker. All the online resources I've found talk about filtering as it relates to portals. But what if I want to filter my records without using a portal?)

       

      Other info:

      My fm experience is limited (6 weeks)

      Windows 7

      FM Pro 15 Advanced

       

      Thank you in advance.

        • 1. Re: Filter records using three fields WITHOUT a portal
          erolst

          “Filtering” records in FileMaker is simply called “Find”, or “Performing a Find request”.

           

          sansae wrote:

          What's not working is the filter by service type. What I want is, after filtering by date, I would like to see the zipcodes that provided a particular service type. So if I choose "plumming" from the drop-down, and in my records there are 5 zipcodes that provided this service from <date range>, then I should expect to see (in the data viewer) only those 5 zipcodes after selecting plumming.

          That wouldn't work as you expect it to … because in the Data Viewer the expression “Table::field" only returns the field's value from the first record (“native” or related).

           

          You may want to look into “summary reports”, or at least “summary fields”; a summary field of type “List of:”, pointed at the zip field, would return the desired result for the found set.

          • 2. Re: Filter records using three fields WITHOUT a portal
            sansae

            Hello again erolst,

            Either you work here full-time, or you're super generous with your time (or both).

            Whatever the case, thank you for the response.

             

            I guess I'll enter find mode in my script and go from there.

             

            I've performed several finds in my previous tasks, but these finds were done on records that were displayed in my layout. I currently do not have any records on my layout. My layout is one of those "single-record" layouts where the idea is, from that layout, you can 1) see all zipcode records, and 2) filter those records by entering the date range and service type.

             

            thanks.

            • 3. Re: Filter records using three fields WITHOUT a portal
              sansae

              Somehow I missed these last two paragraphs:

              "That wouldn't work as you expect it to … because in the Data Viewer the expression “Table::field" only returns the field's value from the first record (“native” or related).

               

              You may want to look into “summary reports”, or at least “summary fields”; a summary field of type “List of:”, pointed at the zip field, would return the desired result for the found set."

               

              I did a summary report using sub-summary parts, which worked great, but supervisor wanted something a bit more "dynamic" and "flexible". So he suggested this one record layout approach where we can "generate" a report by various criteria (i.e. service type, department, salesperson, etc.)

              • 4. Re: Filter records using three fields WITHOUT a portal
                erolst

                sansae wrote:

                My layout is one of those "single-record" layouts where the idea is, from that layout, you can 1) see all zipcode records, and 2) filter those records by entering the date range and service type.

                But in that case, you are working with a portal that is directed at the zip table.

                 

                From what I understand, you're wanting a layout that is

                 

                - based on the zip table, and

                - is in List view (a "multi-record" view, as opposed to "Form view' ie just a single record at a time

                 

                Maybe read up on List vs Form view, and how you can summarize the records of a found set usung sub-summary parts, summary fields and rüthe correct sort order.

                 

                Also search the forum for "scripted find"; there should be a number of posts out there, some even with sample files.

                1 of 1 people found this helpful
                • 5. Re: Filter records using three fields WITHOUT a portal
                  sansae

                  I should clarify, in case I'm misunderstanding you. My layout is actually a form view layout, with only one record, literally. That one "record"/layout has the two fields for selecting a start date and end date, and one field for selecting the service type.

                   

                  Ideally, there will be some sort of magic button at the end of all of this that will have my script attached to it. The user will enter the date range and other criteria (e.g. service type, etc.), then click on that magic button and a report will be generated in the form of an email, excel, pdf, or etc.

                   

                  Thank you for the scripted find suggestion. I'll definitely do a search for that right now.

                  • 6. Re: Filter records using three fields WITHOUT a portal
                    erolst

                    sansae wrote:

                     

                    Ideally, there will be some sort of magic button at the end of all of this that will have my script attached to it. The user will enter the date range and other criteria (e.g. service type, etc.), then click on that magic button and a report will be generated in the form of an email, excel, pdf, or etc.

                     

                    Thank you for the scripted find suggestion. I'll definitely do a search for that right now.

                     

                    The "magic& script attached to the "magic button" would be like so (semi-pseudo code with assumed field names):

                     

                    - perform error checks (at least one date field filled in, or all three filled in, according to your own rules)

                    - if check fails, alert user, exit script; else proceed

                    - open new window

                    - change to zip layout

                    - enter find mode

                    - set date range

                    - set type field

                    - set error capture on

                    - perform find

                    - if nothing found, close window, exit script; else proceed

                    - sort according to intended display

                     

                    Regarding the last point you should know that sub-summary parts only appear if their break field is in the current sort order. So you could create an all-purpose layout and, with a bit of trickery, make different parts appear or disappear (you could even create a faux body part...). Of course, if all else fails, you could still create different layouts ...

                     

                    But know also that you could use a relationship where

                     

                    GlobalTable::gDateBegin <= Zip::theDate

                    GlobalTable::gDateEnd >= Zip::theDate

                    GlobalTable::gType = Zip::type

                     

                    if you use for gType a checkbox control to select multipe types, this would even work as an OR filter, ie related records are those were the date is in the range and the type matches any of the ones selected.

                     

                    You could display that related set in a portal and summarize it (or only summarize it)!, and of course perform a Go to Related Record to see a list (eg for printing).

                     

                    Endless possibilities ...

                    1 of 1 people found this helpful
                    • 7. Re: Filter records using three fields WITHOUT a portal
                      sansae

                      I was thinking along the same lines as you in creating an "all-purpose" layout and hiding the different parts. But the suggestion was made to create the one record layout (without the use of subsummary parts) as part of my training, along with the usage of variables and such. Up until now, I hadn't used variables in my previous scripts

                       

                      I'll look over the pseudocode. Thank you!

                      • 8. Re: Filter records using three fields WITHOUT a portal
                        sansae

                        "GlobalTable::gDateBegin <= Zip::theDate

                        GlobalTable::gDateEnd >= Zip::theDate

                        GlobalTable::gType = Zip::type

                         

                        if you use for gType a checkbox control to select multipe types, this would even work as an OR filter, ie related records are those were the date is in the range and the type matches any of the ones selected."

                         

                        What you said there made me re-think how my table occurrences were related to one another. The first two pair of fields (dates) you mentioned are actually the two fields that are used to form the relationship between both of my TO's. What I ended up doing was, in addition to those two paired fields, I paired up my filter_service_type field to a service_type field using an "=". Voila! It worked. (although I'm still struggling to understand how it all works. I wish I could visualize what's going on)

                         

                        Thank you!

                        • 9. Re: Filter records using three fields WITHOUT a portal
                          sansae

                          erolst wrote:

                           

                          sansae wrote:

                          My layout is one of those "single-record" layouts where the idea is, from that layout, you can 1) see all zipcode records, and 2) filter those records by entering the date range and service type.

                          But in that case, you are working with a portal that is directed at the zip table.

                           

                           

                          Please correct me if I'm wrong:

                          Are you saying that in filemaker, we don't have to necessarily use the Portal Tool to create a portal object in order to use a portal? That is to say, we can create a layout and have it act as a portal?

                          • 10. Re: Filter records using three fields WITHOUT a portal
                            erolst

                            sansae wrote:

                            Are you saying that in filemaker, we don't have to necessarily use the Portal Tool to create a portal object in order to use a portal? That is to say, we can create a layout and have it act as a portal?

                            You're wrong; consider yourself corrected.

                             

                            But you're only wrong because I was wrong. When I said "then you are using a portal" I was not making use of a metaphor, but merely drawing a wrong conclusion - I thought there was a honest to goodness portal on your layout.

                             

                            You can do this: ask yourself what a portal does and what features it provides - what makes a portal act like a portal - and what a layout does.

                            • 11. Re: Filter records using three fields WITHOUT a portal
                              sansae

                              erolst wrote:

                              I thought there was a honest to goodness portal on your layout.

                              Nope. No portal.

                               

                              erolst wrote:

                              You can do this: ask yourself what a portal does and what features it provides - what makes a portal act like a portal - and what a layout does.

                              Well, I know that they both show stuff.

                              haha

                               

                              I found this:

                              "Records from another table are displayed using a FileMaker Pro layout object called a portal. A portal displays the data of related records in rows with one row in a portal representing one related record in the related table."

                               

                              As far as "features" are concerned, I'm not sure. Those portal objects probably provide a lot more than what I've seen so far.

                               

                              Why does it act the way that it does? Maybe a literal definition of the word portal would help explain it? A portal is a gate that leads you to something. idk. i'm talking out of my arse right now.

                               

                              next step: figure out how to filter records when users want to select more than one service-type. piece of cake.

                               

                              thanks again, erolst.