0 # List of years/months/weeks...

Hello,

I have a field with a Beginning Date and another one with the End Date.

Is it possibile to have a list of the months and a list of the years (or whatever time interval) that those two dates define?

Like so:

Beginning date: 01/09/2022

End date: 30/06/2023

List of Months: September 2023, October 2022, November 2022, December 2022, January 2023, February 2023, March 2023, April 2023, May 2023, June 2023.

List of years: 2022, 2023

31replies Oldest first
• Oldest first
• Popular • • Fred
• Fred
• 8 mths ago
• Reported - view

Would you need this done for each record? If there were 500 records you would have 500 lists? Or you would only create one list for all 500 records?

Like
• • Fred
• Fred
• 8 mths ago
• Reported - view

I'm guessing if there is no end date then nothing will happen?

Like 1
• • Fred
• Fred
• 8 mths ago
• Reported - view

Here is one possible solution:

``````let t := this;
let cntMonth := (year(EndDate) - year(StartDate)) * 12 + month(EndDate) - month(StartDate) + 1;
let srtMonth := month(StartDate);
let srtDay := day(StartDate);
let srtYear := year(StartDate);
if EndDate != null then
for loop1 in range(0, cntMonth) do
let loopMon := srtMonth + loop1;
let newRec := (create months);
newRec.(
Date := date(srtYear, loopMon, 1);
Table4 := t
)
end
else
end``````

First you will need a new table called months or whatever you want. Then create a date field (Date) and a reference field to the main table. In my example my main table is Table4.

Line 2 finds how many months there are between the StartDate and EndDate.

Lines 3-5 break down the StartDate into year, month and day.

Line 6 first checks if EndDate is not null.

Line 7 starts our loop with a range that starts at 0 and ends at the total number of months between our StartDate and EndDate.

Line 8 creates a variable (loopmon) based on the StartDate month and adds the value of loop1.

Line 9 - 14 is where we create our records and set the proper fields.

Line 11 is the main part where as the loop runs it sets the field date to be equal to the date of srtYear and the loopmon and 1. I don't know if the day of the month is important. If it is then that will change everything.

The nice thing I discovered is that you don't need to change the start year if the contract is longer than 12 months. I have a test that is 24 months long and Ninox knows to change the year as the months add up.

Line 12 links the two records.

Line 16 is an alert that shows up if EndDate is null.

Then you can create a new formula field in the months table that changes the date format to MM:YYYY.

Like 1
• • Fred
• Fred
• 8 mths ago
• Reported - view

Rafael You can try something like this:

``````let t := this;
let cntWeek := (year(EndDate) - year(StartDate)) * 52 + week(EndDate) - week(StartDate) + 1;
if EndDate != null then
for loop1 in range(0, cntWeek) do
let newRec := (create months);
newRec.(
Date := t.StartDate + loop1 * 7;
Table4 := t
)
end
else
end
``````

The code is dependent on the start date being set to a Friday. I've found a bug on the Mac app where if you start in April and run it past November the date is off by 1 day. Specifically at week 30 it starts to fail and only returns to normal on week 49. Weird. I tried with it with a November start date and it ran fine.

Like 1
• • Fred
• Fred
• 7 mths ago
• Reported - view

Here is one possible solution. Put this in the same button:

``````let t := this;
let selAllRecs := (select Table4);
let multiContractcheck := if count(selAllRecs[User = t.User]) > 1 then
1
else
0
end;
switch multiContractcheck do
case 0:
(
let cntMonth := (year(EndDate) - year(StartDate)) * 12 + month(EndDate) - month(StartDate) + 1;
let srtMonth := month(StartDate);
let srtDay := day(StartDate);
let srtYear := year(StartDate);
if EndDate != null then
for loop1 in range(0, cntMonth) do
let loopMon := srtMonth + loop1;
let newRec := (create months);
newRec.(
Date := date(srtYear, loopMon, 1);
Table4 := t
)
end
else
end
)
case 1:
for loop1 in selAllRecs[User = t.User] do
let cntMonth := (year(loop1.EndDate) - year(loop1.StartDate)) * 12 + month(loop1.EndDate) - month(loop1.StartDate) + 1;
let srtMonth := month(loop1.StartDate);
let srtDay := day(loop1.StartDate);
let srtYear := year(loop1.StartDate);
if loop1.EndDate != null then
for loop2 in range(0, cntMonth) do
let loopMon := srtMonth + loop2;
let newRec := (create months);
newRec.(
Date := date(srtYear, loopMon, 1);
Table4 := loop1
)
end
else
end
end
end
``````

You will need to change:

All reference to Table4 to match your main table.

All the date field names and reference field names in the create function.

All reference to the months table to match yours.

It does not filter out old contracts so if a user has previously closed contracts then I don't filter that so it could recreate the months for contracts that already have them.

