10 Replies Latest reply on Mar 8, 2015 6:28 AM by TracyBogans

    Thinning out Portal Record



      Thinning out Portal Record


      I have a table (Outside Source -ODBC)  that contains ticket information example

      ID, UserName, TicketNumber, Description, SystemName, DateTime,                  Status.... and so on.

      1  ,        tcb ,       123,              bahbah1,                PC,    2/2/2015 12:01PM,    open

      2  ,        tcb ,       123,              bahbah2,                PC,    2/12/2015 2:23PM,     open

      3  ,        smc ,     123,              bahbah3,                PC,    2/22/2015 2:01AM,      open

      4  ,        pat ,       123,              bahbah4,                 PC,   2/27/2015 11:01AM,    closed

      5  ,        smc ,     124,              bahbah3,                PC,    2/28/2015 2:01AM,      open

      6  ,        tcb ,       124,              bahbah4,                 PC,   3/1/2015 11:01AM,    closed

      I want to create a portal(1st choice) or report(if portal cant be done)  that will show the first ticket and the latest record. (Start off Ticket and lastest update)  . A ticket could stay open for 2 weeks and have 30 records tied to that ticketNumber. ID Field is a field that I added to this imported data.. Auto-Enter Serial (Text field)

        • 1. Re: Thinning out Portal Record

          If you only want to see the oldest and newest portal records, I'd use two portals to the same table set to show only one portal row each. Sort the first portal in ascending order by your dateTime field, sort your second portal in descending order by the same field. This suggestion assumes that DateTime is either a time stamp field or two fields, one time and one date.

          • 2. Re: Thinning out Portal Record

             My user that I am viewing may have more than one ticket open ... so How do I see all of  user - "tcb" tickets in one view with two portals one row each ?  ( OR  maybe a view by tickets.. again to only see first and last)


            tcb has 10 open tickets with different ticket numbers... want to see all of his  tickets in that view.. but one line per ticket

            ID, UserName, TicketNumber, Description, SystemName, DateTime,                  Status.... and so on.

            1  ,        tcb ,       123,              bahbah1,                PC,    2/2/2015 12:01PM,    open

            4  ,        pat ,       123,              bahbah4,                 PC,   2/27/2015 11:01AM,    closed


            5  ,        smc ,     124,              bahbah3,                PC,    2/28/2015 2:01AM,      open

            6  ,        tcb ,       124,              bahbah4,                 PC,   3/1/2015 11:01AM,    closed

            • 3. Re: Thinning out Portal Record

              Hmmm, let's try this...

              Your current relationship, but with different names would seem to be:


              If we set up a second occurrence of the Portal Table and link it like this:


              With these match fields:

              PortalTable::TicketNumber = PortalTableSameTicket::TicketNumber

              You could use this portal filter:

              PortalTable::ID = PortalTableSameTicket::ID OR
              PortalTable::ID = Last ( PortalTableSameTicket::ID )

              To limit the visible entries to the first and last for each ticket.

              • 4. Re: Thinning out Portal Record

                Sorry, having a like problem seeing /understanding this solution. .. Remember all of my data is in one table.( So user may have 10 different ticket numbers within this one table and each ticket may have 4 or more entries.

                So everytime an alert comes in that is the same  or similar alert it is added to the main table: 

                ID  Username  Ticketnumber         Description ............................... Ststus                TimeStamp

                1    tcb                TN001               outage in bld #1                             open                      1/1/2015 4:00 pm

                2     tcb               TN001               Outage is due to weather                open                     1/1/2015  5:00 pm

                3     tcb               TN001               called support team                        open                     1/1/2015  6:30 pm

                4    tcb                TN002               water leak                                     open                      1/1/2015 4:00 pm

                5     tcb               TN002              called Plumberr                              open                     1/1/2015  5:00 pm

                6     tcb               TN001              support emailed files                       closed                    1/1/2015  6:30 pm

                  I have attached a sample of what you described in the above post.  Seem like I should have a additional table with just usernames  so I can get one occurrence of each username and build my portal with a user table ???... Sorry just not getting it

                • 5. Re: Thinning out Portal Record

                  My answer assumed that all your portal data was in one portal. This does not prevent you from creating a second occurence of that table. 

                  • 6. Re: Thinning out Portal Record

                    Sorry... Im not getting the same result .

                    I have a two tables  ( UserTable and a MainTable)   Relationship between these two table is the field Username.

                    I have  created two additional occurences of my MainTable. .... MainTablePortal and MainTableSameticket . Relationship between MainTable and MainTablePortal and MainTableSameTicket is field TicketNumber.

                    I have tried placing my Portal in the Layout  UserTable  with this filter on all portals:
                    MainTablePortal::id = MainTableSameticket::id or
                    MainTablePortal::id = Last( MainTableSameticket::id)

                    Trying to see the Highlighted portal records only in a Portal view

                    • 7. Re: Thinning out Portal Record

                      I see too many table occurrences here.

                      Your layout is based on UserTable. It appears that your portal refers to Main table. That should leave just one added occurrence of MainTable in your relationships chart, not two more.

                      Question, Can two users see records with the same ticket number but different user names? That would seem possible with your relationship graph. If so, then, you don't need another table for user names, but you would need two pairs of match fields to limit the related records to those of the same ticket and also the same user name.


                      UserTable::UserName = MainTable::UserName

                      MainTable::UserName = MainTable|SameTicketUser::UserName AND
                      MainTable::ticketnumber = MainTable|SameTicketUser::TicketNumber

                      Your layout would be based on UserTable, your portal would refer to MainTable and the portal filter expression would refer to MainTable|SameTicketUser.

                      MainTable::ID = MainTable|SameTicketUser::ID OR
                      MainTable|SameTicketUser::ID = Last ( MainTable|SameTicketUser::ID )

                      My assumption here is that you need the user name to keep from matching to records of the same ticket number that have different user names. If this is not the case, you can omit the first pair of match fields that I specified in the relationship. I am also assuming that MainTable::ID refers to a field that uniquely identifies each field in MainTable such as you get with an auto-entered serial number or a Get ( UUID ) generated string of text.

                      • 8. Re: Thinning out Portal Record

                        Sorry,.. Maybe I need to stop working on the weekends. Still not getting results.

                        And yes to your question... In Another portal I need the user to see who has updated his existing tickets.  Example:

                        I starting working ticket # 123  Saturday Morning... make three updates 8:30am, 9:00am, and 9:30am So in my One Portal I would see my updates of 8:30am and 9:30AM  

                        (If this is something that can't work I can work with the last to updates 9:00am and 9:30am ..Last two most resent Updates)

                        You work the same ticket on Sunday. making update at 2"00 2:30 and 3:30pm

                         in another portal  of related updates  I will see you lastest update. to this ticket at 3:30PM

                        • 9. Re: Thinning out Portal Record

                          But what is not clear is whether or not two users will need to see just their tickets out of a set of records with the same ticket number.

                          But there was an error in my last post. I mixed up a table occurrence name. I should have posted this as the portal filter expression:

                          MainTable::ID = MainTable|SameTicketUser::ID OR
                          MainTable::ID = Last ( MainTable|SameTicketUser::ID )

                          Sorry about that.

                          • 10. Re: Thinning out Portal Record

                            Thanks.   Portal is working great.

                            Yes. to your question... Users will need to see if other users have updated their tickets with the same ticket number. I was going to setup a separate portal for that.  Basically ,     I want the user (Owner of the ticket) to see any updates to his tickets