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.
{[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])))
)
)
)
)
)
)
}
User | Count |
---|---|
18 | |
14 | |
11 | |
7 | |
4 |