Ok. I give. I've been trying to figure this o...

Blair_Smith · 01-12-2019 04:00 PM

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!!!

RezaRaoofi

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

http://help.appsheet.com/data/data-the-essentials

Steven_Coile

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.

Steven_Coile

I’m not familiar with Zapier, so I can’t help there.

Phil

@Blair_Smith

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

Steven_Coile

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.

Steven_Coile

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.

Steven_Coile

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.

Blair_Smith

@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!

Steven_Coile

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.

Dani_Mittler

@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!

Lynn

Hi @Dani_Mittler
Have you checked out slices?

Steven_Coile

Thank you for removing the last names!!!

Steven_Coile

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.

Steven_Coile

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.