Is it possible to capture a date field value on record open so that when that date field changes I still know what the original value was?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a scheduling app that has a "Schedule Date" field. I would like to be able to capture and stamp that date on record open to another field called "Previous Schedule Date" so that when the Schedule gets changed (due to reset or no show) I can use the original date to count resets and no shows? We tried doing this manually but compliance was very low. 

I would like to automate this and take it out of the users hands if possible. I want them to just change the date and set up counters to determine if it was a reset or no show. 

Thanks,

David

Photo of David

David

  • 80 Points 75 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Try this

Set up a text field called [Schedule date log] and set the Field properties to Log Changes.  

Make a form rule that says

when the record is saved

and the [Scheduled date] has changed

Action:

Change the value in the field [Scheduled date log] to the value in the field [Scheduled date] 



That should give you an audit trail in indelible ink of all the Scheduled date changes.
Photo of David

David

  • 80 Points 75 badge 2x thumb
This does not work for me. I can't use that text field to manipulate the date and determine if it was early enough to be a reset or too late and thus a no show.
no problem.
make a new field called [Previous scheduled date] as a formula date field

The formula will be

ToDate(Trim(Right([Scheduled date log],"]")))
Photo of David

David

  • 80 Points 75 badge 2x thumb
Does this work if there are more that one change made over the life of the record until it's complete?
The log field will record every change. That last formula date field will hive off the most recent update and convert that text back to a date which is usable in formulas..
Photo of David

David

  • 80 Points 75 badge 2x thumb
This is giving me the current schedule date and not the previous. Can this be adjusted to the second to last entry (which would be the previous date)?
OK, try this version

ToDate(Trim(Right(Part([Scheduled date log],-2,"["),"]")))
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
The edit form contains hidden fields with id's / name's of the format _fid_6_oval which can be read by script when the form opens and stuffed into a field to save the old value when the save button is clicked.
Photo of David

David

  • 80 Points 75 badge 2x thumb
I figured the IOL technique would be able to do this. Any chance you could give me an example of the code to accomplish this since I'm not a coder? Very familiar with the IOL technique not a clue where to start with the code. If not, that's ok. I'll keep working on a non-script type solution. Thank you Dan.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
I am attending a Pokémon conference and will catch up with this and other answers tomorrow.
I suggest going with the native solution unless you want to use this to learn the iol technique.

Keep in mind that when the new Mercury user interface comes in in the fall, we don't really know if and how these non-native customizations will work.

Hence I always feel that it is safer to stay with a native solution where there is one.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Service Workers will replace all other script injection techniques and it will work with Mercury. On the other hand, QuickBase realizes the benefit of users injecting their own JavaScript and will probably provide a supported script solution before they would close out IOL.
:). My method captures the history of all date changes, and who did it and when.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
A Service Worker can intercept any network request (eg HTML, CSS, JavaScript, Font, etc) and pass, modify or substitute the response before the browser even receives the asset and can parse and render/assemble it. So you basically have complete control over everything using Service Workers.

And there is nothing anyone can do to stop you from using Service Workers because there is no Content Security Policy or other configuration to block it. Service Worker for the win!

   ̄\_(ツ)_/ ̄     ̄\_(ツ)_/ ̄     ̄\_(ツ)_/ ̄     ̄\_(ツ)_/ ̄     ̄\_(ツ)_/ ̄     ̄\_(ツ)_/ ̄     ̄\_(ツ)_/ ̄
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
This application will save the last three previous values of a text field in three separate fields.

Previous
https://haversineconsulting.quickbase.com/db/bkz9izg89?a=er&rid=1


Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=516


You can edit the record or create your own records. You would have to modify the script slightly to manipulate a date field but it is a simple change. I simplified things and wrote the code without using jQuery and without formally testing for what page the user is on (since the _fid_* elements would not be present on other than edit or new pages).
Photo of David

David

  • 80 Points 75 badge 2x thumb
Thanks Dan. This seems fairly straight forward but I'm not sure how or why the date field would have to be scripted any differently. Is it not just original value to previous value (and so on)?
My but you are a glutton for punishment. :) Why not just use my post above.  
ToDate(Trim(Right(Part([Scheduled date log],-2,"["),"]")))
Photo of David

David

  • 80 Points 75 badge 2x thumb
I actually have worked this out without script but that doesn't mean that this old dog can't try to learn a new trick does it? Also, if it turns out to be a better mousetrap, learning this technique could only help a person down the road. Please don't think I don't appreciate your help!
OK, no problem.  I did say that that was one possibility was that you did want to learn script and the IOL technique.  If that is the case, then this is a perfect low risk way to learn!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
M>Why not just use my post above.

Because the OP wants to soar with the eagles in the cloud using script!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
D>Is it not just original value to previous value (and so on)?

Off the top of my head I thought the old value of a date (eg name=_fid_oval_6) was stored in a different format than the human readable date that is displayed in the field. A spot check appears to prove this wrong so I think you can use the same code after all.
Photo of David

David

  • 80 Points 75 badge 2x thumb
I want to thank both Dan and Mark for their contributions and efforts to help me out. There is usually more than one way to skin a cat and there is validity to both methods. Ultimately I went with the non script method but that is only because I'm afraid I would not have the knowledge needed to troubleshoot any problems that came up with the script method. That being said, I will not hesitate to draw back on this scripting education from Dan to use when the need arises.
Thx for posting this closure to this thread.  On occasion, I have hired Dan to write some of his famous iol / script for my clients when native QuickBase can't do something that my client really really wants.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
I am getting out of the IOL biz. Service Workers is the new thing. Your eyes will pop out of their sockets when you see what Service Workers can do with QuickBase.
Great, I get to skip that IOL 101 coursework and move right up to the MBA class for SW 401.  Perfect.