List of years/months/weeks...

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?

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

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
    alert("No End Date")
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.

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
    alert("No End Date")
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.

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
            alert("No End Date")
        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
            alert("No End Date")
        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.

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)

See this post for more info.