What I am doing in the first few lines is to:

1) Line 2: put in a variable all the records from my main table

2) Line 3 - 7, sets a variable by find all records that have a match on user and if the count is greater than 1, it sets the variable to 1 or 0. I do this so I can then use a switch command to have a different set of code run. I like switches as they are easier to read.

Then I start my switch. Case 0 is when there is only 1 record for the contractor. Looks like the previous code.

Case 1 now handles if there are two records for the contractor. We have to now use a new for command to go through each of the records to make the new months.

I hope this helps.

Like 1
• • Gianluca
• Gianluca
• 5 mths ago
• Reported - view

Fred here I am again....

the original formula had to be complicated a bit in order to give different results according to when a contract starts and ends...

I attached  a txt file where you can see the actual situation that needs to be recreated with the formula.

Here below, instead, there's my formula: I tried, but there are mistakes that I can't find (I think the problem is where there are two loops involved to get the final result).

``````let xCont := this;
let xDip := Dipendente;
let xNomDip := Dipendente.C_NomeCompleto;
let cntYear := year(DataFinale) - year(DataAssunzione);
let srtMonth := month(DataAssunzione);
let srtDay := day(DataAssunzione);
let srtYear := year(DataAssunzione);
let endMonth := month(DataFinale);
let endDay := day(DataFinale);
let endYear := year(DataFinale);
let xDataA := DataAssunzione;
let xData := DataFinale;
let xMans := MansioneCdS;
delete (select Organico)[Contratti = xCont];
switch cntYear do
case = 0:
for loop1 in range(0, cntYear) do
let loopY := srtYear + loop1;
let newRec := (create Organico);
switch srtMonth do
case < 9:
switch endMonth do
case < 9:
newRec.(
DataInizioServizio := xDataA;
DataFineServizio := xData;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont)
default:
newRec.(
DataInizioServizio := date(loopY, 9, 1);
DataFineServizio := xData;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont)
end
default:newRec.(
DataInizioServizio := xDataA;
DataFineServizio := xData ;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont)
end
end

case = 1:
for loop1 in range(0, cntYear) do
let loopY := srtYear + loop1;
let newRec := (create Organico);
switch srtMonth do
case > 8:
switch endMonth do case < 9:
newRec.(
DataInizioServizio := xDataA;
DataFineServizio := xData;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont)
default:
newRec.(
DataInizioServizio := xDataA;
DataFineServizio := date(loopY, 8, 31);
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont);
newRec.(
DataInizioServizio := date(loopY, 9, 1);
DataFineServizio := xData;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont)
end
default:
switch endMonth do
case < 9:
newRec.(
DataInizioServizio := xDataA;
DataFineServizio := date(srtYear, 8, 31) ;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont);
newRec.(
DataInizioServizio := date(srtYear, 9, 1);
DataFineServizio := xData;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont)
default:
newRec.(
DataInizioServizio := xDataA;
DataFineServizio := date(loopY, 8, 31) ;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont);
newRec.(
DataInizioServizio := date(loopY, 9, 1);
DataFineServizio := xData;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont)
end
end

default:
for loop1 in range(0, cntYear) do
let loopY := srtYear + loop1;
let newRec := (create Organico);
newRec.(
DataInizioServizio := xDataA;
DataFineServizio := date(loopY, 8, 31) ;
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont
)
newRec.(
DataInizioServizio := date(loopY, 9, 1);
DataFineServizio := date(loopY + 1, 8, 31)
Mansione := xMans;
DipendenteCdS := xNomDip;
Contratti := xCont
)

``````

Do you have any suggestions, please?

Like
• • Fred
• Fred
• 5 mths ago
• Reported - view

With something this large and complicated, I would first figure out which case is not working (0 or 1). Then break down each step in another formula to make sure each step works.

Here are some thoughts:

``````let cntYear := year(DataFinale) - year(DataAssunzione);
(other code)
switch cntYear do
case = 0:
for loop1 in range(0, cntYear) do
``````

Maybe it is me but doesn't this say that the range is from 0 to 0? You are switching on cntYear and if case = 0 then run this loop which is from range(0, cntYear) with cntYear = 0?

``````for loop1 in range(0, cntYear) do
let loopY := srtYear + loop1;
let newRec := (create Organico);
switch srtMonth do
case < 9:
switch endMonth do
case < 9:
newRec.(code...)
default:
newRec.(code...)
default:
newRec.(code...)
``````

I think you have to move your srtMonth and endMonth variable to inside the loop or Ninox will use the data from the record you are running the script from, since it is outside of the loop. Looking at your code, I could be wrong, but most of your variables will need to be in the loop so it can gather the correct data from each record in the loop.

Also, I've found that to do less than in a switch you have to do:

``````switch true do
case endMonth < 9:
(code)
``````