11 Replies Latest reply on Mar 26, 2014 12:08 PM by philmodjunk

    Portal filtering by date - help

    dinoapolito

      Title

      Portal filtering by date - help

      Post

           Hi,

           For the life of me I can't get this to work.

           I am trying to filter portal records according to a global date field. My portal has a field called OrderDate in a table called Orders and the global date field is called gDate in a table called Globals.

           Here is my formula for portal filtering

           If ( Orders::orderdate = Globals::gDate ; 1 ; 0 )

           It returns no rows.

           What am I missing?

            

            

            

        • 1. Re: Portal filtering by date - help
          symbister

               the expression box is headed:

               "Each portal record will be visible when:"

               So your filter expression should just be Orders::orderdate = Globals::gdate

               but where does gdate get its value ?

                

          • 2. Re: Portal filtering by date - help
            dinoapolito

                 Thanks, I actually tried that but it still doesn't quite work. To simplify I tried to just 'hard code the date I need.

                 I have 5 dates in the portal

                 14/09/2011

                 16/05/2012

                 23/05/2013

                 If I enter

            Orders::orderdate = "14/09/2011"   ---it doesn't work. I get no rows in the portal

                 If I enter 

                 Orders::orderdate < "16/05/2012"  -- it works. I get a single row with the 14/09/2011 record

                 It seems the > and < operators work but the = does not

                 In reply to your question gDate comes from user input. Likewise if I use

            Orders::orderdate = Globals::gdate ----it doesn't work 

            But if I use > or < then I get results

                  

                  

            • 3. Re: Portal filtering by date - help
              symbister

                   ah

                   I recall that once I had to 'deconstruct' the Date into month, day, year, then do the comparison with the Date(m,d,y) function

                   Orders::orderdate = Date (month(gdate), day(gdate), year(gdate) ) always assuming that gdate is defined as type date :) 

                   or set up a new TO with a relationship linking gdate = orderdate, then use this in the portal ?

              • 4. Re: Portal filtering by date - help
                dinoapolito

                     Bingo!

                     I just realised it was a case of the date format. Thanks for your help.

                     But ....

                     I would rather have my portal display only rows that are a result of a SQL query eg.

                     Select orderid, orderdate, productcode, qty

                     from orders

                     where order date = gDate and ...; etc

                     Basically for a given customer - the table that the layout is based on - I want the portal to only show SOME records from the related orders table.

                     I know SQL better than FMP and could do it in a jiffy with SQL but I don;t know how to implement that in FMP

                      

                • 5. Re: Portal filtering by date - help
                  symbister

                       sorry Dino - I'm not up to SQL - yet!

                  • 6. Re: Portal filtering by date - help
                    dinoapolito

                         Thanks for your help. SQL is great but I don;t know how to use it in FM

                         Can I indulge one more time??

                         I can make the date filtering work now that I know the date formats have to match. But here's the problem.

                         Remember the date will come from another field

                    Orders::orderdate = Globals::gdate

                    But.. that only works if the there is a date in Globals::gdate. I want the portal to filter by that date if there is a date inputed into gDate but if there isn't a date then I want the portal to show all records (all dates).

                         I can't achieve that. If gDate is empty the filter will show no records. I want it to show all records

                          

                          

                    • 7. Re: Portal filtering by date - help
                      symbister

                           OK, then your filter expression could be:

                           If (Isempty(Globals::gdate) ; Orders::orderdate =/ Globals::gdate ; Orders::orderdate = Globals::gdate ) (=/ means not equal to)

                           I don't have a sample set up to try this, but I suggest that this could be the go

                            

                      • 8. Re: Portal filtering by date - help
                        dinoapolito

                             It doesn't like the =/

                             Do I need to escape it or quote it? Says its expecting a 'number, text constant etc here'

                        • 9. Re: Portal filtering by date - help
                          symbister

                               you need to use the 'not equal' symbol in the expression builder, see screen shot....

                               (can't type that in here )

                          • 10. Re: Portal filtering by date - help
                            dinoapolito

                                 DOH

                                 I tried it but still no luck. I get no records regardless of whether there is something in gDate or not

                            • 11. Re: Portal filtering by date - help
                              philmodjunk

                                   Seems to me that you need this expression:

                                   Is Empty ( Globals::gDate ) OR Globals::gDate = Orders::orderdate