14 Replies Latest reply on May 13, 2013 12:10 PM by Carl.Simpson

    Query on Noticeboard Item

    kathi Apprentice

      I have several noticeboard items. For example, I have one for "Service Disruption", "Scheduled Maintenance", and "No Notices". On my self service logon screen (v.7.5) I have a gadget to show ITNS Notices and I have a query that shows all the noticeboard items that have not expired. What I would like to do is only show the noticeboard item messages for "No Notices" whenever there are no other noticeboard items (i.e. "Service Disruption" and "Scheduled Maintenance"). I have not been able to figure a way to filter the results. Please let me know if you have any suggestions. Attached is a sreenshot of my filter.

        • 1. Re: Query on Noticeboard Item
          karenpeacock SupportEmployee



          Perhaps there is an easier way but I thought this sounded in theory like it should work:


          Create a collection of notices on an object, for example the change object.  Create a change record which always remains open and have all your noticeboard items associated (as a collection) with this change.  Ensure that your "No Notices" noticeboard item has an expiry date in the past.  Create a calculation on the noticeboard object which sets a boolean called "No Notices?" to True or False.  This should check to see if the count of notices with an expiry date in the future = 0 and whether the current notice has a title containing "No Notices".  If both conditions are met then set the flag to True else leave set it to False.


          Then your query that you show on your dashboard looks for whether an article expiry date is in the future OR whether the "No Notices?" flag is set to True.




          • 2. Re: Query on Noticeboard Item
            kathi Apprentice



            Sorry for not replying sooner. I had several other issues that needed more attention; including upgrading 7.5 to 7.5 sp1. In theory it appears that what you suggested should work; however, it looks like it is a lot of work to just filter query results. Once I get enough time, I hope to be able to look into this more.


            Thanks again for your suggestion,


            • 3. Re: Query on Noticeboard Item
              aparker Employee

              Hi Kathi,


              Maybe I'm not really understanding what you're describing, but if you have a query that only shows items that are within their expiry date and the only one is the 'No Notices' item then that will be the only one that is displayed. Any others that are past their expiry date will be, by definition, not shown.



              • 4. Re: Query on Noticeboard Item

                Hi All,


                We are also looking to enhance our noticeboard. Problem is as always, user error. When we post a notice for an outage or scheduled downtime, we have to remember to manually expire the 'no notices' notice and then when all the messages expire, remember to go back in and alter the dates again on the no notices message. The functionality that we are after is a default 'no noitces' that automatically is shown when there are no other messages and is automatically removed when there is an active message. Very easy to do manually but seems to be forgotten by the analysts quite often!

                We are also looking to alter the noticeboard so that messages are posted as planned outages (via the category) with set times for actual outage so during this period it moves automatically to the current section from planned downtime section and then expires and disappears. I'm sure this can be completed with a few extra fields but when you factor in that there might be two periods of downtime is gets more complicated on the query design.....



                • 5. Re: Query on Noticeboard Item

                  I think a calculation will work for you.


                  Create a new Boolean attribute in the Notices object.  Set a variable to True, return false if the title is 'No outages', and then start looping through every record until you find a record that would show up on the notice board.  At that point set the variable to False and return the value.


                  In your query you show all notices that are in the date range to be shown OR all records where the boolean value is true.  You will end up with something like (1 and 2) or 3 where 1 is beginning date of notice >= today and 2 = end data of notice <= today and 3 = is boolean true.


                  All records will be false guaranteed if it's not the "No outages" record.  The looping looks to see if there are any valid notices that will show up. If there are it returns false otherwise the default value was true.  So only if no notices are in range would the result be true and therefore it would be the only record returned.


                  The tutorial on calculations has a section about stepping thru calculations that will help a lot.

                  • 6. Re: Query on Noticeboard Item

                    Oops, ya what Karen said although it really isn't that bad.

                    • 7. Re: Query on Noticeboard Item

                      Hi, Thanks for the assistance, I get the logic but in practice (test system practice) I seem to be having an mental block. My Noticeboard item is part of the system object. We use change management extensively with loads of queries and counts so I am loathe to link the notices to change as would need to edit all the queries. I'm not really understanding how this is needed (which is probably the issue). I'm happy to modify the display query to include the Or display no notices. Not really sure even after reading the tutorials on the calculations how this will loop through and update the boolean to true if there are no other messages with expiry date <= now. We use this as real time so how often does it loop - only when a new message is saved or updated? Understanding this aspect will probably help with the further development of automating the move of notices from planned to current to expiry.




                      • 8. Re: Query on Noticeboard Item

                        Add this calculation (result as string) to your NoticeboardItem:


                        import System

                        static def GetAttributeValue(NoticeboardItem):

                            Value = 'False'

                            CountVar = 0

                            if NoticeboardItem.Title == 'No Notices':

                                Value = 'True'

                                for CountVar in NoticeboardItem:

                                    if NoticeboardItem.ExpiryDate > DateTime.Now:

                                        Value = 'False'

                            return Value


                        Unless it is your "No Notices" record (expiry date is in the past to work correctly), we set the value to False.  If it is the "No Notices" record, I search through all noticeboard items for anything that expires in the future.  If anything exists it changes the value to false, otherwise it will be true.


                        Now in your query set a criteria so that:


                        1.  Expiry date "Is Within the Next" 100 Years


                        2. Calculation_Attribute = True


                        If the record's expiry date is within 100 years (ie not expired) or the Calculation attribute is set to True, the record will show up.  Sine the "No Notices" expires in the past the first criteria won't catch.  The second criteria will only be true if there is nothing in the first criteria based on the formula.


                        Of course this is all theory as I don't use Noticeboards so I have not tested this.  But in theory it should get you really close.

                        • 9. Re: Query on Noticeboard Item
                          Stu McNeill Employee



                          Unfortunately that calculation won't be possible.  From one noticeboard item you can't access other noticeboard items in a calculation because there is no direct relationship between the records.  I can't think of another way to do this without manual intervention similar to as you already have.

                          • 10. Re: Query on Noticeboard Item

                            A bit off topic her but ...


                            Based on Stu's info, that means that a calculation based on incident should be able to start in an incident then because of linking to raiseuser be able to see the collection of incidents where the raiseuser matches the currently selected raise user and traverse all related incidents related to a user to see if there is are any incidents in progress.  Then with the new dynamic forms as the raiseuser is selected I should make a label show up or hide based on there being an open incident.  Or is there too much linking to be able to access that data?

                            • 11. Re: Query on Noticeboard Item
                              Stu McNeill Employee

                              Hi Carl - that is a nice thought but that isn't possible because the Raise User link from the Incident object to the User object is a one-way link.  There are hundreds of links to the User object, one for every Creation User, Last Update User, Raise User, etc. dropdown.  If each of those was a two-way relationship you would end up with hundreds of collections on the User object back to each of those referencing objects and a very nasty performance issue trying to load them all.

                              • 12. Re: Query on Noticeboard Item

                                So basically it's impossible to flag a user as having open incidents without taking a severe hit on performance.  Searching 130,000 records just isn't practical and I have yet to find an easy way to tell my analysts that the raiseuser has open incidents.  That almost seems like a new business function for the new dynamic forms.  Enable/disable a flag based on open incidents > 0.

                                • 13. Re: Query on Noticeboard Item
                                  elizabethcombrink Expert

                                  Support - look away now please :-)


                                  Carl, I created the Open Call Count you are referring to by calculating it in SQL.  The calculation triggers everytime a new incident is saved.

                                  • 14. Re: Query on Noticeboard Item

                                    Interesting idea.  Now if only I could change to a black background with a 24 point bold yellow font and show that number on my Incident in Web Desk.  I guess a tiny number is almost as good.