Increase a number by 1 each year...simply

[Auraboros]

Hi...I have looked high and low for this solution...maybe it is too simple for anyone to comment on!

I have a number field...say I enter 50.

In one year from the date I entered 50 in that field, I would like it to automatically increase by 1, to 51. I do not want to have to re-enter the number, and if I don't visit that record with the field in it for 3 years, then I want it to increase by 3. Increase by 1 each year. 

OK, let me tell you what it is. I enter the ages of clients' children, next to their names, in a database. I do NOT want to have to enter their birthdays, nor even their birth year, I want to just enter the age, in a number, like "12". In a year from now I want that number to change to 13. I don't really care if it does this exactly on the date I entered it. It can change on January 1 each year, I don't care. It isn't meant to be accurate, it is just a basic idea of the kid's age, could be off by a whole year even. I don't care.

I want this number to change each year...based on when I entered it if possible (it would then be "sort of" accurate)...so if 5 years go by before I look at that record again, I want the age to increase by 5 years.

Seems simple...but every solution I've found requires that I enter the birthday, which I don't want to have to do...or even enter the year of birth...same problem. I want something very simple. (People get suspicious when you ask for their kids' birthdays...)

Solution??

THANKS!!!!

[comment]

11 minutes ago, Auraboros said:

I want this number to change each year...based on when I entered it

For this to work, you would need to know when you entered it - for example, by having a field auto-enter the record's creation date/timestamp. Then you could have an unstored calculation field showing the current age (approximately, since you don't know the exact birthdate) .

Without this, the only way to achieve what you want would be by running a script once a year to increment the stored Age values by 1.

 

 

[Auraboros]

I set up a script trigger on the age field so when modified it inserts the current day in another off layout field.

But now I don't know what to do with that date. How do I get the contents of the "Age" field to add a year when 365 days have passed from the modify date? I think I am going about this the wrong way...

Thanks Comment! 

Todd

[Wim Decorte]

@comment mentioned the solution already; since you don't care when you increment; just a have a scheduled script that runs on January first and increments the age field by 1.

If you want to use that new field you just created then create a nightly schedule that does the same but just for those records where that date field is exactly a year ago.  Do a search on that new date field for

Date( month(<your date field>) ; day( <your date field> ) ; year(<your date field>) - 1 )

If you find records then loop through those.  If no records are found, exit the script.

[Auraboros]

Thanks guys...I hope one day I have a handle on all this like all of you do. I have been taking courses but most of them do not get advanced enough. I love trying to figure this stuff out but I think there is some logic chip that is missing in my brain. I also make things too complicated. I got really close with this but I couldn't get my "script trigger on modify" to work correctly...it would snag and just sit there until I hit command-period.

I think it is probably best just to have that yearly scheduled script run through all the records with the age field. Thank you both for the solution and suggestions!

Todd

[comment]

7 hours ago, Auraboros said:

How do I get the contents of the "Age" field to add a year when 365 days have passed from the modify date?

You don't. A stored value will never change on its own just because time has passed. But you can - and IMHO should - use a third field, an unstored calculation field, to display the current age, calculated from the stored age and the date the record was created as =

Age + Div ( Get (CurrentDate) - DateCreated ;  365.2425 )

or, if you want it more accurately, as =

Age + Year ( Get (CurrentDate) ) - Year ( DateCreated ) - ( Get (CurrentDate) < Date ( Month ( DateCreated ) ; Day ( DateCreated ) ; Year ( Get (CurrentDate) ) ) )

I believe this is the best solution for you because (a) it leaves your original data as entered, and (b) it does not modify your records spuriously when no real modification has occurred (this is specially important if you keep track of record modification dates).

 

6 hours ago, Auraboros said:

I think it is probably best just to have that yearly scheduled script run through all the records with the age field.

Keep in my mind that a yearly script has to run every year, and - no less importantly - must not run more than once in the same year. IOW, there should be a record of each run, and the script needs to check it before it acts.

 

6 hours ago, Wim Decorte said:

If you want to use that new field you just created then create a nightly schedule that does the same but just for those records where that date field is exactly a year ago.

I don't think that's a good idea because it requires the script to run every night, with absolutely no exceptions: no weekends, no holidays, no power outages, no floods. If you miss a day, there's no record of that - and your data is skewed forever.

 

 

Edited May 22, 2017 by comment