Is this a bug? Weird behaviour when using Date Modified

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have been playing with a way to implement a hidden calculated code that gets emailed out to an approver but is obscured from the main form. The idea is that the approver enters the code, QB checks if it matches the calculated code and shows the Approval field andf the user approves the record.

I started building the calculated code on the basis of the "Date Created" field (using DayOfYear) which worked fine. I then tried to add in "Date Modified" to the formula so that the code would change each time the record was modified. However, I ran into some very odd behaviour with "Date Modified".

I have done some testing and can replicate the issue as follows:

Within the same table create three fields...

1 - Create a Numeric field called "Enter Code"
2 - Create a Formula - Numeric field called "Code" with the following formula "Hour(ToTimeOfDay([Date Modified]))"
3 - Create a Formula - Numeric field called "Check Code" with the formula "[Enter Code] - [Code]"

When I go back to the form for a particular record "Code" says "9". I enter "9" in the "Enter Code" field. You would expect that "Check Code" would say "0" (since 9 - 9 = 0) but it instead says "-1" !!!!

Similarly, one of my records has a code of "17" (which is based on the Last Modified date with time of 5:40 PM, i.e. 17:40) so I enter "17" into the "Enter Code" field. Again, you would expect the "Code Check to show "0" but it instead shows "7" (meaning QB thinks the actual value is "10" but is showing "17")

So basically, QB is showing a different number in the field than it actually is holding behind the field! This only seems to happen with the "Date Modified" field, the "Date Created" field works fine... Anyone run into this before???

I'm raising a case with QB but thought I'd run it past the community as well.
Photo of David

David

  • 20 Points

Posted 2 years ago

  • 0
  • 1
Photo of David

David

  • 20 Points
Slight update, the actual "Code", i.e. the actual "Date Modified" when you are in edit mode seems to be based on the current Date/Time rather than the Date Modified. So the 10 that QB thought was the TimeOfDay is not the TimeOfDay from the Date Modified field but seems to be the current TimeOfDay... This feels like a bug but I'll wait to see what QB say.