• Registered: 2020-06-26
  • Posts: 302

Topic: Update Calculated Field

Hi All,
Is there any way to update a Calculated field on a form without leaving the form and re-opening the form?
.
I have a calculated field called calcNextSvcDate. I also have a dropdown field called cboSvcInterval.  The calcNextSvcDate is computed by taking the Last Service Date and adding the Service Interval to it.  These fields work fine when creating a new Service record.  However I'm wondering if I'm able to update the Calculated field whenever I change the Service Interval on the form, WITHOUT having to close and re-open the form?
.
Please see the attached  PDF file showing part of my form.
.
Thanks
Frank

Post's attachments

Attachment icon Update Calculated Field.pdf 46 kb, 188 downloads since 2021-08-14 

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 906

Re: Update Calculated Field

If I understood the question correctly.
For action OnCloseUp or OnChange cboSvcInterval component
use in script
dbUpdate function TableGrid.

Form1.TableGrid1.dbUpdate;

3 Reply by derek (edited by derek 2021-08-14 14:21:36)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Update Calculated Field

Hi Frank, Sparrow,
I was rather expecting this question - LOL! 
It's one of the things with calculated fields that you have to go in and out of maintenance forms to pick up the latest updates (forms with tablegrids are not a problem because you can use formN.tablegridN.dbupdate as Sparrow said).
But there is invariably a way around it with a bit of script (see attached - specificaly lines 11-20 in the script).
It could all be done in one or two lines but I've broken it down into the basic steps so it's more straightforward to follow.  Also, I tend to use fields on the form (but hide them when I've checked it working) rather than variables in the script - it just makes it easier to see what's going on. 
So, the 4 basic steps in the script are:
1.  you've just changed the service interval so get the new no' of interval days and hold it in edit3 (hidden)
2.  get the date of the most recent service and hold it in datetimepicker1 (hidden)
3.  add the new no' of interval days (edit3) to the most recent service date (datetimepicker1) and hold in datetimepicker1
4.  convert the date in datetimepicker1 to a string and write it to edit2 (the original calculated field).
You can set edit3 and datetimepicker1 to 'visible' temporarily if you need to see what's going on 'behind the scenes'.
As always, there are other ways to do it but I was trying to think of ways that would also minimise any re-work for you.
Derek.

Post's attachments

Attachment icon fcupdate.zip 346.24 kb, 216 downloads since 2021-08-14 

  • Registered: 2020-06-26
  • Posts: 302

Re: Update Calculated Field

Derek,
Many thanks for the example code.  It sounds pretty straightforward.  I'll work on incorporating it into my form.
.
As a boat person, I'm impressed with your Equipment list.  It sounds like you've been around boats or have a pretty good knowledge of them.  You have more items in your equipment list than I do, so maybe I'll borrow a few of them.  I hope you're not going to charge me for them. LOL.
.
Thanks again for all of your support.  I wouldn't be able to do half of what I do without the help of yourself and other members of the forum.
.
Frank

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Update Calculated Field

Hi Frank,
No - I'd struggle to know my port from my starboard LOL!.
I found a list on the internet and loaded it into the program.  I just find it easier for testing purposes if the data is more realistic.  Get a bit fed up with using Item A, Item B etc all the time.
Derek..

  • Registered: 2020-06-26
  • Posts: 302

Re: Update Calculated Field

Derek,
If I'm being honest, I don't remember all of the equipment on my boats, even after living aboard for over 20 yrs.  Google has come to my rescue also smile
.
I've been thinking about my Calculated field scenario for calcNextSvcDate.  I know, I know, too much thinking can get me into trouble.  But that's what I have you folks for - to bail me out.
.
Anyway wouldn't it be a good idea to have a NextSvcDate field on my Equipment form? Instead of the calculated field?  So it would be available every time I opened the form and/or when I want to do some reports about upcoming maintenance.
.
We would still need the Calculated formula to come up with the NextSvcDate though.
.
Then I'm thinking that the Calculated field could just be put into the NextSvcDate field after it is calculated.  I've been trying to convert the new Calculated field to a DateTimePicker format and save it in my new field EqNextSvcDate, but it's not working.
.
Maybe my logic is wrong or I'm missing something?  Please let me know what you think.
Thanks
Frank

7 Reply by derek (edited by derek 2021-08-16 15:31:01)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Update Calculated Field

Ahoy Frank!
Personally, I wouldn't go with a mix of calculated fields and stored fields that are essentially doing the same thing.  It negates one of the main reasons for using calculated fields.
Using a 'stored field' to hold the 'next service date' in a table isn't a problem and there are some advantages to taking that approach.  For example, it gets around the issue with a calculated field of having to write some code to show the updated value on the maintenance form immediately.
Attached is how you could do it with 'next service date' stored on tbl_equipment instead of using a calculated field (the maths is done in the script instead). 
Derek.

Post's attachments

Attachment icon fcupdate5.zip 346.68 kb, 199 downloads since 2021-08-16 

  • Registered: 2020-06-26
  • Posts: 302

Re: Update Calculated Field

Derek,
In your example code, you are storing the NextSvcDate in a table Grid.
Currently I have added a DateTimePicker field to my frmEquip.  Is there any way to have the calculated formula update the DateTimePicker field directly, without having to use a Table Grid?
.
FYI, I have an Equipment Table Grid on the form that proceeds the Equipment form.  So the user will select a piece of equipment from that form and it will take it to this frmEquip that I have attached. As you can see, I have quite a few fields on my Equipment form.
.
I'm thinking it would not be efficient to create a table grid for just one field (NextSvcDate).  I think you mentioned something like that previously.
.
I've attached a screen shot of my Equipment form so you can see what I'm referring to.
. I'm thinking that maybe one line of script to put the Calculated Date into the DateTimePicker field?  Or am I asking too much?
.
Also, I'm thinking that this DateTimePicker field should be updated If and When I might change the Service from say Weekly to Monthly, without adding a new Service record.
.
And to add a little more complication, I'm eventually going to want to run/print a report showing the upcoming service items that will need to be addressed, say in the next week or month...  I'm thinking I should be able to add the NextSvcDate DateTimePicker field to my table grid in the previous form so I can create reports.  I hope all this makes sense and I'm not getting myself lost in the weeds LOL.
.
I noticed your "Ahoy" phrase.  You're getting all nautical smile  FYI: If you put Starboard and Port on your body, your Starboard side is your right hand.  It's always your right hand no matter which way you're facing. So you can look at a boat (or an airplane) and always tell which way it is going.  And more trivia - the Starboard (right) side always has a RED light and the Port (left side) always has a GREEN light. No extra charge for the nautical trivia smile
.
Thanks, Frank

Post's attachments

Attachment icon EquipNextSvcDate.pdf 76.54 kb, 182 downloads since 2021-08-16 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Update Calculated Field

Frank,
As I described in my earlier post, there's no merit in using both a calculated field AND a stored field to show nextsvcdate.
So in the previous attachment (fcupdate5), I replaced the calculated field with a stored field and work out the date of the nextsvcdate in the script.
The script fires when
a) a service record is saved
b) when the service interval is changed. 
In both instances, the script works out when the next service date is and saves that date to the tblequipment table
As this is now stored in tblequipment, 'nextsvcdate' becomes available to you wherever you want to display it -  as a column on your Equipment tablegrid in the preceding form or as a datetimepicker field on your Equipment form - it's up to you.
So, in the example of your Equipment Form (in your attached .pdf), simply remove the 'Next Service Date (calculated) field  and solely use the Next Service Date (date picker field).
If it's still not working as you want it to, can you attach your project.
Derek.

  • Registered: 2020-06-26
  • Posts: 302

