Best practices for joining tables to display in AppSheet

[Roland_W]

I have an AppSheet app with multiple tables stored in a Google Sheet. These tables are logically related: for example, I have a Customer, Order, and OrderItems table. A customer can have many orders and an order can have many items.

I'm struggling with how to best join these tables to display a read-only table in AppSheet.  For now, I've been using a Google Apps Script to programmatically join the tables to create a new table in Google Sheets that I bring into AppSheet as a read-only source.   This seems to work, except I don't know how to keep the data current. I've set up a trigger in Google Apps Script to run the script every 10 minutes, but I do have odd and intermittent failures like "We're sorry, a server error occurred. Please wait a bit and try again."

TLDR: I'm trying to do an inner join on tables in Google Sheets to display in AppSheet and I wonder what the best approach would be. 

[Rifad]

I’m waiting to see if someone has managed to solve this. 

[MultiTech]

I'm confused 🤔 Are you are describing a standard one-to-many relationship?

• Customer has many orders
• Order has many order_detail

If you create the ref connection on the child, in AppSheet it automatically creates the display of the reverse reference records.

• So the customer can see all their orders
• On the order, we can see all it's details

Or are you describing a different scenario?

[Steve]

Virtual columns?

[MultiTech]

I asked Appster, just to be sure - it had the same conclusion.

---------------------------------------------------------------------------------------------

Yes, this person is essentially asking about implementing a one-to-many relationship in AppSheet. They have Customers, Orders, and OrderItems tables, with logical relationships:

• A Customer can have many Orders (One-to-Many)
• An Order can have many OrderItems (One-to-Many)

What They're Struggling With

They are currently manually joining these tables using a Google Apps Script, which creates a new merged table in Google Sheets. The issue is keeping that data updated reliably, as their script runs on a timed trigger but sometimes fails.

Better Approach in AppSheet

Rather than using Google Apps Script to join the data, they should leverage references (REFs) and virtual columns in AppSheet to dynamically join the related records without needing an extra merged table.

Steps to Properly Structure the Data in AppSheet

1. Ensure Reference Columns Exist

   • In the Orders table, add a column [Customer_ID] as a Ref to the Customers table.
   • In the OrderItems table, add a column [Order_ID] as a Ref to the Orders table.

2. Enable Related Lists

   • AppSheet automatically creates a Related Orders virtual column in Customers.
   • AppSheet also creates a Related OrderItems virtual column in Orders.

3. Create a Slice for Displaying Data

   • If they want a read-only table showing combined data, create a slice that brings in relevant fields from Customers, Orders, and OrderItems.

4. Use a Virtual Column for Display

   • Instead of creating a merged table, they can use a virtual column in Orders:

     CONCATENATE([Order_ID], " - ", [Customer_ID])
     

     This makes it easy to show combined data dynamically in a read-only format.

Why This is Better than Google Apps Script

• No need for periodic script execution – the data updates automatically.
• Less maintenance – No API calls or external script dependencies.
• Efficient performance – AppSheet handles relationships natively.
• Scalability – Works for large datasets without worrying about script failures.

-------------------------------------------------------------------------------------------------

Continue the convo here: https://chatgpt.com/share/67b4d610-d00c-8003-b41c-689c3f9556cd

___________________________________________________________________________________________
Next time, engage with Appster to get answers faster!
- You can chat with the LITE VERSION of Appster through ChatGPT.
- Gain access to the FULL VERSION by signing up at www.MultiTechVisions.com/answers (Main Menu > Support Tiers)

[Roland_W]

---

@MultiTech wrote:

Create a Slice for Displaying Data

• If they want a read-only table showing combined data, create a slice that brings in relevant fields from Customers, Orders, and OrderItems.

---

I don't see how to create a slice using columns from multiple tables. 

[MultiTech]

I think it was making a single point about many; the core essence there is that if you're wanting to display read-only data, while still having the data editable in the app, you would need to create slices for this.  (Something I do all the time)

Pro Tip:  The only way in which someone can get into the form (to edit the record) is through the Edit action.  So if you hide that from someone that's not supposed to be able to edit things, they can't.  This is a simple way to make your data read-only, without having to go through all the hassle of anything else.

[Roland_W]

I'm less concerned with the data being read-only. I'm more concerned with the best way to join data from multiple tables and display it in AppSheet. 

It looks like the best option now is to add virtual columns. I don't see a native way to join tables within AppSheet. 

[Steve]

---

@Roland_W wrote:

I don't see a native way to join tables within AppSheet.

---

There is none.

[MultiTech]

---

@Roland_W wrote:

I don't see a native way to join tables within AppSheet. 

---

Oh... you mean like... combine the customer and order and detail contents all into one.  Yeah can't do that, not sure why you'd want to. 🤔  What's the benefit? Or intended functionality?

[Roland_W]

In a nutshell, I'm trying to create tabular reports that display data from multiple tables simultaneously. For example, I want to join data from the Customers table with data from the Orders table to only show customers who have placed orders over the past 6 months and sort that by the the number of orders placed.

I could build a report using Looker Studio, but the functionality is rather limited, and I don't want to force users to leave the AppSheet application to view and export data. 

[Suvrutt_Gurjar]

---

@Roland_W wrote:

In a nutshell, I'm trying to create tabular reports that display data from multiple tables simultaneously.

---

Maybe you are looking for something more complex but if you are looking for template reports, you may want to take a look at the following sample app

This app has exactly the same entities that you have described , "Customers" , "Orders" and "Order Details". "Order Details" is equivalent to line items of an order.

Order Capture How-to - AppSheet

Within the app, please take a look at the automation and bots  "CustomerUpdateRule" ,  "OrderUpdateRule" and "OpenOrdersReportRule".

Hopefully, you will get ideas on reports that you wish to build.

[Roland_W]

Thank you, that's close, but I'm trying to display a single table that will have all customers and all orders in the same table. That means there will be a row for each order and customers will be repeated. 

The only native solution I could find to this was to add virtual columns to the Orders table and create a slice to show values based on specific conditions. 

[Rifad]

In Salesforce, there’s an option in the settings that says “Join Table A and B.” Unfortunately, there’s no way to do this in Appsheet. I’m waiting to see if someone has managed to solve this issue.

[Roland_W]

The best I could do was building a relationship between tables by designating reference columns to build relationships between tables.

[scott192]

On Table A create a VC.  In the formula use SELECT(TableB[KeyField],[ForeignKey] = [_THISROW].[PrimaryKey]).
It is assumed that Table B will have some sort of identifier that relates it to Table A (foreign key).

[Roland_W]

I didn't need to use that formula. I set up a relationship between the Customer and Order table by setting the a reference column linking Orders to Customers and I was able to add VCs to the Order table by using formulas like [Customer].[CustomerName]

[Steve · Accepted solution]

---

@Roland_W wrote:

The only native solution I could find to this was to add virtual columns to the Orders table and create a slice to show values based on specific conditions.

---

Yep.