1 2 Previous Next 20 Replies Latest reply on Oct 30, 2012 2:25 PM by PaulWebb

    Is one method better than the other?

    PaulWebb

      Overall goal is to show a count for records with an open status. FM Pro 12 Adv

       

      I came up with two methods to accomplish the above. Wondering if one is better than the other and why.

       

      Setup

      Two tables: Portfolio and SR

      Layout: records from Portfolio

       

      Method 1

      Filtered portal, related records from SR

      Filter: LeftWords ( SR::SR_Status ; 1 ) ≠ "Closed"

      One field in portal: Count_SR

      Summary: Count of SR Number

       

      Method 2

      Create two fields in the SR table

      CurrentlyOpen

      Calculation; Unstored: If ( LeftWords ( SR_Status ; 1 ) ≠ "Closed" ; 1 ; 0 )

      CurrentlyOpenSummary

      Summary: Total of CurrentlyOpen

      CurrentlyOpenSummary is displayed on layout.

       

      Is there a better way to accomplish the same thing? Are there any problems with using either of the above options?

       

      Thanks

        • 1. Re: Is one method better than the other?
          comment

          What values can SR_Status have - and how does a SR (whatever that is) become closed?

          • 2. Re: Is one method better than the other?
            PaulWebb

            SR_Status can be...

            New

            3rd Party Pending

            3rd Party RMA Pending

            AS Pending Workaround

            CE Pending

            CE Pending - Lab Recreate

            CE Pending Workaround

            Customer Pending

            Customer Pending Workaround

            Customer Updated

            DE Pending

            DE Pending Workaround

            Failure Analysis Pending

            Release Pending

            Requeue

            Restoration of Service

            Service Order Pending

            Solution Provided/Monitoring

            Close Pending

            Customer Requested Closure

            Closed

            Closed w/o Customer Confirm

             

            An SR is a trouble ticket and closes when the engineer that owns the case closes it. The SRs are not managed in the FM solution. The solution pulls data (or will eventually) from another DB.

            • 3. Re: Is one method better than the other?
              comment

              Using the first method, each SR in a portfolio must be evaluated every time the screen redraws. This could prove too slow when the number of SRs is large - esp. if you are showing several portfolios in a list view.

               

              The truth is that the second method, the way you have described it, is not much different. If, however, you were to make the calculation stored, it would be much quicker.

              • 4. Re: Is one method better than the other?
                PaulWebb

                Thanks. I've changed it and I'll see how things go. Is there another method that might work better? The layout will only show one portfolio at a timeand not all portfolios are the same but the biggest ones might be around 200 SRs currently open.

                • 5. Re: Is one method better than the other?
                  comment

                  pawebb wrote:

                   

                  The layout will only show one portfolio at a timeand not all portfolios are the same but the biggest ones might be around 200 SRs currently open.

                   

                  I suppose a few hundred should not create a problem - mind you, though, that all related SRs must be evaluated, not just the open ones. The advantage of the filtered portal method is that it doesn't require any extra fields (assuming the summary count field is needed anyway).

                   

                   

                  If it proves too slow, perhaps you could try something like =

                   

                  PatternCount ( List ( SR::SR_Status ) ; "Closed" )

                   

                  defined in the Portfolios table.

                  • 6. Re: Is one method better than the other?

                    PatternCount ( List ( SR::SR_Status ) ; "Closed" )

                     

                    Hi Michael!

                     

                    I confess I did a double-take here.  Unless I am misunderstanding the situation (and that happens all the time, LOL), PatternCount() must evaluate every record before it can produce a result whereas Position ( List ( SR::SR_Status ) ; "Closed" ; 1 ; 1 ) stops when it hits the first true so it would be faster.   Even not IsEmpty ( FilterValues ( List ( SR::SR_Status ) ; "Closed" ) ) ... although I question whether text calculations can ever be as fast as numeric.

                     

                    Just a possible option ...

                    • 7. Re: Is one method better than the other?
                      comment

                      LaRetta wrote:

                       

                      PatternCount() must evaluate every record before it can produce a result whereas Position ( List ( SR::SR_Status ) ; "Closed" ; 1 ; 1 ) stops when it hits the first true so it would be faster.  

                       

                      The last time I tested this (and it was a while ago), it proved to be an unfounded assumption. In any case, pawebb  wants an actual count of all open SRs - not just an indication if one exists.

                       

                       

                      LaRetta wrote:

                       

                      Even not IsEmpty ( FilterValues ( List ( SR::SR_Status ) ; "Closed" ) )

                       

                      Alas, that wouldn't work for SRs with a status of "Closed w/o Customer Confirm".

                      • 8. Re: Is one method better than the other?

                        I dislike unfounded assumptions as well and I had just accepted this as fact never testing it myself ... <I shall test> 

                         

                        Neither did I read the entire thread which I normally do  ...  <hanging head>

                         

                         

                        My apology for the disruption and now we resume your regular broadcast ...

                        • 9. Re: Is one method better than the other?
                          Malcolm

                          Overall goal is to show a count for records with an open status. FM Pro 12 Adv

                           

                          A checkbox with the value 1 on a number field is effective. It can be set to auto-enter when SR_status contains "closed". You can then create a summary field which counts the number field. The summary will alway display the correct count across any valid relationship.

                           

                          Malcolm

                          • 11. Re: Is one method better than the other?
                            PaulWebb

                            Michael Horak wrote:

                             

                            If it proves too slow, perhaps you could try something like =

                             

                            PatternCount ( List ( SR::SR_Status ) ; "Closed" )

                             

                            defined in the Portfolios table.

                            I tried this but it only counts records with a status of "Closed". I tried changing it to count everything else but no luck. As of right now I only have 12K records in the solution to test with. Once this goes live that number will go up considerably with some portfolios creating as many as 12K records a year. So once I have multiple years in the system it sounds like this could really slow it down.

                            • 12. Re: Is one method better than the other?
                              PaulWebb

                              Malcolm wrote:

                               

                              A checkbox with the value 1 on a number field is effective. It can be set to auto-enter when SR_status contains "closed". You can then create a summary field which counts the number field. The summary will alway display the correct count across any valid relationship.

                               

                              Malcolm

                              I think this is the same as my method 2 in a different dress. First field calculates a 1 or zero and then a summary field to total them. It works but sounds like it might have performance problems down the road.

                              • 13. Re: Is one method better than the other?
                                comment

                                Paul Webb wrote:


                                I tried this but it only counts records with a status of "Closed".

                                 

                                Hmm, let's see... Nope, it works for me:

                                • 14. Re: Is one method better than the other?
                                  PaulWebb

                                  Michael Horak wrote:

                                   

                                  Paul Webb wrote:


                                  I tried this but it only counts records with a status of "Closed".

                                   

                                  Hmm, let's see... Nope, it works for me:

                                  Sorry, I meant that it only counted closed cases which includes both "Closed" and "Closed w/o Customer Confirm". Which is the opposite of what I am looking for. I'm not concerned with how many closed cases there are just how many are open.

                                  1 2 Previous Next