10 Replies Latest reply on Feb 23, 2017 10:15 PM by petey

    Custom Popup Values to Filter List View Records

    petey

      Hey! First post!

       

      I've learned sooo much from this forum... You guys rock!

       

      (Using FM 15 Pro Advanced on a Mac)

       

      GOAL

      Popup button that displays a custom list, which in turn sorts records in list view.

       

      DATA

      I have a table with fields for school dates ( Date ) , locations, prices, etc.

       

      There is a data entry layout and a list view layout. In list view layout, the users will only want to see the current year's list of schools. There will be a date on every record, of course. For example:

       

      2/2/2016

      3/7/2016

      6/6/2016

      4/22/2017

      5/28/2017

       

      In list view, I want a popup in the header that just shows the years, with no duplicates:

       

      2016

      2017

       

      Once the year is selected, I presume there will be a script trigger that sorts the records. That part I can probably do. But creating the custom list, without duplicates has been a nightmare. (I'm new, haha). I've come close.

       

      I've tried:

       

      1. Right ( Date ; 4) -----> but this just gives me one record with the first part of the date stripped, but not all related records.

       

      2. List (Related::Date) ----->FM doesn't like the word "Related" and won't allow me to enter the calc. I must be missing something here!

       

      3. Summary field set to list ( Date ), called sum_date  ------>This lists all records nicely. I created another calc field with: Right ( sum_date ; 4 ) ----> just shows one record.

       

      4. Used a conditional formula on the sum_date: Right (Self ; 4). No go.

       

      Any help would be appreciated.

       

      Cheers,

       

      Pete

        • 2. Re: Custom Popup Values to Filter List View Records
          philmodjunk

          "2. List (Related::Date) ----->FM doesn't like the word "Related" and won't allow me to enter the calc. I must be missing something here!"

           

          Where you are using "related" you have to use the name of a table occurrence--that's a "box" on the relationship graph. And it must be linked to the table occurrence specified as the context for your calculation or the layout's table occurrence if used in script step. This still won't work for what you want, but that's why it "didn't like" the word 'related'.

           

          As Mike suggested, year (date) can be defined in a calculation field in the same table as your dates. Your value list can then list values from this calculation field.

          • 3. Re: Custom Popup Values to Filter List View Records
            petey

            Thanks for the quick response!

             

            Mike, I wasn't aware of that function, thanks. However it just shows one record.

             

            philmodjunk, that's what I figured, but there must be something wrong with my relationship. (I'm still working up to a complete understanding on how that all works.)

             

            All the data is in the same table. Do I need to create a self-join relationship? I thought I did, but must have messed something up.

             

            I created a number field: ID_School_Dates (indexed, auto-serial)

            I created a number field: id_schooldates (indexed)

            Then I created a new TOC for the table and linked the two fields together with the = sign.

             

            Where's my mistake?

             

            Here's some screenshots:

             

            toc.png

             

             

            field.png

             

             

            calc_year is set to: Year ( Date )

            I also tried: Year ( sum_date )

             

            -Pete

            • 4. Re: Custom Popup Values to Filter List View Records
              philmodjunk

              You don't need a relationship just to get a value list of years.

               

              You can use a relationship that matches a number field formatted with the value list to the calculation field that computes the year if you use a script with Go To Related Records to pull up your found set. But you can also set up a script to perform find and then no added relationship is needed.

               

              And are you sure that T34 and T34c are occurrences of the same table? The way that the relationship line connects to each occurrence box suggests that they aren't.

              • 5. Re: Custom Popup Values to Filter List View Records
                petey

                I didn't think so.

                 

                I don't think my knowledge is sufficient enough yet, to track with you regarding those script options. Are you suggesting I use a script trigger??

                 

                Yeah, they are the same...I expanded them:

                 

                Screen Shot 2017-02-22 at 9.42.39 PM.png

                 

                It's kind of hard to build understanding when something doesn't respond the way you expect it. For example, the FM manual gives this example for the list function:

                 

                List(Field3) returns:

                red

                green
                blue

                 

                So in a new field calculation called "test," I put in: List ( Date1). It doesn't list. It just shows one record. But a summary field will list. However, I've been struggling to find a way to filter that last.

                 

                Screen Shot 2017-02-22 at 9.43.48 PM.png

                 

                I'm obviously missing something. Appreciate the help and an ear to my rant.

                 

                Pete

                 

                • 6. Re: Custom Popup Values to Filter List View Records
                  philmodjunk

                  You don't need list for this. Do you know how to set up a "use values from field value list"? That's the first step here.

                   

                  While you don't need it here, List, if you go back and check again is normally used like this:

                   

                  List ( RelatedTable::Field )

                   

                  And then it lists all values from field over the set of related records. It won't drop out duplicates and we don't need it here for our value list.

                   

                  There's a simple scripted find that can work from that popup menu via a script trigger. That's where I'm headed here, but first to get that value list of years. Do you know how to do that?

                  1 of 1 people found this helpful
                  • 7. Re: Custom Popup Values to Filter List View Records
                    petey

                    Okay, that makes more sense.

                     

                    No, I don't know. Is this it?

                     

                    Screen Shot 2017-02-22 at 10.31.07 PM.png

                    Okay, I see where you're headed...Just need to figure out the list part. lol

                     

                    Pete

                    • 8. Re: Custom Popup Values to Filter List View Records
                      petey

                      I...we did it.

                       

                      Here's what I did (in case anyone else comes across this) :

                       

                      1. Created a calc field called Date_Filter, with calc: Right ( Date1 ; 4)

                      > This gave me just the year, e.g 2016, 2017.

                       

                      2. Created a value list from Date_Filter.

                      > Magically, this gave me a list without duplicates!

                       

                      3. Created a global field called gFilter_year.

                       

                      4. Used gFilter_year on the layout and set it as a dropdown for the value list created on step 2.

                       

                      5. Wrote the below script to execute on OnObjectSave, and boom! It worked.

                       

                      Script:

                       

                      Allow User Abort [ off ]

                      Enter Find Mode [ Pause: Off ]

                      Set Field [mytablename:Date_Filter ; mytablename::gFilter_year]

                      Set Error Capture [ On ]

                      Perform Find []

                      Go to Record/Request/Page [ First ]

                      Refresh Window [ ]

                      Scroll Window [ Home ]

                       

                      Once you got me thinking about how to do this, I just about nailed it, bur had to reference another post (which you just wrote), to figure out the script. Here's the post: Perform Find based on value selected from drop-down menu

                       

                      Big thanks!!

                       

                      Pete

                      • 9. Re: Custom Popup Values to Filter List View Records
                        philmodjunk

                        . Created a calc field called Date_Filter, with calc: Right ( Date1 ; 4)

                         

                        This where Mike and I recommended using:

                         

                        Year ( Date1)

                         

                        It's bit clearer that way that you are getting the year portion of the date in your calculation.

                        • 10. Re: Custom Popup Values to Filter List View Records
                          petey

                          After needing to combine some dates in another field, I can see now, why that's better.

                           

                          Cheers!