1 2 Previous Next 16 Replies Latest reply on Feb 25, 2017 10:29 AM by samarthmkt

    Filter portal by timeframe

    samarthmkt

      Hello Friend,

       

      I have an layout name Pos Invoice asscioate with two portals by tab control  (One is name as stock status and another one is Pos invoice transaction details). Every thing is ok only thing is that i could not getting all records of all transactions invoices ,only getting per invoice transaction records. I want all records on the portal table and also want to filter the table by timeframe ( Monthwise, weekwise, daywise. lastmonthwise , lastweekwise etc) how we can figure this thing. It will be highly gratefull for figurring out this query.

       

      ThanksScreenshot (20).pngScreenshot (21).png

        • 1. Re: Filter portal by timeframe
          jbrown

          Every thing is ok only thing is that i could not getting all records of all transactions invoices ,only getting per invoice transaction records. I want all records on the portal table and also want to filter the table by timeframe ( Monthwise, weekwise, daywise. lastmonthwise , lastweekwise etc)

          Good morning.

          Let me make sure I know what you want:

          1. You want all the transactions to show up in the portal, not just the transactions for that one invoice.

          The portal you have must be based on a relationship that shows only that invoice's transactions. That is good for an invoice layout. To get what you want, you'll need to create ANOTHER relationship that says "Show me all the transaction records regardless of the invoice". To do that you need to set up the relationship using the X or Cartesian Join. This says Everything on the left is related to everything on the right.

          This will show ALL the transaction records from EVERY invoice.

           

          2. You want to filter the portal to show only a time frame's transactions.

          To do this you can set up a global field in this invoice table that is used in the portal's filter function. There's many things you can do with this. You can set up the global field to be a date field. the user enters a date, and the portal shows from that date to today.

          In the portal filter setup, you'd enter something like ">=" & YourTable::YourFieldGlobal. This will show all records that are greater or equal to the date you entered. That is a basic set up.

          To be more advanced, you'd create two global date fields and use those in the portal filter, using this formula:  ">=" & YourTable::StartDate_Global and "<=" & YourTable::EndDate_Global

           

          Try those suggestions. I'll put together a quick demo file.

          • 2. Re: Filter portal by timeframe
            beverly

            How I've handled this:

            Have a start_date_g and end_date_g. Use these in the filter along with any other criteria (with AND). If both global fields are empty, then ignore the timeframe.

            Then by script set these globals.

            For This month:

            Set Field ( start_date_g ; Date ( Month ( Get ( CurrentDate ) ) ; 1 ; Year ( Get ( CurrentDate )) )

            Set Field ( end_date_g ; Date ( Month ( Get ( CurrentDate ) ) + 1 ; 0 ; Year ( Get ( CurrentDate )) )

             

            NOTE: putting a "0" day into next month, returns the end day in this month.

             

            Do the same for other timeframes.

             

            Since "today" is only one day, you can set both fields to Get ( CurrentDate )

             

            Filter:

            if ( not IsEmpty ( start_date_g & end_date_g ) ) ;

            mydate >= start_date_g

            AND

            mydate <= end_date_g

            ) ; "" )

            ....

             

            beverly

            • 3. Re: Filter portal by timeframe
              beverly

              p.s. a publicly available example of where I filter a portal by month & year:

               

              There are a couple techniques for showing "all", "none" when you want. Check the filter in the second section (layout one) "Filtered by"

               

              Case

                ( salesperson::no_filter = 1 ; not IsEmpty ( sales::salesID ) // all related

                ; not IsEmpty ( salesperson::filter_date ) ; sales::year_month = salesperson::filter_date  // filter by year_month

                ; "" // no related

                )

              This just uses and auto-enter year_month value and relationship, but could apply to the date 'range' as I outlined earlier.

              1. a checkbox (no_filter) is true, then show all records as if there is no filter

              2. if there is a value in the global, use it and filter

              3 if there is no value in the global, nothing 'matches' and the portal is empty

               

              of course, you can have your own variation. For some developers, an empty global would mean show all (not filtered)

              beverly

              1 of 1 people found this helpful
              • 4. Re: Filter portal by timeframe
                samarthmkt

                Thanks for your reply. Sorry for late on the response of your comments. Though i want to ask one thing what will be the calculation in the portal table when we filter the portal table by value list ( Drop Down List)  i.e. For week transactions i have two value list ( Current week and last week) Filter selection field for weeks transactions.Present i have portal filter calculations only for current week . So i also want for last week also in the same portal table filter. The same i want in day transactions also ( Current Day and previous day) Some pics for the same

                Thanks

                 

                Screenshot (23).pngScreenshot (24).pngScreenshot (25).png

                • 5. Re: Filter portal by timeframe
                  beverly

                  you can have a drop down list with any text you need:

                  Last Week

                  This Week

                  Last Two Weeks

                  Last Month

                  This Month

                  ....

                  These are just clues to what you put into your start and end date field (or variables) - which will be used to filter the portal.

                  Case

                    ( reportType = "Last Week" ; Let ( $start = <<calcHere>> ; $end = <<anotherCalc>> )

                     ; reportType = ....

                     ; Let ( $start = Get ( CurrentDate ) ; $end = Get ( CurrentDate ) // default is today )

                  )

                  All you are doing is selecting a range-by-label, then supplying the calcs for the filter based on the selection and any other fields you need in your filter.

                   

                  beverly

                  • 6. Re: Filter portal by timeframe
                    samarthmkt

                    Thanks Friend for your reply,

                    Here I go through your clue but unable to get filtered records by select value from value list of dropdown list. I have put the let function in portal table filter option. Here are some pics for the same. One thing I want to ask is there we have to put any more calculation filter transaction field. at present is only text .

                    Thanks

                    Screenshot (7).pngScreenshot (8).png

                     

                    Screenshot (5).pngScreenshot (6).png

                    • 7. Re: Filter portal by timeframe
                      beverly

                      It's difficult to see where you are using the _value_ of the drop down in your calculations to get the range for the filter. You are triggering a script to "refresh", but that doesn't mean it's used in the calc.

                       

                      beverly

                      • 8. Re: Filter portal by timeframe
                        samarthmkt

                        Thanks for your reply,

                        I simply want to filter the records of portal table by the dropdown list (Filter Transactions) i.e. if I select Today I should get record of today only and the same if I select other values records should filter as per selections. Here I am little bit confusing where to put the calculation to get the result. I am attaching small demo file where I put the filter transaction dropdown field as well as portal table in layout filter transaction for filtering records. Pls assets me how it can be done by putting proper calculation in proper field or table.

                         

                        Thanks

                        • 10. Re: Filter portal by timeframe
                          beverly

                          understandable. But selecting the TEXT 'today' does nothing to the calc, unless you have a case statement as I suggested. I do not see your calc.

                           

                          I don't download .rar files. post as .zip or just the database. And post to the forum, not a dropbox, please.

                          beverly

                          • 11. Re: Filter portal by timeframe
                            samarthmkt

                            Ok I am Sending the zip file but unable to attach zipfile there is no such option in reply box. Only lnsert link option is there. So pls consider the drop box link.If you permit

                            Thanks

                            • 12. Re: Filter portal by timeframe
                              BruceRobertson

                              You CAN attach files here.

                              You must have noticed that people do it all the time.

                              Note the "Use advanced editor" link at top right in the reply dialog.

                              • 13. Re: Filter portal by timeframe
                                samarthmkt

                                Thanks Friend,

                                Here is zip attach file

                                • 14. Re: Filter portal by timeframe
                                  samarthmkt

                                  Pls give me support on this small issue which I have described above.Yet I have attached the small demo zip file above also. Still I could not get the values for each timeframe zone or list. I know that you have go through on above issue . I hope you will give the help on this issue or make calculation in my zip file .

                                  Thanks

                                  1 2 Previous Next