Ok. I give. Iโve been trying to figure this out for over a month and I feel like it should be something thatโs โeasyโ/already in AppSheet that Iโm missing:
I want to archive past records based on a date in a column. ([DATE OF APPOINTMENT] is over 7 days old)
I want to archive, not delete. (I have a second tab called ARCHIVE to which Iโd like to save past appointments.
The [rownumber] being part of the KEY seems to screw everything up when I try to do it manually (duplicates records because I assume itโs assigning a new KEY based on the past rows being deleted and everything moving up). I tried changing the KEY but then it wouldnโt save any new info (again, assuming the app couldnโt find the record for some reason?)
In any case, Iโm guessing everyone has to get rid of the old records so as to not bog down the system, but I just canโt figure it out. (Iโve messed around with Google Scripts, but havenโt been able to find one that works for me)
I want this to happen automatically, like a time-based trigger, but Iโm still kind of stumped by the row number/key and how that affects the records with dates that arenโt in the past if rows are deleted?
Thanks in advance for any help!!!
What did you use as a new key instead of the [row number], that did not work? Looking at the spreadsheet, it seems one large flat table with no good candidate for a unique key. Have you read these articles below? I recommend to read them if you have not yet. But off the top of my mind, you could add a new column with unique values in it to become your new Key column.
http://help.appsheet.com/data/keys/what-is-a-key
http://help.appsheet.com/data/keys/manually-generating-uniqueid-key-values
Also these ones could help with improving your data model: http://help.appsheet.com/app-design/app-design-101
Generally a bad idea to use the row number as key. The row number could change for any number of reasons, especially if anyone ever uses the spreadsheet directly (someone might sort the sheet, or delete a row, for instance).
So first thing would be to find a way to create a key for each row that is independent from the row number. As @RezaRaoofi said, thereโs no obvious good candidate in any single existing column. There might be a good combination of several columns that together would be a good key, but thatโs something youโd have to determine. Another possibility (also as Reza noted) would be to add a new column and assign a key value for each row. This option would be problematic if you intend to use the spreadsheet both directly and through your app. Generally speaking, itโs a bad idea to work with a spreadsheet directly once itโs in use by an app.
So given the above, I second Rezaโs suggested reading list for you.
Iโm not familiar with Zapier, so I canโt help there.
You can use Zapier with AppSheet as described here help.appsheet.com - Connect a Zapier โZAPโ to Your App Connect a Zapier โZAPโ to Your App help.appsheet.com
On to the issue of archiving.
There is no easy way to move rows from one worksheet to another with AppSheet.
There are two common ways to hide โarchivedโ rows, but both use AppSheet features to hide the rows from the app userโs view; neither method actively manipulates the worksheets. Consequently, neither method will help someone who is using the spreadsheet directly, as the rows will remain in-place in their original worksheet.
The big underlying question is how you are or intend to use the spreadsheet. Ultimately, if you want a successful app, you cannot routinely interact with the spreadsheet directly; once your app is connected to the spreadsheet, the app should be the primary wayโif not only wayโyou use the spreadsheet.
Also, please unshare the Google Sheet. There is too much apparent personal info in it to make me comfortable. I donโt have any authority to demand you do, but were I a customer, Iโd be peeved that my and my dogโs name were posted publicly.
@RezaRaoofi First, thanks for taking the time to answer me. I have read pretty much every article there is so that I didnโt have to come here, to no avail.
Re: the KEY this is what I had tried: I added a virtual column that generates a random number and then =CONCATENATE([CUSTOMER LAST NAME],[RANDOM #]). Iโm guessing it wasnโt saving because the records that were having issues were created with the original/current KEY: =CONCATENATE([_RowNumber],[RANDOM #])
(Also, I deleted a bunch of tabs before I uploaded the test sheets (and deleted customers contact infoโฆIโve now deleted their last names per Steveโs note)
I donโt intend to ever โuseโ the spreadsheetโbelieve me, I know it screws everything up if you mess with itโthis is the only thing I need to figure out. Weโve been testing it for a month or so and itโs working great (except when Iโve been trying to figure this โarchivingโ thing out and then it gets wonky, so I change it back and itโs fine).
My biggest concern is having too much data/records in the app/on the spreadsheet. Is this not something I need to worry about? What do most people do to prevent this? Just delete the old records?
I need to save the notes for the current appointment, so my plan was to use Zapier to save those to their customer file based on the โSTATUSโ column (can I use Zapier with AppSheet or is that an issue, too?)
This is where Iโm thinking Iโm missing something super obviousโhow to save important information that is input by the user without bogging down the system.
+Steve Coile you mentioned โThere are two common ways to hide โarchivedโ rows, but both use AppSheet features to hide the rows from the app userโs viewโ but not what they areโcan you let me know what they are?
Thanks again!
The two common ways to hide rows are slices (Data > Slices) or security filters (Data >Tables > table > SECURITY > Security filter or Security > Security Filters > table > Security filter).
Slices offer a variety of features beyond just hiding rows, but donโt really improve performance.
Security filters prevent the affected rows from reaching the device, which both improves security and improves performance when large data sets are involved.
Security filters can add some complexity to app design since the filtered rows literally wonโt exist from the appโs perspective, even though they are present in the spreadsheet.
Slices also add complexity, but the benefits are strong, too.
I would recommend starting with slices.
Whether slices or security filters, you would configure either to omit rows marked with ARCHIVE, and/or to omit rows with a DATE OF APPOINTMENT value older than 7 days.
@Steve, What is the best way to set up a hide row slice? I would like to hide completed items after a week of completion. I guess Iโm not really sure what the code would look like.
I donโt want the information deleted just the older rows hidden.
Is anyone willing to share some examples of what theyโve used?
Thanks!
Hi @Dani_Mittler
Have you checked out slices?
Thank you for removing the last names!!!
For your key column, I would recommend you create a dedicated column for use solely as the key column, set Editable_If for the column to =FALSE, and set its Initial value to =UNIQUEID(). When a new row is added, the initial value formula will set the rowโs key to a random value thatโs very, very unlikely to ever occur again. The Editable_If formula will prevent any app user from changing the key value should they ever gain access to it.
You may use other column values to construct the key value, but I donโt recommend it.
Unless your worksheets have tens of thousands of rows, I wouldnโt be concerned about performance. Your data is not particularly well-structured for efficiency, so thereโs room for improvement, but I wouldnโt expect the ability to archive old records would provide a noticeable win, at least early on.
User | Count |
---|---|
36 | |
34 | |
27 | |
23 | |
18 |