I am making an app to manage recurring service contracts for repeat customers, it has 4 tables:
- Contract (Every customer can have multiple contracts, but each contract has just 1 customer) it lists the services that need to be done for each contract.
- Rates for the year ( a price list by year by service)
- Calculated contracts
Basically, I want to be able to choose a contract, and from the contract page, I can push a button, enter the year of my choice and get the rates calculated for that year entered in the calculated contract table. I then use that information to create a print merge letter that generates the customer's contract.
So far, these are my relationships (I'm sorry this is in French, but should be easy to make out):
What I have done so far was to have a button on the Contract page that generates a pop-up asking for the year to be calculated and then copies the fields of the contract in the record in the Calculated contract table.
This is not ideal, I would have prefered a drop down or another way to automatically pick the record from the list of existing years in the rate table. I suspect it would be subsequent steps easier as well.
Where I am stumped is the part where I retrieve and copy the rates from the rates table because when the user types "2018" for exemple, my script searches for record 2018 and not the record for the year 2018.
My other challenge is to copy the customer info from the customer table related to the contract.
Here is my script:
I may have gone in a completely wrong direction, please don't hesitate to point me otherwise. This is my first script.