Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

How can I calculate the difference between the initial value of a field and the updated value on update on a form

How can I calculate the difference between the initial value of a field and the updated value on update on a form?  I have an actual hours field for a unique technical support case record.  When a user updates the value of actual hours I want to calculate the difference between the original value and write one record to a case hours history table with the after update value for actual hours and the date time of the update.  I have the update writing the record to the history table but I'm having problems using the fields' BeforeUpdate, OnDirty, Or AfterUpdateEvents to calculate the difference between the initial value and after update value and writing to the history table.    Alternatively, once many records are written to the history table for one record how can a report be written to calculate any differences in actual hour values  from week to week between the history records?
0
conardb
Asked:
conardb
  • 4
  • 3
2 Solutions
 
als315Commented:
In you case may be better to work with copy of record and save data with some button. In this case you will have more control on changes made. Otherwise user can change one field many times without record change. How many records should be written to history?
It is not a problem to show difference in hours from week to week, but you should clarify algorithm: is it difference between first and last value or something other?
0
 
conardbAuthor Commented:
I am writing a copy of the record to a seperate table on the after update event of the hours field so when the hours are updated a record is written to history.  I'm writing the date time of the update then group reporting by week.  One record will be written for each update to the hours field.  I'm trying to calculate the difference in the hours field and write that difference to history then sum the differences in hours entered by week in a report.
0
 
als315Commented:
Can you upload DB with main table, history table with some sample data and show expected result?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
conardbAuthor Commented:
I found a solution, thanks.  On the after update event, I calculate the change in the fields value using .value and .oldvalue, store in a variable then run an sql insert command.
0
 
als315Commented:
It is possible, of course, but generally you should not store calculated fields.
0
 
conardbAuthor Commented:
Point taken.
0
 
conardbAuthor Commented:
I resolved and took suggested comment from expert
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now