5 Replies Latest reply on Aug 4, 2017 12:16 PM by philmodjunk

    Calculate Field in Same Table




      I created an Audit log and I need to get certain calculations done. I will post a picture of what it currently looks like:


      Screen Shot 2017-08-04 at 10.31.04 AM.png


      I need to gather the amount of time someone is spending by calculating either the TimeStamp field, or the ScriptStart Field etc.


      Eventually, I want to split this up by time spent on each data source. There are three different ones in total.


      I have seen some posts here about something similar but I haven't figured out yet where to start.


      I tried creating a TO so I can subtract the timestamps but I need to subtract the previous one with the current one and so on.


      Or if anyone has done it a different way, I am open to suggestions.


      Thank you all for your time.

        • 1. Re: Calculate Field in Same Table

          The difference of two time or two timestamp fields is the elapsed time in seconds. From what I see, you know this already given the elapsed time field that I see.


          So getting the previous timestamp would seem to be the issue. What criteria would you use to identify the previous log entry from which to get that value? Same Created by Name, but greatest TimeStamp value that is still less than the current record's time stamp?


          A self join could be used to match to that field.

          ExecuteSQL could grab that value also.


          To minimize performance hits, I'd copy that value into a field of the current record at the time it is created so that you can use a stored calculation that computes elapsed time instead of a "chain" of unstored calculations.

          • 2. Re: Calculate Field in Same Table

            We can break it down into segments.

            Example, the criteria is the CreatedBy (which gives the username) and we would break it down by date.

            So we have the total amount spent on X date by X person.


            This is where I got the Idea of subtracting the Timestamps to get the total amount spent. I have tried using summary fields but doesn't quite work as I intended it to.


            After all of this is established, it's as simple as adding up the dates to get the total amount spent for a month.


            When you say copy the value into a field, you mean copy the timestamp that is shown? Because that is already hard coded in and does not change. ProcessTime field is a stored calculation since it does it once per record. And that is ScriptEnd - ScriptStart.


            Regarding the SelfJoin, Would I have multiple ones? One matching the CreatedBy and the other for matching the TimeStamp?


            Thanks Phil.

            • 3. Re: Calculate Field in Same Table



              I assume the table with the audit log is populated with a script, if so;

              maybe you can set the previous timestamp as a global variable $$previousTimestamp in your script.


              In your script can start with $currentTimestamp and after the new record in your audit table is created you can set $$previousTimestamp in the previous entry field.


              At the end of the script you can set the variable $$previoustimestamp with  $currenttimestamp.

              If this is a possibility there should also be a start script and a script for lunch break and shift end.

              Otherwise you will never know the time spent.


              Hope this helps or maybe it will give you an idea for a another approach.




              • 4. Re: Calculate Field in Same Table

                I am gathering information on how much time they spend using the application for tracking purposes. Lunch or shift end is irrelevant here.


                And yes, the records are populated based on a script.


                And I am planning on getting daily values first so when the next day starts, it starts from 0 again.


                We can go as far as finding records from x time to x time to keep it within the work hours anyways.


                But I do like setting a previous timestamp and use that as part of the calculation.



                • 5. Re: Calculate Field in Same Table

                  Putting the value into a global variable seems an excellent idea here. Fast and simple.