Switch with multiple for loops and consequents new records
Hello,
for each person, I need to list all the academic years that person has been or is being hired, along with the startDate and endDate for each academic year resulting.
The only useful data already available from the contract are startDate and endDate.
Academic years start on September 1st and end on August 31st of the following year, while a contract can start and end any time or it can even be still running.
So if a contract starts on 01/09/2022 and ends on 30/06/2023, the list will only be made by one record:
- record 1: 2022-2023, startDate is 01/09/2022 and endDate is 30/06/2023.
Instead, if a contract starts on 01/09/2022 and ends on 01/10/2023, the list will be made of two records:
- record 1: 2022-2023, startDate is 01/09/2022, endDate is 31/08/2022
- record 2: 2023-2024, startDate is 01/09/2023, endDate is 01/10/2023
If the contract is longer, let's say it started on 01/09/2019 and still running, the list will be made of many records, like the following:
- record 1: 2019-2020, startDate is 01/09/2019, endDate is 31/08/2020
- record 2: 2020-2021, startDate is 01/09/2020, endDate is 31/08/2021
- record 3: 2021-2022, startDate is 01/09/2021, endDate is 31/08/2022
- record 4: 2022-2023. startDate is 01/09/2022, endDate is today
A solution for a simpler and similar situation was the on offered in this post https://forum.ninox.com/t/p8hwk0w/list-of-yearsmonthsweek but the need to define also academic years makes it more complicated to define
16 replies
-
Do you have a sample DB you can share so we can see the table structure and have some data to work with?
-
Quite straight forward. See attached sample database with code to generate records.
-
Paul Chappell Many Thanks!.
Would you also be kind enough to explain me the steps in your calculation?
Especially...
- what is "do as server"
- the "range" thing....to be sure it works also for older contracts, could I extend the range let's say (1980, 2100)?
-
Gianluca The idea is to loop through the years past and future to find overlapping dates in order to create the academic year records.
"do as server" tells Ninox to create the records on the Ninox server rather than on the device. This speeds up the whole process as it cuts down on data across the internet. If you remove the "do as server" line and the last "end" statement it will still run but a lot slower.
The "range" is just the loop. You can use whatever start and end years you want. It just depends on how far back your contracts go, so 1980-2100 is fine. So all we are doing is looking at each year in the loop and deciding if the Contract dates overlap with that particular year. It only creates a new academic record if there is an overlapping year.
-
Paul Chappell Wonderful and wonderfully clear!
I'll leave the do as server just as is, but since I'm using the Mac app I guess I'll create an alternative button without the do as server command, just in case someday I need to run the command and there's no connection at all :)
Again...thank you so much!
-
Gianluca No problem. You don't need to worry about the "do as server" and having 2 buttons. Ninox will handle it automatically.
-
Paul Chappell works like a charm, so I dare to ask for one more suggestion: if the same worker has more than one contract (separated my years), is it possibile to have a list with all the academic years for all the contracts of the same worker?
-
Gianluca Sure. You just need a sub-table.. see revised database attached.
With all of this you cannot create records where "endDate is today" because "today" is constantly changing.
-
till learning
Paul Chappell Thanks! As you can see I'm still learning a lot about Ninox and maybe I wasn't clear in my request.
The button in the first sample database that you have sent creates perfectly the list of academic years for each contract.
What I want to achieve, though, is that the same button (even placed in a different table, if needed) gives me the comprehensive list of all academic years for all the contracts of the same worker.
Maybe I'm dumb and I'm doing something wrong, but at the moment in order to get the same result, I need to click on the button from within each contract of the same worker.
-
Gianluca That's fine. Just add the button to the Names table and loop through each contract. New sample attached..
-
@Paul Chappell by now I owe you a few beers, or whatever drink you like :)
just ...to understand the instructions and adapt to my needs, let's see if I got the algorithm right.
First you set the actual worker as a variable.
Then you make a selection in the Contracts Table and choose only the contracts for the selected worker.
In that selection, you loop through the contracts, each identified by its own ID (variable co.ID)
For each contact you loop through the years.
And then you set the value of each field.
My main doubt is about that "co.ID" variable: is it my guess correct?
-
Gianluca Yes. The idea of my example is first you create the names. Then you create a Contract from either the Name record (which automatically links a new Contract to the Name record) or from a new Contract record directly (where you first need to choose a Name record). Obviously you can create as many Contracts as you need for each Name.
The Name record will show all the Contracts for that name as a list.
When you click the CalcAccYears button from the Name record it will work out the years by looping through each Contract record linked to that Name.
let tempNameId := this.Id;
This creates a temporary variable containing the Id of the Name record.
let tempContracts := (select Contracts where Name = tempNameId);
This creates a temporary array of Contract records linked to the same Name Id.
for co in tempContracts do
This is the start of a loop where each of the Contracts in the array are first stored in a temporary record. I've used co to represent each contract in turn. You can call it whatever you want.
So, for each co record, whenever you use co.fieldname you are referring to the contents of the fieldname in the record called co.
This then loops for each Contract.
Obviously, you don't also need the CalcAccYears button in the Contracts record which does the same thing but for only the one contract.
I use temp to indicate a temporary variable or record. That's just my preference. You can use whatever suits you.
Hope this helps!
-
Paul Chappell everything set up and works perfectly. Thanks.
More beers added
-
-
Here is my take on it. I try to cover empty end dates and start dates that happen mid school year. Open the contratti table to see the button.
I've commented the code in the button so I won't put them here.
Please excuse the bad italian names for the tables and fields.
Content aside
- 2 yrs agoLast active
- 16Replies
- 202Views
-
3
Following