7 Replies Latest reply on May 12, 2011 9:27 AM by philmodjunk

    filter a portal using week and year

    Kays

      Title

      filter a portal using week and year

      Post

      heyy,

      i ve got a table "weekly status" containg : week, year and rate

      i created a portal, and i wanna show the max(week,year) from the table "weekly status", i don't think uts possible cuz how u could return the max of 

      week = 50, year = 2010

      and

      week = 15, year = 2011

      in the filter option of the portal , i thought about something like date (1;week;year) = max (date(1;week;year)), it makes sence but FileMaker doesn't accept caculations in a Max Function.

      ne help !!

      K|Z 

        • 1. Re: filter a portal using week and year
          philmodjunk

          Calculations can be used inside a Max function, but date ( 1 ; week ; year ) = Max ( date ( 1 ; week ; year ) ), will always return true as you are computing the Max of a single value and then comparing it to that single value.

          Here's a date calculation that returns the date for Sunday of each date in the week that you may find useful:

          DateField - DayofWeek ( DateField ) + 1

          If you just have one record per week, you can either just enter the Sunday date into this week field or use the above expression in an auto-entered calculation to change any date entered into the nearest preceding Sunday date.

          You can use this field to group all records by week and it's also a value that should work with your use of the Max function.

          Since this is a date, it will be distinct for the week and the year. It also makes dates from weeks that start in December and ends in January part of the same week--which may or may not be what you want here.

          • 2. Re: filter a portal using week and year
            Kays

            okiii thx, :) 

            but is there a way to type directly into the portal filter custom box without adding extra fields or changing the database,

            a calculation that allows us to show only the latest week,year record, just one record, the latest

            K|Z  

            • 3. Re: filter a portal using week and year
              philmodjunk

              Are week and year number fields?

              Sort your portal by Year, then by Week. The most recent records will be listed at the top. If you then limit the portal to a single row, you'll see only the most recent record.

              • 4. Re: filter a portal using week and year
                Kays

                actaually i thought about that, but its not what i'm lookin for

                the whole idea is to have the portal showing only 2 records, then filter it to show only the latest record, in that way the second record in the portal will allow us to create a new record 

                K|Z

                • 5. Re: filter a portal using week and year
                  RestaurantCharlie

                  I had a similar problem with week/year combinations and decided on concatinating the two. So for week 1 of 2011 I would get 201101, that way you  can max, sort, etc and get proger results.

                  In my solution I created a field with that value, so I haven't tried putting the calculation directly into the portal filter, but it is worth giving it a try.

                  max(getasnumber(year&If(length(week)=1;"0"&week)))

                  If you just want the most recent week to show up or a number of the most recent weeks, you can probably also use the sort option in the portal setup sort by year then week descending, and in the filter calculation if getNthRecord≥1 and getNthRecord≤4(or whatever number of weeks you wanted).

                  • 6. Re: filter a portal using week and year
                    Kays

                    actaually i thought about that, but its not what i'm lookin for

                    the whole idea is to have the portal showing only 2 records, then filter it to show only the latest record, in that way the second record in the portal will allow us to create a new record 

                    K|Z

                    • 7. Re: filter a portal using week and year
                      philmodjunk

                      Then You'll either need to use one portal row and use other means for adding the new portal records or you'll need to modify your database with fields that will work the way you need them to. Restaurant Charlie has suggested one method, I've suggested another.