Re: Getting a virtual column to generate list of d...

Encrafted · 04-14-2024 09:29 AM

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])))
)
)
)
)
)
)
}

Encrafted

I think I'll have to make a looper column to replace [_THIS]. It's just another virtual column with a formula pointing to my virtual preview column. Doing that stops AppSheet complaining about the MAX() function, anyway. I'm finding other mistakes in my code along the way too.

MultiTech

You need Appster in your life! (^_^)

https://chat.openai.com/g/g-ZG9TkPOrl-appster

That's quite the formula, haha, and might take a long time to try and comprehend... so I fed it to Appster to see what it could do with simplifying the formula for you. 🤖🧠💪

Not sure if the logic is right, it might be missing some of the nuance from the deeper layers; you'll have to check and see if it does what you're wanting.

___________________________________________________________________________

Here is the formula provided by Appster-lite (the free-ish version in ChatGPT)

{[Sesh date],
IFS([Sesh repeat]>0,
    IF(COUNT([_THIS])<COUNT(([Sesh day])*[Sesh repeat],
        SWITCH([Repeat spacing],
            "weekly", (MAX([_THIS]) + 7),
            "fort", (MAX([_THIS]) + 14),
            "3-week", (MAX([_THIS]) + 21),
            "4-week", (MAX([_THIS]) + 28),
            "monthly",
                IF(EOWEEK([Sesh date])=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))
                    + (((EOWEEK([Sesh date]) - 1) * 7))
                ),
        MAX([_THIS]) +
        INDEX([Sesh day],
            (COUNT(SPLIT(LEFT(CONCATENATE([Sesh day]),
                FIND(WEEKDAY(MAX([_THIS])),
                    CONCATENATE([Sesh day])
                    )
                ),
                " , ")
            ) + 1)
            - WEEKDAY(MAX([_THIS]))
        )
    )
)}

Simplification Explanation:

Removed Redundant IF Statements: The conditions inside the SWITCH handle different repeat spacings. We used a direct mapping for weekly to 4-week frequencies.
Consolidated Monthly Calculation The nested IF for monthly scheduling now checks only once if the week of the month is the fifth week and applies the necessary date calculations accordingly.
Simplified Index Calculation The index retrieval for [Sesh day] has been cleaned to remove unnecessary CONCATENATE calls and simplified the logic for finding the correct index.

This cleaned-up version should be easier to maintain and understand, focusing on the primary logic without repeated code patterns. Remember, for further optimization and to ensure it fits your specific use case, testing within the context of your AppSheet application is essential.

Continue the convo here

-----------------------------------------------------------------------------------------

And then (just for fun) I fed it to the pro version in the Answer Portal, to see if it's answer was different/better.

It seems you're working with a fairly complex AppSheet formula that calculates the next session date based on various repeating patterns such as weekly, fortnightly, and monthly. To simplify this, let's break down the formula into smaller, manageable parts.

The basic logic of your formula is to check if `[Sesh repeat]` is greater than 0 (meaning there is a repeating schedule) and then calculate the next session date based on `[Repeat spacing]`, which can be weekly, fortnightly, 3-week, 4-week, and monthly patterns.

Here's a simplified version of the formula, assuming some parts of your original expression could be simplified or are repeated:

{
    [Sesh date],
    IFS(
        [Sesh repeat] > 0,
        IF(
            COUNT([_THIS]) < COUNT(([Sesh day]) * [Sesh repeat]),
            SWITCH(
                [Repeat spacing],
                "weekly", (MAX([_THIS]) + 7),
                "fort", (MAX([_THIS]) + 14),
                "3-week", (MAX([_THIS]) + 21),
                "4-week", (MAX([_THIS]) + 28),
                "monthly", ...monthly calculation...
            )
        ),
        ...other conditions if required...
    )
}

In the "monthly calculation" section, you'd need to insert the logic you have for calculating the correct monthly date based on your needs. It looks like you're trying to calculate the date taking into account the end of the month and specific weekdays, which is considerably more complex due to calendar variations.

For assistance with the "Start of Week Formula" and determining the start of the week from a given date, you can utilize the formula provided in the "Resources & Tools" section under the "Start of Week Formula," which is:
```
[DATE_COLUMN] - (WEEKDAY([DATE_COLUMN]) - 1)
```
This formula will give you the starting day of the week (Sunday) for any given `[DATE_COLUMN]`.

Please note that to provide a full simplification, more context about the exact requirements would be necessary. The formula above is a simplified structure and will require you to insert the appropriate monthly calculation. If you need further help with specific parts of the monthly logic, feel free to provide more details or check out available resources in the "Resources & Tools" section for additional guidance.

=============================================================

Kinda looks like the lite version had a better answer. 🤔... (Time for some tinkering)

Hope it helps!

Joseph_Seddik

Look here: Target Workday Calculator - Google Cloud Community

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