Okay, the last question I asked that I thought was resolved, but apparently it didn’t solve the issue.
I have the following setup
Column B: [Date]
**Column K: [**Count]
**Column L: [**Day to recur] every month
Column M: [Next recurring] event date
This is a formular calculated field containing Edate(), Eomonth(), Month(), If(), and based on value user enters into column L, which day to occur every month. So, I can’t make this an in-app expression field.
Now, with recurring action, I’ve setup the following steps:
-
Add a new row, [Date ]= [Date], [Count] = [Count] [Day to Recur] = [Day to Recur]
-
Set the value of the columns in this row (the initial row): [Count] -1, [Date] = [Next Recurring]
-
Action to repeat step 1 & 2.
Below as example, starting with the Initial data in a Row:
[Date] = 1-Jan-23, [Count] = 3, [Day to recur] = 30
[Next Recurring] is auto calculated on Googlesheet, the value in [Next Recurring] = 28-Feb-23
By using the action below, that I should be able set a recurring event for every month on the 30th day, until my count value = 0.
If it’s Feb, the day will be set to end of the month, if 30th happens to be a Sunday, then it moves the recurring day to the next day. All these have been set by formular and is working correctly on Googlesheet, so I just need to bring this into the App, so it shows in the calendar view.
Action step 1, Add a new Row (duplicate the initial row):
[Date] = 1-Jan-23, [Count] = 3
[Next Recurring] is auto calculated on Googlesheet, value in [Next Recurring] = 28-Feb-23
Then, Action step 2, The Initial row gets updated to:
[Date] = 28-Feb-23, this is done by action [Date] = [Next Recurring]
[Count] = 2, this is done by action [Count]-1
Based on the new [Date] = 28-Feb-23, my [Next Recurring] is auto calculated on Googlesheet, the value is now [Next Recurring] = 31-Mar-23
Action step 3,
Repeat Step 1 & 2, until [Count] < 1
However, when the action 3 (Rinse and Repeat) starts, the [Date] value no longer gets updated to [Next Recurring] value as it supposed to happen in step 2 described above. It only happened on the initial loop, but not any subsequent loops.
I’ve tired many other ways, by adding Actions in between step 1 and 2, to make a new column called [Copy Next Recurring] that copies the date value in [Next Recurring], in hoping this will turn the new column into a static value column
Then, set the [Date] = [Copy Next Recurring], but it still wouldn’t work.
I’ve tried the solution others suggested from my previous post by using =Lookup( Max()), but still wouldn’t work. It fetches the value correctly, but the date column will be blank, something is restricting it from being updated by using =Lookup(Max()) from a formula calculated column.
I’ve already have the [Date] column unchecked from is Required, and made sure it can be updated.
Then at last, I tried to use a static value by setting the [Next Recurring] to 7 (a number), and it worked all perfectly.
The date value for each following row becomes 7 days greater than the previous row. (A recurring event for every 7 days)
Therefore, my problem is that the [Next Recurring] date HAS to be formula calculated on my Googlesheet, and it can’t be a static value.
Is there any method that could solve my issue?
Thank you.