1 2 Previous Next 20 Replies Latest reply on Oct 31, 2016 5:23 AM by Mike_Mitchell

    Case

    cillion

      Hi there!

      Am trying to get a case to work. If a supportcase does have more than 0 related supportlines the status should be Started, if the same thing is true AND one of the supportline has an empty field for Time_ended the the supportline is Working at the moment. There also should be a script that finishes the supportcase off with filling another status field just for that pupose. This is my case now that is not working proparly for the status field:

       

      Case (

      customer_support_SUPPORTLINES::id  >  0 and IsEmpty ( ustomer_support_SUPPORTLINES::id::Time_ended ) ; "Working" ;

       

      customer_support_SUPPORTLINES::id  >  0 ; "Started" ;

       

      customer_SUPPORT::status_change_for_script = "Done" ; "Done" ;

       

      "Unstarted" )

        • 1. Re: Case
          Mike_Mitchell

          What exactly "isn't working"? What is your expected result, and what is the result you're getting?

          • 2. Re: Case
            cillion

            First of, when creating the new support the status gets "unstarted", and that correct. But when the user ads a new supportline (meaning start time/activity on that support) then the status changes to "Started" and not "Working", because if the users has not stopped the time on that line (empty Time_ended field) it should be "Working". And if the script for ending it has been triggered, and filled the other status field this status calc dont change. I maybe should have the "done" bit in the start of the case?

            • 3. Re: Case
              Mike_Mitchell

              You keep referring to a script. Is this calculation in a script, or in a field definition? If it's in a script, when does the script fire??

              • 4. Re: Case
                philmodjunk

                But when the user ads a new supportline (meaning start time/activity on that support)

                Does that mean that a new related record has been added? If so, your calculation only references data from the first related record. It can't "see" any data from the related customer_support_SUPPORTLINES table occurrence except whatever comes out as the "first" related record. In an unsorted relationship that would be the oldest related record. In a sorted relationship, that would be whatever related record is first in the specified sort order.

                1 of 1 people found this helpful
                • 5. Re: Case
                  cillion

                  In this case there would be the last created (max of ) customer_support_SUPPORTLINE::id that he should check if the Time_ended is empty, if thats the case somebody is "working" at it at the moment. So something like this :

                   

                  Case (

                  customer_SUPPORT::status_change_for_script = "Done" ; "Done" ;

                   

                  max(customer_support_SUPPORTLINES::id)  AND IsEmpty (customer_support_SUPPORTLINES::id::Time_ended ) ; "Working" ;

                   

                  customer_support_SUPPORTLINES::id  >  0 ; "Started" ;

                   

                  "Unstarted" )

                  • 6. Re: Case
                    philmodjunk

                    But isn't the id field identical in value for all related records?

                     

                    If you want data from the most recently entered record, use either the Last function to access that data or set up a relationship sort order that sorts by either an auto-entered serial number or auto-entered timestamp in decending order and the most recently added record will also be the first related record.

                    • 7. Re: Case
                      cillion

                      Okey. No the id field in supportlines is a auto enter serial number. Something like this then :

                       

                      Case (

                      customer_SUPPORT::status_change_for_script = "Done" ; "Done" ;

                       

                      Last(customer_support_SUPPORTLINES::id)  AND IsEmpty (customer_support_SUPPORTLINES::id::Time_ended ) ; "Working" ;

                       

                      customer_support_SUPPORTLINES::id  >  0 ; "Started" ;

                       

                      "Unstarted" )

                      • 8. Re: Case
                        Mike_Mitchell

                        If you're adding a related record, then that will not trigger a calculation in the parent table (as Phil has already mentioned). You will need to use another method to trigger it, such as a Script Trigger or a trigger field inside your calculation. For example:

                         

                        Let ( [

                             trigger = customer_SUPPORT::modifyTS

                        ] ;

                         

                        Case (

                        customer_SUPPORT::status_change_for_script = "Done" ; "Done" ;

                         

                        Last(customer_support_SUPPORTLINES::id)  AND IsEmpty (customer_support_SUPPORTLINES::id::Time_ended ) ; "Working" ;

                         

                        customer_support_SUPPORTLINES::id  >  0 ; "Started" ;

                         

                        "Unstarted" )

                         

                        )

                         

                        This will cause the calculation to fire whenever the value in the modifyTS field (which would be an auto-enter modification timestamp) changes.

                         

                        Another option would be to use an OnRecordCommit Script Trigger. (Or you could use other trigger events, as well.)

                         

                        But just referencing the related field will not cause the calculation to re-evaluate.

                        • 9. Re: Case
                          philmodjunk

                          Mike,

                           

                          I'm familiar with the method you are using, but please notice that your trigger field reference is referencing the same, table occurrence as those used in the Case function: customer_SUPPORT.

                           

                          Since that is a related table occurrence, that will no more trigger an update than modifying one of the other fields already referenced in the case function. Such a "trigger field" only works if it is a field defined in the same record as the auto-enter calculation field.

                           

                          An unstored field of type calculation will automatically update on the other hand and sometimes, to get a stored value in the parent record, we end up using a triggered script to update the field in the parent table when data is modified in the related child record.

                          • 10. Re: Case
                            Mike_Mitchell

                            Yes, I know. It was meant to reference the local table. I guess I got the wrong TO?

                            • 11. Re: Case
                              Mike_Mitchell

                              And yes, you could use a trigger (which I mentioned) or an unstored calc. Of course, an unstored calc carries its own set of issues. Performance when searching, performance in first load, performance because it has to re-evaluate every time it's called, inability to use as the target of a relationship, etc.

                              • 12. Re: Case
                                cillion

                                Yes. I tried this trigger both in the same and the related table, and it does not work.

                                 

                                Okey so I need a OnRecordCommit script on the related supportlines layout? In this script I just set the status field in the parent to "Working" if the time ended is empty?

                                • 13. Re: Case
                                  Mike_Mitchell

                                  OnRecordCommit operates on the current layout. Putting it on the related layout will not work unless the record is being edited from there. (The vast majority of the triggers work that way - the current context or object).

                                   

                                  If you put it in the parent layout, it should work (because OnRecordCommit is a before-event trigger, so you don't lose the active row). I think, though, that your calculation might be flawed for use in the parent context. This line:

                                   

                                       IsEmpty (customer_support_SUPPORTLINES::id::Time_ended )

                                   

                                  will reference the first related record. Is that what you want? Which Time_ended are you looking for?

                                  • 14. Re: Case
                                    cillion

                                    There is nothing i the parent changing when creating a supportline. So I think I need the OnRecordCommit on the Supportline table/layout.

                                     

                                    There could be more than one supportcase currently "Working" at the same time by different users for example. So it need to woork even if there is multiple supportlines are "working".

                                     

                                    After using a OnRecordCommit script on the supportline table/layout that changing a tempStatus field there, empty or "Working" if the TimeEnded field is empty. And then make tempStatus in the parent Support table as well that calculates the tempStatus in the supportline, the tempStatus in the parent dont get the same value. Why is that ? If this worked I could just assign the "Working" status exacly the same as the Done status in the Case.

                                    1 2 Previous Next