Company wide app

[Alex_Kemsley]

So are starting to build a new app to run our company.
Whats the best structure.
One sheet for the whole company
One app for the whole company
Or table per department interrelated
One app per deparment using a common sheet
Worries about the overhead of one table and one app on load times.

[MultiTech]

There are many things to consider, I would suggest reaching out to one of the partners to help you get started - it’s better to get off on the right foot.

solutions.appsheet.com

Working with our Partners - AppSheet

AppSheet partners are ready to create a custom mobile app from scratch, deliver AppSheet training to your organization, or add features to an existing app.

[Grant_Stead]

My general approach is one table per noun…
People
Places
Things
Ideas - that’s the hard part.

[MultiTech]

Agreed. Also one table per verb - or “thing” that people are keeping track of.

• If you want to track time logs for projects, that one;
• if you wanted to add notes to projects, that’s another.

If I detail out the hierarchy of my main time-tracking app I use for tracking the progress of my projects, it looks like this:

• Clients
  • Projects
    • Phases
      • Logs
    • Notes
• Invoices
  • Invoice_Notes
  • Line_Items
    • Line_Item_Notes
  • Invoice_Payments
• Users

Plus a bunch of supporting tables to hold various lists.

[James_McFarlane]

When you get this complex what are on average app sync times?

[MultiTech]

I count something like 18 or 19 seconds.

I should note: this has two years worth of data in it (I’ve yet to go through and clear things out from last year… #LazyAss).
This app is also a dinosaur - the need for a rebuild has been ever-increasing. It was built 2 years ago, and there are a lot of workarounds built in that are now actual features of the platform.

So this is kinda worse case scenario here.

---

Helpful Tip: The animated circle thing takes about 1 second to get from one corner to the other - that’s what I was pointing out with the pointer.

[Grant_Stead]

Usually it’s ideal to break things up a little… I can typically keep enterprise applications in the 5sec range by designing them well with different user classes, or use cases.

[MultiTech]

Grant_Stead:

keep enterprise applications in the 5sec range

Agreed.

[James_McFarlane]

@MultiTech_Visions your app looks awesome. thanks for sharing.

@Grant_Stead mine is in the 18 to 20 seconds range so it is high. The table with most records has 18,000. going to reduce some fat.

[Grant_Stead]

Yeah, that needs some good filtering, and SQL… Or even database partitioning!

[David2]

The issue of sync time has been on my mind a bit lately, my app which I use to manage projects has about a short sync time, but it will accumulate a mass of information over the years to come, does large amounts of information slow the app down, or does having to many tables slow it down?

If large amounts of information slow it down, is it general practice to delete information when it’s no longer required? Can this information remain in the database but appsheet just doesn’t load it? It’d be nice to use an app for the life of it, without having to delete data.

@Grant_Stead Do you build and deploy enterprise applications for yourself and/or other people? As it be nice to get someone with a vast knowledge of appsheet to overlook my application to offer any suggestions on my general layout and setup of tables and information handling.

[Grant_Stead]

I could take a look…
In general you want to keep the number of records sent to the device in the 10k range… So you need good filtering and/or data partitioning… If you’re using a database then appsheet pushes the security filters to a SQL side query, which is usually pretty fast…

[Austin]

The biggest factor is a combination of amount of records and what you’re doing with those tables in the way of virtual column. We have an app with 100k rows but like 1 concatenate virtual column with around a 5s sync time and we have another app with maybe a few thousand records but somewhere in the neighborhood of around 100 virtual columns across the whole app and it has a 5s sync time. The more complex app used to have around 50k rows and had a 20-30s sync time before we separated historical and current data to make 2 different apps with 5s syncs each. If you’re making a whole system in appsheet you might want to take this approach of different apps for things that have very different needs. We have 1 app to manage all people, permissions, departments and account. 1 app to do the day to day work in. And last but not least an app to view previous work.

[Grant_Stead]

Curious… Do all 100k records get actually sent to the device or does it get filled down some how?

[Austin]

While we had 3-4 columns we let every row get sent to the app due to simplicity but once we expanded to about 10 columns we decided to filter it down and now its only a couple seconds.
(note these are from databases not sheets)

[Grant_Stead]

That’s cool.

[David2]

Ahh wow, some good points made here, @Austin_Lambeth I like the idea of not entirely deleting the old data, but just moving it to a second app with historical data. Perhaps after a year of a job being completed, I could move it to a secondary app. Does this involve moving the data to a different table/database? Or leaving it in its original table/database and just filtering it out?

Your suggestion of a database is also a good idea @Grant_Stead, I currently use google sheets and was not even aware a database was an option with appsheet. What is your pick when it comes to database providers? I believe google offered databases but this might have been with their appmaker which is no longer available.

[Austin]

We use the same database since the historical still holds lots of references elsewhere but we move it to a new table. This lets you get rid of virtual columns that might be for use by actions or workflows or only make since when you’re working with the data. This also allows you to make the table read-only which lets you enable server caching of the table. This really helps as the data doesn’t rapidly change and if it does change and that change is delayed its much less of an issue to wait an hr.
I don’t personally have a ton of experience with how much server caching helps performance just due to this being our only historical app.

[Koichi_Tsuji]

For the small set of data table, Virtual column is not an issue, not bring a visible effect to the sync time. However, the number of records we need to read from the data source reach to some thousands, the sync speed gets unacceptably slow. It is common, expecially we have expression in VC, like select(XX) type of expression which should be releatively expensive.

As far as possible, I make it an own rule to avoid the use of Virtual column with expensive expressions, but sometime it is unavoidable.

In terms of moving to scaled data source, like SQL, there will be pros/cons.

For instance, server cache, i.e. for read only data table, it works well when we host data on spreadsheet, like googlesheet. However, as far as my past experiences is concerned, Appsheet keep reading whole table on SQL when it come to READ-ONLY as well as server cache being turned on. This is long running issue, yet solved, as far as I know.

Moving data to SQL, and set some sort of “indexing” will improve the performance for sure, but if we have thousands and millions records with read only set up, SQL is not give the better performance, as it keep reading whole table all the time.

This is not an issue easily solved.