Desktop Calendar caculation function?

[datacaster]

Hi All,
I am wondering if this can be done as desktop application.

Someone rents an apartment/flat and rent starts at $250.00 and that value stored in fields called "starting_rent" and "current_rent" and the date stored in a field called "escalator_date". There is an increase that happens by 3% which is stored in a field called "escalator_rate".

I would like the desktop application when opened to look at all "escalator dates" in a table called "tenant agreements" and pick all tenants that meet the calculation date of (escalator_date + one year) and then calculate the new rental amount by taking those records and using (current_rent * escalator_rate + current_rent) and writing that new value back to "current_rent" field only.

Is it possible to do something like this and if so what would one do here to make it happen?

All your help is greatly appreciated as I try to learn new things. This seems very complicated, but maybe its simple?

Thank you,
Paul

[MoGrace]

Re: Desktop Calendar caculation function?

I would like to know where you live that has rents that start at $250... I might want to move.

[datacaster]

Re: Desktop Calendar caculation function?

I wish too, just a number to be funny!

Paul

[Ted Giles]

Re: Desktop Calendar caculation function?

Serious?
Current rent * Escalator Rate = 250 * 3% + Current rent
That's what you said.

Do you mean,
If ER date >= Lease start date + 1 year, Current Rate = Current Rate * 1.3 - assuming 3% increase.

You will need to handle how the next year is processed.

I would put this in an operation which ran at startup.

[Stan Mathews]

Re: Desktop Calendar caculation function?

pick all tenants that meet the calculation date of (escalator_date + one year)

That sounds like a simple query.

date() = add_years(escalator_date,1)

would yield true or false and so select the appropriate records.

(current_rent * escalator_rate + current_rent) and writing that new value back to "current_rent" field only

that's an update operation on the current_rent field with the filter from above.

I don't know about the "when opened" part. What happens if someone opens the database in the morning, closes, and opens it later in the day. Seems to me a button on a form would be a better way to trigger the process.

[Ted Giles]

Re: Desktop Calendar caculation function?

Not sure a multiple open would cause issues Stan, if the date logic was correct. Those falling outside of the logic would get skipped?
I suspect that he wants to preordain rent rises based on a % age value and time line.
If he had a rent history and a rent forecast, he could plan and analyse stuff.
Easiest way would be to have initial rate and uplifts for successive years, like +3%, + 6%, if it is linear.

[Stan Mathews]

Re: Desktop Calendar caculation function?

If opened a second time on the same day then the logic that chose which records to be updated would be satisfied just as it was the first time. Seems there needs to be an additional field to store last increase date and that date included in the filter and the update.

date() = add_years(escalator_date,1) .and. last_incr < date()

Then update

current_rent with the expression (current_rent * escalator_rate) + current_rent or more simply (current_rent * (1+escalator_rate))

last_incr with date().

[datacaster]

Re: Desktop Calendar caculation function?

Thanks for all the comments.
After posting and going to bed I realized later that it could be a button that is pushed before billing each month. The escalator is constant 3% each year and it is 3% of the current rent. The starting rent is a record and maybe I could use that as Ted says and I suppose 3, 6,9,12% for each year times the starting rate. Never thought about it that way and tried the math and it works, just seems to round out lower like $10.918 becomes $10.90 (need to check)

I guess I need to think more on this. I was giving my simplest approach I could think of, but I see possible flaws now in process as you responded, it made me think more.

Just as a clarification on calculation
250(the current rent)*3% being 3/100 being (.03) so
250*.03=7.5+250=257.50 the new current rent for the year

Thanks again!
Paul

[Stan Mathews]

Re: Desktop Calendar caculation function?

3% of the current rate is not the same as 3, 6,9,12% for each year times the starting rate. That's the principle behind compound interest rates.

[MoGrace]

Re: Desktop Calendar caculation function?

250*1.03=257.50
257.50*1.03=265.225 vs 250*1.06=265.00

[datacaster]

Re: Desktop Calendar caculation function?

3% of the current rate is not the same as 3, 6,9,12% for each year times the starting rate. That's the principle behind compound interest rates.