Re: Update Calculated Field

Derek,Sparrow,
I wanted to give you an update on my NextSvcDate issue.
The solution you provided works great in the example code you sent.
However when I tried to implement it into my forms and script I had a lot of trouble.  I had to change some of your table grid references to the fields I have on my Equipment form.  It should have been pretty straightforward but I was obviously screwing something up.
.
So I went looking for something a little easier as I had in the back of my mind that there might  be a simpler solution to be able to add some days to a DatePicker field.  I stumbled on this code on the web, actually it was from DriveSoft on the forum: frmEquip.EqNextSvcDate.DateTime := frmService.cboSvcDate.DateTime + 60 ;
.
It works as written, but I wanted to replace the 60 with whatever ServiceInterval happened to be on my Equipment form.  So I put in a new question on the forum and Sparrow provided a suggestion to use the strToInt function. It appears to be working.
frmEquip.EqNextSvcDate.DateTime := frmService.cboSvcDate.DateTime + strToInt(frmEquip.cboSvcInterval.Text ) ;
.
At first it didn't work because SvcInterval was already an Integer field.  I had tried to use it as is before but kept getting compatibility errors.  Maybe there was something wrong with my syntax??
.
Anyway, I changed SvcInterval to a text field, then applied the strToInt function and now everything seems to be working OK.
.
I want to thank you both for all your efforts.  I know I've learned more stuff, I just hope I can remember it later on when I might need it again.
.
Frank