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
  • Newest first
  • Active threads
  • Popular
    • Fred
    • Fred
    • 4 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
      • Gianluca
      • Gianluca
      • 4 mths ago
      • Reported - view

      Fred One List for each record. It's needed to add details about the working time when someone signs a part time contract (especially if the contract has a End Date) 

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

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

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

      Fred  it’s unimportant, but in case I could easily use the current date as end date.

      Like
    • Fred
    • Fred
    • 4 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
        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.

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

      Fred I need to try it and then will let you know, but at the moment I send the usual big “thank you” your way 😃

      Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred 

      Hi Fed my greetings, this give me God idea for me, to create a DateCutoffs, but I Need a StartDate example ( 15/ July/2021) and EndDate (24/February/ 2023) but every 7 days always Friday days, is posible change the formula ?.

      Thanks Fred

      Like
      • Gianluca
      • Gianluca
      • 4 mths ago
      • Reported - view

      Fred i tweaked it a bit to use today as EndDate in case EndDate is null (here below you can see it). Haven't tested it yet cause when I try to save the formula field, an alert warns me that "This formula might not modify the data" (both with your script and my tweaked one), so I guess I'm maybe writing the formula in the wrong place?

      I've created the table named "months". In "months" table I've created a date field named "Date". I've set a reference from "months" to my main table (-->)  and then in my main table I've created a formula field named "MonthsList" where I've put your script.

      Here is the tweaked script

      let t := this;
      let cntMonth := (year(EndDate) - year(StartDate)) * 12 + month(EndDate) - month(StartDate) + 1;
      let cntMonth2 := (year(today()) - year(StartDate)) * 12 + month(today()) - 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
              for loop1 in range(0, cntMonth2) do
              let loopMon := srtMonth + loop1;
              let newRec := (create months);
              newRec.(
                  Date := date(srtYear, loopMon, 1);
                  Table4 := t
              )
          end
      end
      

      PS - the day of the month is not important, so if the formula works it's fine this way.

      Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Gianluca Hi Gianluca,  use this formula in a button, works fine

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

      Rafael Thanks  :) Does it necessarily have to be in a button? can't it be put somewhere to perform the loop without any action required by the user?

      Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Gianluca At this point I don't know Gianluca, I have tried to use the formula  on field but it give the same error.

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

       Since it is modifying data, it can be in:

      1. a button
      2. in a Trigger
      Like 2
      • Fred
      • Fred
      • 4 mths ago
      • Reported - view

      Gianluca Just wondering what will you do for records with no EndDate in the next month after you run it? For example, if you ran it today, your months will stop in July 2022. What happens in August 2022 which you didn’t create? Since they are still working don’t you need to keep adding months?

      This code does not check for previously created so if you run it again it will recreate a duplicate set of records.

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

      Fred smart point…

      actually this script should be run only once after a contract ends, so it wouldn’t be a problem. But if you run  it by mistake before you need to, you will get an incomplete list…and it would be much better if this can’t happen.

      A possible solution, maybe:

       since the list is created from the main table (which is ‘contracts’), all the records created in ‘months’ table are directly connected to one of the contracts (and its unique ID)

       If the script could have a further  instruction to first delete from the ‘months’ table all the records connected with the current contract ID, and then create the new list, you could run the script all the times you want and have only one list per contract.

       Would that be possible? And would that work?

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

      It is possible and it is one solution. It is a solution I use in one of my tables.

      The only issue I can see is you want to add notes or some other data to the month records and thus if you delete everything the add them again you will lose data.

      But if you are only supposed to run it once, then maybe you can do a display only if on the button to only show when EndDate is not null and count of months is equal to 0. So it checks to see that there is an end date and there are no related records in months so you will need to create them.

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

      Fred Hello :)

      I've come across another need.

      The formula works perfectly in a button and does everything I need to, creating the list of months for the contract in use.

      However, the same person might have multiple contracts and at the moment I have to create the months list from inside every contract. Would it be possibile to adapt this formula so that with just one click of the button in whatever contract form related to the same person ID, it creates the months list for all the contracts?

      Like
    • Fred
    • Fred
    • 4 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
        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.

    Like 1
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred 

      Hi Fred I have done a test from1/April to 30/Dec same year 2022, and work witout a bug on Android Tablet. I will continue testing from year to another year 

       

      Thanks Fred

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

      Rafael Maybe it is a MacOS only issue.

      Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred I testing from 7/Jan 2022 to 29/Dec 2023 (104 weeks) and no bug Fred.

      Again Thanks 

      Like
      • John Halls
      • John_Halls
      • 4 mths ago
      • Reported - view

      Fred Hi Fred I found the same bug last week. I was projecting stock going forward 200 days and it would work for the first 100 or so and then fail. It was solved by using the date() function, so try

      Date := date(t.StartDate + loop1 * 7)
      

      Regards John

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

      John Halls Thanks for the info. It didn't do anything for me.

      I created a new DB to send off to Ninox but could not duplicate it. Which got me thinking that the new Time-Zone independent feature may have fixed it. The DB I was using for testing is an old DB.

      Like
    • Fred
    • Fred
    • 3 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
                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.

    Like 1
      • Gianluca
      • Gianluca
      • 2 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?

    • Fred
    • Fred
    • 2 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)
    

    See this post for more info.

    Like 1
Like Follow
  • 2 mths agoLast active
  • 31Replies
  • 178Views
  • 4 Following