Mass adding of templated rows and working wit...

AppSheet AppSheet Q&A
1

Mass adding of templated rows and working with dates via columns:

I have a project for the local “meals on wheels” for the elderly.

There are around 60 people who have meals delivered daily.

Every month needs to be replicated, and then changes made to that months planning.

Weekends need to be identified within the month reporting.

My first thought was to set up a table with [Name], [Date], Order.

However replication of that for an entire month for 60 people is 1,800 rows and I don’t know if this is possible.

My second thought was to have a table with columns [Name], [Month], [1], [2], [3] etc…

Where each column represents a day of the week of the stated month.

a y/n in each day column would reflect an order for that day.

Now I only have to replicate 60 rows each month.

Does anyone have any opinions for me to get started?

Option1 lends itself to easy calculation of weekends - It must be possible in option2 but with more work?

I’m assuming for every option2 record I could create virtual columns that would return a day of the week for every date calculated from the month plus column header (max 31 virtual columns) and somehow correlate (tag) the appropriate virtual column to the main column.

And ultimately can records be auto created as intended?

If the answer is yes I’m sure I can work it out, but if not I’ve saved myself time by asking the question.

2

@Lyndon_Ball Right now AppSheet isn’t capable of automatically creating rows every month. For that you’ll need to use a script or a pivot table. I’m hoping other community members can chime in with their ideas. This stackoverflow post might be a good place to start, when generating your list of all people * all dates. stackoverflow.com - Google sheets query - Cartesian join between two ranges

I’d recommend going with your first option, of using Name/Date/Order as your column structure. If you need to identify weekends, you can use the WEEKDAY formula https://support.google.com/docs/answer/3092985?hl=en&ref_topic=3105385 Google sheets query - Cartesian join between two ranges stackoverflow.com

3

Not sure if I’ve read this correctly, but I’ll have a go.

Create a table with

[name], [month], [days].

[Days] is an ENUM with options of 1,2,3,4,5,6…31.

So to book someone’s collections you’d choose their name, the relevant month and tick off the days they are having a delivery.

Then to show each day’s deliveries you’d need a slice something like:

AND( Table[month]=Month(Today()), IN(Day(Today()),[Days]) )

4

@Simon_Robinson, that is an interesting alternative that I hadn’t thought through.

I now have three possible ways of solving.

While it saves on columns used I’ll still need to extract total orders for a day (so the kitchen knows how many meals to cook that day) and work out which days are weekends weekends command a different funding rate per meal.

I’m sure this is achievable through an enumlist but how will multi-options be stored, and how would I extract the itemised data?

It’s a semi rhetorical question as I realise it may become clear one I have a play with the different options.

@tony.

Thank you for your pointer to stackoverflow.

I know your link appears to be focussed on google sheets query, though I’m guessing sheets is capable of scripting also and is what you were suggesting?

I learnt the basics of VBA a decade ago but trying to stay away from coding is what interested me in appsheet in the first instance.

My ability to assimilate learning isn’t the same as it used to be.

It would be great if appsheet could have a trigger to self-populate records from a templated record.

is there some way to feature request this, or has it been done already?

5

Part of the difficulty here is your apparent expectation that you app will operate without user interaction: that new rows will automatically add themselves to account for the passage of time. The app can’t do that itself, so you’d have to find some external mechanism.

My though would be a different approach: instead of a new row for every participant every day, have a row for each participant that remains in effect every day (i.e., reuse the same row) and add a row only when the normal daily row needs a change. Record the exceptions rather than routine occurrences.