Another scalability question

[pro100bear]

I have a question regarding scalability and establishing the right workflow. I'll provide as much information as I can for clarity. For context, our company is involved in the distribution of chemical liquids. We handle the pouring of these liquids directly at our warehouse. To manage our inventory effectively (among other reasons), we are considering the development of a custom software solution. While AppSheet seems like an almost perfect fit for our needs, I'm somewhat puzzled about how its security filters function.

Our plan is to maintain a several MySQL database and tables. To illustrate:

• 1st database: Contains all orders.
• 2nd database: Details all items from these orders.
• 3rd database: Dedicated to fulfillment.

At present, we have approximately 37,000 orders (sourced from Shopify) that need to be transferred to the 1st MySQL database. I estimate that the 2nd database will have roughly 5 * 37,000 = 185,000 rows. Additionally, we have other sources of orders, such as those from private clients or B2B, where orders are processed manually, among others. The 3rd database, which is for fulfillment, will likely be the largest, with an estimated 500,000 rows.

While I have viewed several tutorials on YouTube, most of them address straightforward use-cases, like displaying data specific to a user profile. I'm clear on that aspect. However, I'm seeking guidance on more complex scenarios, such as:

• Loading all unfulfilled orders.
• Reviewing all fulfillments from the previous day.
• Viewing all orders for a particular month.
• Analyzing data for a specific liquid.
• And so on.

I anticipate approximately 30-40 different filtration scenarios. My primary confusion lies in how to incorporate all of these into a single security filter setting within AppSheet. Please, can someone explain me this part?

Thank you!

[AleksiAlkio]

Difficult to give any speciic guidance because it depends what these search conditions are , in what table etc. But in generally speking.. as you are using a real db as a data source, you need to write your security filters in a way that the they are pushed to SQL query. Otherwise the AppSheet server first needs to read all rows from MySQL and then filter it. If you have like 800k rows, it will definitely affect app's perfomance. Please check this article with more details how to do it.. https://support.google.com/appsheet/answer/10104706?hl=en&sjid=12538706083253388523-EU#:~:text=Secur...

[pro100bear]

Thank you! That is exactly what I don't understand. All the examples on the Internet are about filtering data based on USEREMAIL, for example. Am I right it does not have to be USEREMAIL all the time? We can use other parameters and don't be attache to USEREMAIL?

How I see it:

• Let's say I need to see all unfulfilled orders.
• We create a table view (based on a slice) and filter this slice based on [Status]="Not Started".
• Am I right I can somehow include this [Status]="Not Started" in security filter?
• And then I can include many other conditions for other reference views too?

[AleksiAlkio]

The filtering with the slice is not happening in DB. It's happening in app user's device. It means.. all the data is first fetched and then sent to app user's device. Not a good solution from the performance perspection.

IN(), AND(), CONTAINS() and like <, >,= you are able to push to DB query. But, that's not the whole truth. If you have other expressions used inside of the IN(), AppSheet server pushes them as parameters. You can also use OR() when the condition is possible to filter before push to query.

If you write a security filter for example something like..
OR(
USEREMAIL()="name@domain.com",
IN([ProjectID],SELECT(Projects[ID],[Date]=TODAY()))
)

First the AppSheet server identifies if the app user is correct. If it is, it doesn't need to push anything because it should fetch all data. If it's not, Then it pushes only the IN() part of it like ProjectID InList "asduyt53","asdlkj12","6ftgecrd",etc... to your DB before the data is fetched to AppSheet server.

[pro100bear]

---

@AleksiAlkio wrote:

The filtering with the slice is not happening in DB. It's happening in app user's device. It means.. all the data is first fetched and then sent to app user's device. Not a good solution from the performance perspection.

---

That is right. It is unfortunate. But I understand the concept 🙂

I meant to use security filter to extract data for all views and slices and then using slices to show only need data from the extracted list.

In your example you also using USEREMAIL. Do we also have to some some kind user identification for security filters?

[AleksiAlkio]

No, if that's not needed. You can use it for example for admins etc. that are allowed to see all the data.

[Aurelien]

In addition to @AleksiAlkio 's tip: 

Scale using data partitions - AppSheet Help

and more generally: Scale to large data sets - AppSheet Help