How to Stop Syncing Large Tables (60k+ Rows) and Load Data On-Demand?

[lventura]

Hi All. I’ve built a CRM app that’s become very slow, because of syncing large tables. I’m looking for the best practice to stop the default sync and instead load related data only when a user requests it.

My App’s Structure:

My app tracks client communications and has three key tables in a Supabase (PostgreSQL) database:

• Clients: A small table with a few thousand rows.

• Emails: A large table with 62,000+ rows and growing.

• email_client_links: A join table with 150,000+ rows. This is necessary because a single email can be associated with multiple clients (one person may have mulitple businesses ie clients)

The Bottleneck:

Currently, in my Clients_Detail view, I rely on the automatic Ref_Rows virtual column ([Related email_client_links]) to list a client’s emails. This forces AppSheet to sync both the Emails and email_client_links tables entirely to the client device, which is causing major performance degradation on app load and navigation.

My Goal: On-Demand Data Loading

I want to completely change this pattern. The goal is:

1. The app should load quickly, without syncing the Emails or email_client_links tables.

2. The Clients_Detail view should load instantly.

3. I want to remove the automatic Ref_Rows list and replace it with a “View Emails” action button.

4. When a user clicks this button, the app should perform a live query to fetch and display only the emails associated with that specific client.

My Specific Questions:

1. How do I correctly use a Security Filter to prevent the Emails and email_client_links tables from syncing any data on the initial app load? I’ve seen suggestions to use a Security filter (edited) with a TRUE = FALSE condition, but I want to be sure this is the right approach for both tables.

2. What is the correct formula for the “View Emails” Action? I assume the action should be Go to another view within this app using LINKTOFILTEREDVIEW. I need the correct filter expression that tells the target view to display only the emails where the email’s ID is present in the email_client_links table for the current client. Will this then query my postgres/supabase for pertinent rows?

Any guidance, links, best practices, or formula examples for this “on-demand” pattern would be hugely appreciated.

Thank you

[Steve]

@lventura

This forces AppSheet to sync both the Emails and email_client_links tables entirely to the client device

Nope. The occurrence of the REF_ROWS() expression doesn’t “force” anything.

AppSheet has no built-in support for on-demand loading; it’s an off-line first platform, so all data is loaded in the client (subject to security filters and partitions). You’d have to implement it yourself.

@lventura

The app should load […] without syncing the Emails or email_client_links tables.

Security filters.

@lventura

The Clients_Detail view should load instantly.

Already possible, for some definition of “instantly” .

@lventura

I want to remove the automatic Ref_Rows list and replace it with a “View Emails” action button.

You can remove the REF_ROWS() list display by removing its column from your views. You can prevent the expression computation by replacing REF_ROWS(...) with LIST(), or by either removing the Ref relationship between clients and emails entirely, or demoting it to an Enum of Ref relationship (which doesn’t generate the Related … column).

You cannot replace the Add button attached to the Related … column inline list. There are ways to remove it, but not replace it with something else. There are several approaches to adding a View Emails button.

@lventura

When a user clicks this button, the app should perform a live query to fetch and display only the emails associated with that specific client.

This is the complex part. There’s no such thing as a “live query” in AppSheet, where the app queries a remote source and pulls in the results on demand. To approximate this, you’d have to implement a means for the user to specify the desired clients, capture them in a table, and force or wait for a sync. When the sync occurs, the server gets the desired-clients specifications and security filters would use them to feed the appropriate data back to the app.

So what you want is reasonably doable, just not in quite the terms you’re using.

@lventura

How do I correctly use a Security Filter to prevent the Emails and email_client_links tables from syncing any data on the initial app load?

Suppose the table that captures the user’s desired-clients specifications is called Desired Clients with the columns Useremail and Clients. Let’s create a slice named My Desired Clients with a row filter expression of (USEREMAIL() = [Useremail]). The security filter for email_client_links would then look like IN([ClientID], SPLIT(("" & My Desired Clients[Clients]), " , ")), and the security filter for Emails would look like IN([EmailID], email_client_links[EmailID]).

@lventura

1. What is the correct formula for the “View Emails” Action?

It’d be an action of type Grouped: execute a sequence of actions that sets the Clients column value in the My Desired Clients slice to the client, then navigates to the view that displays the emails. The navigation action should force a sync to prompt the server to generate the list of emails.

@lventura

I assume the action should be Go to another view within this app using LINKTOFILTEREDVIEW.

LINKTOFILTEREDVIEW() only works for data already in the app (client-side). For what you want, the filtering is done on the server by security filters, so there’s nothing to do client-side.

See also:

Scale using security filters - AppSheet Help

[lventura]

Ok. I’m probably asking the wrong questions.

I sort of want to do this horizontal scaling.

Advanced techniques: Horizontal scaling - AppSheet Help

So I made a user_control table security filter on

email_links
[client_id] = ANY(SELECT(user_control[current_client_id], [user_email] = USEREMAIL()))

and

emails
IN([id], SELECT(email_client_links[email_id], [client_id] = ANY(SELECT(user_control[current_client_id], [user_email] = USEREMAIL()))))

which achieves the first part, no load of 65k+ rows.

But now I need to put some kind of action in a detail view to trigger the database query.

I’d like to set user_control.current_client_id using actions, but I don’t seem to have that option.

My whole navigation “in” is via a table of 3k clients (not using the discreet downdown thing in the example by google).

If the user clicks on the client in the table, we’d go to client_detail and a button in client_detail that says “show me client related emails” pulls them out there, with a pause for sync - which is fine… But for reason I can’t seem to make an edit to the user_control table when I’m in client views? Am I going mad?

[lventura]

Oh, I’ve just seen this! Thank you. I will digest and see if I can grasp this.

[lventura]

Hi! Still me. I still can’t figure out how to get the grouped actions to work. I can manually set a user_control variable with the client_id, and get my subset of emails, with the security filter - but I can’t construct something to make it happen in app.

So I set up a “caller” action - the button for the user

and a group with a debug action, which works fine, and a “setter” action (sub action) Set Current Client ID

But I can’t figure out (if possible) how to pass the client_id to the “setter” action?

Went down a rabbit hole with INPUT(), but gave up because INPUT() seems only for interactive user input.

I’m thinking maybe this is possible using an automation? I mean - passing the variable. I appreciate I am trying to do something which appsheet is not natively intended to do. What do you recommend?

Thank you.

[lventura]

STOP THE PRESS. I think I’ve found a solution.

Added two rows to my clients table

ADD COLUMN last_selected_by VARCHAR(255),
ADD COLUMN last_selected_time TIMESTAMP;

Have one action at the top of my clients detail

And a couple of security filters. One on the unsorted emails

IN(
  [id],
  SELECT(
    email_client_links[email_id],
    [client_id] = MAXROW(
      "clients",
      "last_selected_time",
      [last_selected_by] = USEREMAIL()
    )
  )
)

and one on the client_id to email_id lookup
[client_id] = MAXROW("clients", "last_selected_time", [last_selected_by] = USEREMAIL())

Of course this isn’t terribly robust, if one staff member selects a client’s emails after another staff member, the two new columns in the clients table get overwritten. But it’s a start.