I see after doing the math that we have compounding rate increases as to using the initial rate and doing 3, 6, 9, etc...
I have to use the compounding rate current rate * .03 for each years rent. Just the comment made me think can I do that.

Starting rent 100
first year
100*.03=3+100=103 Compounding rate being the base
second year
103*.03=3.09+103=106.09 Compounding rate increase .09 higher
or
100*.06=6+100=106.00
third year
106.09*.03=3.18+106.09=109.27 Compounding rate increased to .27 higher
or
100*.09=9+100=109.00

If I could only put $50K in a high interest account and wait 50 years!
Wish I had $50K to put in an account and 50 years to live!

Thanks for all the thoughts and comments and help!

Paul

[Ted Giles]

Re: Desktop Calendar caculation function?

You'll be OK. Robin is older than all of us.

[madtowng]

Re: Desktop Calendar caculation function?

Hi Paul,

I think the function below will do what you're looking for.
I set it up this way to accommodate testing, but for the most part,
your autoexec or any other script will simply invoke this as autoIncrease(date()).

Hope this helps.

Gregg

FUNCTION autoIncrease AS C (qDate AS D )
	
dim tbl as p
dim tracker as n =1

tbl = table.open("tenantAgreements",FILE_RW_SHARED)

tbl.fetch_first()

while .not. tbl.eof() .or. tracker <= tablecount("tenantAgreements",".t.")
if qDate >= addyears(tbl.escalator_date,1) then
tbl.change_begin()
tbl.current_rent = tbl.current_rent + (tbl.current_rent*tbl.escalator_rate)
tbl.escalator_date=addyears(tbl.escalator_date,1)
tbl.change_end(.t.)
end if
tracker=tracker+1
tbl.fetch_next()
end while
	
	autoIncrease = "Finished"
END FUNCTION

[datacaster]

Re: Desktop Calendar caculation function?

Hi Paul,

I think the function below will do what you're looking for.
I set it up this way to accommodate testing, but for the most part,
your autoexec or any other script will simply invoke this as autoIncrease(date()).

Hope this helps.

Gregg

FUNCTION autoIncrease AS C (qDate AS D )
	
dim tbl as p
dim tracker as n =1

tbl = table.open("tenantAgreements",FILE_RW_SHARED)

tbl.fetch_first()

while .not. tbl.eof() .or. tracker <= tablecount("tenantAgreements",".t.")
if qDate >= addyears(tbl.escalator_date,1) then
tbl.change_begin()
tbl.current_rent = tbl.current_rent + (tbl.current_rent*tbl.escalator_rate)
tbl.escalator_date=addyears(tbl.escalator_date,1)
tbl.change_end(.t.)
end if
tracker=tracker+1
tbl.fetch_next()
end while
	
	autoIncrease = "Finished"
END FUNCTION

Wow, I'll give this a try and see!

Thank you for the code suggestion,
Paul

[datacaster]

Re: Desktop Calendar caculation function?

[QUOTE=datacaster;738997]Wow, I'll give this a try and see!

Hi Gregg and all,
I gave it a shot, but not sure if I have everything correct.
I adjusted the code as my real table is different and I posted below, so I put a button on my agreement form and pasted to an onpush action and no errors or anything.

I put the statement below as I couldn't find how to call a function from a button dah, so that's why nothing?
autoIncrease(date())

In code tab I used run that function and using 100 as a current rate and a date 1 year old it came up 400

Paul

FUNCTION autoIncrease AS C (qDate AS D )

dim tbl as p
dim tracker as n =1

tbl = table.open("tenant agreements",FILE_RW_SHARED)

tbl.fetch_first()

while .not. tbl.eof() .or. tracker <= tablecount("tenant agreements",".t.")
if qDate >= addyears(tbl.agreement_escalatordate,1) then
tbl.change_begin()
tbl.agreement_currentrate = tbl.agreement_currentrate + (tbl.agreement_currentrate*tbl.agreement_escalatorpercentage)
tbl.agreement_escalatordate=addyears(tbl.agreement_escalatordate,1)
tbl.change_end(.t.)
end if
tracker=tracker+1
tbl.fetch_next()
end while

autoIncrease = "Finished"
END FUNCTION