Hello,
I have a Schedule Table, where users insert their presence in a chosen dates. It looks like this:
User | Date | Availability |
A | 22.04.2024 | Can |
A | 25.04.2024 | Can |
A | 28.04.2024 | Can't |
B | 22.04.2024 | Can't |
B | 25.04.2024 | Can't |
B | 28.04.2024 | Can |
C | 22.04.2024 | Can |
C | 25.04.2024 | Can |
C | 28.04.2024 | Can |
I created a form where a manager can chose a period to add to this table above.
Now I need to create an action, which will add row with Date and User for each Date from the added period and for each user in the base.
Users are in table Users, some of them can be deactivated, so I need to skip them in the action.
How can I create such action?
Thanks in advance for help!
Hi @ADRN
This action you are looking to create can be referred as a "looping action".
I suggest you look for these key-words in the community: Search - Google Cloud Community
Here is an excellent post from @MultiTech that may help you as well:
Looping with Actions (Sample App BUILD Video) - Google Cloud Community
Let us know if you still struggle to achieve it! 🙂
Yeah, I did that before creating this post. Not helped much in my case.
But this is not a simple loop. It is a loop within the loop, so the level of complicity is higher.
For now I created a hybrid solution (AppSheet & Excel) - I will share in case anyone prefers this path, but it is not an ideal AppSheet-only solution:
1. Create table Periods (import to AppSheet) where an user specifies the date range to add.
Main columns used for this issue:
Period_ID
Period_start
Period_end
2. Create table Users_Dates (Excel only) which works as a variables. Columns:
- Imoprt Start (set formulas which will take the desired dates from table Periods. In my case I just used last added row)
- Import End (same as Import Start)
- DateList (set formulas to calculate an array of dates. A Filter() can be a big help here).
- UserID (import data about users in any way you like, for example by using IMPORTRANGE() )
3. Create table AddDays (import to AppSheet) - this table should have an Cartesian join of Dates and Users.
For this one I used this tutorial:
Cartesian Join in Excel
4. Using this tutorial of one loop made by Steve I created loop.
Loop works like this:
a. User specifies range (in Periods table. Period table --> Master Table in Steve's tutorial)
b. In the background in Excel, AddDays table prepares Cartesian Join based on the variables in Users_Dates table (AddDAys table --> Source Table in Steves's tutorial).
c. All rows from AddDays are added to the Target Table which will contain all users and all dates.
----------
For now it works, but during tests I had some issues with copying ID or overwriting records.
User | Count |
---|---|
36 | |
31 | |
28 | |
23 | |
18 |