Hi Community
I am editing my app to provide daily information to my CEO with regards to Staff attendance and Client attendance including sick leave etc.
We wish to retain “todays” data as historical and tomorrows data as viewable in the app. Is there anyway I can automate the app to save the spreadsheet in the background or create another tab on the app to view historical data.
Hi @khalai
Welcome to the community !
You may want to use Slices, and create views based on these.
Slices are a fraction of the spreasheet, that matches a row filter condition.
Such conditions can be:
khalai:
We wish to retain “todays” data as historical
[TimeStamp]=TODAY()
khalai:
tomorrows data as viewable in the app
[TimeStamp]=TODAY()+1
Then, for settings each UX view on the correct slice:
For reference:
Hi Aurelien
where is the slices option?
Your filter condition must be applied to a column with date or datetime.
This should be a Yes/No condition.
I don’t see a date/dateTime column on your screenshot ?
your filter condition will be:
[Date DD/MM/YY]<TODAY() if you want “earlier” rows
[Date DD/MM/YY]=TODAY() if you want “today” rows
[Date DD/MM/YY]=TODAY()+1 if you want “tomorrow” rows
Below is the sheet the app is updating on a Sharepoint Site however it is overwriting. What would you suggest would be the best way to save yesterday, archive and then go again with new data today. The archived data for any day should be recallable from within the app.
| Centre | 9AM | 1PM | 5PM | Staff | Date DD/MM/YY | H | C.S | O.S | C.C | I |
|---|---|---|---|---|---|---|---|---|---|---|
| Dawson | 77 | 111 | 0 | 8 | 05/01/22 | 1 | 0 | 0 | 0 | 0 |
| Birchin | 41 | 93 | 0 | 4 | 05/01/22 | 0 | 1 | 0 | 0 | 1 |
| Finsbury | 38 | 46 | 0 | 3 | 05/01/22 | 1 | 0 | 0 | 0 | 0 |
| Southwark | 49 | 60 | 0 | 3 | 05/01/22 | 1 | 0 | 1 | 0 | 0 |
| North Row | 32 | 33 | 0 | 4 | 05/01/22 | 0 | 0 | 0 | 1 | 0 |
| Meridien | 15 | 0 | 0 | 4 | 05/01/22 | 0 | 1 | 0 | 0 | 0 |
khalai:
the best way to save yesterday, archive
I don’t think you need to do so.
I would suggest to just use slices if you need to hide these and retrieve these for specific purpose.
Did you read the Slice article ?
I will read in detail tomorrow and maybe try in the sandbox. Thanks for your help so far.
khalai:
Is there anyway I can automate the app to save the spreadsheet in the background or create another tab on the app to view historical data.
To be clear, AppSheet does not provide a way to create, delete, or rename worksheets within a spreadsheet file.
It seems like you are using a single row for each person and the values for today are inside a column of the users table. I’m right?
You need two tables at minimum.
One for Users
Another for Records.
You could create a bot to make equal amount of rows from Users on Records and make them available to enter info each day, leaving the old records from Records as they were. You could even make them uneditable.
Maybe I’m wrong, but that’s what I understanded
I my spreadsheet I posted earlier all figures in rows and columns change on a daily basis.
Before they are changed and at the end of each day I would like to save a copy so that it can be reviewed at a later date. Saved (archived) data should be able to be retrieved on a date basis.
OK, everything makes sense then.
In this case, you may want to make a bot, and use it with a “schedule event”, in order to run this data action:
in order to create a history of all informations saved, from your “main” sheet to your “history” sheet (sheet= table).
Maybe also, you may want to add a process in your bot in order to “initialize” the Date column to the new “today”.
For reference: (Bots are in the “Automation” panel)
Do that work ? I never tried XML output.
Mind the Time Zone, unless you are in GMT TimeZone
Nothing happened so far. I am moving offices today and tomorrow so may need your help next if possible?
You can post your achievement here, me or any other person in the community will be happy to provide help
