Getting a virtual column to generate list of dates in a form with complicated spacing options

Encrafted_0-1713111337151.png

It makes the most sense to start with the form. So, basically from a start date this is going to generate scheduled session days from the options selected. I want to have a virtual column in the form preview those dates. I wrote the code that I thought would do that, or at least if it didn't fail in ways I could troubleshoot. Instead the expression editor tells me I'm using MAX(), COUNT() and probably other things incorrectly (and I might be missing a parentheses but I tested out a section which wasn't and still got told off).

All I want to know, really, is what to use to refer to the latest item in my recursive-generating list. MAX([_THIS]) is apparently not it. And it doesn't like me using COUNT([_THIS]) either. Both of which are pretty essential to my code functioning.

The code is pretty onerous so I wrote it all out in a normal text editor so I could colour-code things and add comments to make it easier to keep track of. I can't seem to copy the formatting directly so I'll paste screenshots then include the actual code in a block at the end.

The colours aren't super consistent so don't pay them too much mind.

Encrafted_1-1713111964910.png

Encrafted_2-1713112025382.png

Encrafted_3-1713112052755.png

 

{[Sesh date],
IFS([Sesh repeat]>0,
IF(COUNT([_THIS])<COUNT(([Sesh day])*[Sesh repeat],
IF (COUNT(([Sesh day])=1,
SWITCH([Repeat spacing],
โ€œweeklyโ€, (MAX([_THIS]) +7),
โ€œfortโ€, (MAX([_THIS]) +14),
โ€œ3-weekโ€, (MAX([_THIS]) +21),
โ€œ4-weekโ€, (MAX([_THIS]) +28),
โ€œmonthlyโ€, 
IF(
SWITCH(
  EOWEEK([Sesh date]),
  EOWEEK(EOMONTH([Sesh date], -1) + 1),
    1,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 7),
    2,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 14),
    3,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 21),
    4,
  5
) =5,
(EOMONTH((EOMONTH(MAX([_THIS]), 0) + 1), 0) + 
MOD(
([Sesh day] - WEEKDAY(EOMONTH((EOMONTH(MAX([_THIS]), 0) + 1), 0)) - 7), 7
)
),
(
(((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 
MOD(
([Sesh day] - WEEKDAY((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 7)
, 7)
) 
+ ((
SWITCH(
  EOWEEK([Sesh date]),
  EOWEEK(EOMONTH([Sesh date], -1) + 1),
    1,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 7),
    2,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 14),
    3,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 21),
    4,
  4
) 
- 1) * 7)
) 
),
(
(((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 
MOD(
([Sesh day] - WEEKDAY((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 7)
, 7)
) 
+ ((EXTRACTNUMBERS([Repeat spacing]) - 1) * 7)
) 
), 
IF(
WEEKDAY(MAX([_THIS])) = MAX([Sesh day]),
SWITCH([Repeat spacing],
โ€œweeklyโ€, (EOWEEK(MAX([_THIS]), 0) + MIN([Sesh day])),
โ€œfortโ€, (EOWEEK(MAX([_THIS]), 0) + MIN([Sesh day]) +14),
โ€œ3-weekโ€, (EOWEEK(MAX([_THIS]), 0) + MIN([Sesh day]) +21),
โ€œ4-weekโ€, (EOWEEK(MAX([_THIS]), 0) + MIN([Sesh day]) +28),
โ€œmonthlyโ€, 
IF(
SWITCH(
  EOWEEK([Sesh date]),
  EOWEEK(EOMONTH([Sesh date], -1) + 1),
    1,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 7),
    2,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 14),
    3,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 21),
    4,
  5
) =5,
(EOMONTH((EOMONTH(MAX([_THIS]), 0) + 1), 0) + 
MOD(
(MIN([Sesh day]) - WEEKDAY(EOMONTH((EOMONTH(MAX([_THIS]), 0) + 1), 0)) - 7), 7
)
),
(
(((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 
MOD(
(MIN([Sesh day]) - WEEKDAY((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 7)
, 7)
) 
+ ((
SWITCH(
  EOWEEK([Sesh date]),
  EOWEEK(EOMONTH([Sesh date], -1) + 1),
    1,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 7),
    2,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 14),
    3,
  EOWEEK(EOMONTH([Sesh date], -1) + 1 + 21),
    4,
  4
) 
- 1) * 7)
) 
),
(
(((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 
MOD(
(MIN([Sesh day]) - WEEKDAY((EOMONTH(MAX([_THIS]), 0) + 1) - DAY(EOMONTH(MAX([_THIS]), 0) + 1) + 1) + 7)
, 7)
) 
+ ((EXTRACTNUMBERS([Repeat spacing]) - 1) * 7)
) 
), 
(
MAX([_THIS]) +
INDEX(
[Sesh day],
(COUNT(
 SPLIT(
   LEFT(
 CONCATENATE([Sesh day]),
 FIND(
   WEEKDAY(MAX([_THIS]))
CONCATENATE([Sesh day])
 )
),
 " , ")
)+1)
- (WEEKDAY(MAX([_THIS])))
)
)
)
)
)
)
}

 

 

1 3 311
3 REPLIES 3