How to show data from 3 different Table

[andreagiordano]

I have data in 3 different tables ...

I Used the formula sum if (=SOMMA.SE(Crediti!C:C;A:A;Crediti!D:D) This summ the credit for each user  =SOMMA.SE(ViaggiSplit!C:C;A:A;ViaggiSplit!E:E)  this sum the cost for the trips done by each user =SOMMA.SE('Calcolo Costo'!L:L;A:A;'Calcolo Costo'!M:M) this sum the refunding for drivers and then there is a column that sum all up and gives a balance) 

for each of the data that I want and by doing so in another table I'm able to show the data grouped in a dedicated view but this shows only the plain table and doesen't give access to the detailed data

On the other hand  with this solution I don't have the possibility to use "group by" and have the chance to see the data in more detail ...

Is there a way that I can show data from 3 different tables in one view? 

[WillowMobileSys]

---

@andreagiordano wrote:

Is there a way that I can show data from 3 different tables in one view? 

---

There are three options:

1)  If your data across all three tables are related by the same "Utenti" entity, then consider combining the table together in a single table since you want to view the data together.

2)  Another concept is to have a Parent table with several child tables.  The Parent table has all of the common details and each Child would have a Ref column pointing back to the Parent.  This allows a Detail View where you would see the Child data in separate Inline table views.  Additionally, you can use a Parent Deck view to group the rows and even pull in child data for that grouping criteria.

3)  Build a dedicated table that pulls only the desired details from the 3 tables into a single row - like 1) but a separate table with only what is needed.  This is typically used for Summary tables that provide weekly, monthly, yearly, etc reporting.  The idea is to have actions on the other tables as that data is added or changed, a similar change/update is made to the "Summary" table.

I hope this helps!

[andreagiordano]

Your explanation is very useful. I'm trying to figure out if I can implement one of your solutions ... it doesen't really looks like to me.

In the main table I have all the information of the trips (data, strart km,end km, total km, total time ,the driver and a refund for the driver 10% of total cost of trip, tank status and refueling,  calculation of total cost that then gets divided for each passenger in a child table and in another table I have the list of users that top-up their credit ....  ) I don't see a way to pull this data in one table other that what what I did, using (sum if). Anyway I will think i t over!

[WillowMobileSys · Accepted solution]

This is an app design exercise.  You need to always START the design process with your desired end result - i.e. How do you want to visualize the data in the app?  This needs to take into consideration the data.

I don't know your data or app at all, so I can't give any concrete direction.  If you want to share more about your app and its data, then I and others may have better suggestions to help you.

[andreagiordano]

Hi WMS !!! I come again on this topic ... I would like to re-design my app so that it has a more clear workflow. Where do I start from?

I will try to summarize it.
I manage a car that students share. I want the app to get input from students (start time, end time, km of the odometer at the beginning and at end, driver, passengers.

I let the app calculate the total cost, calculate a refund to the driver.

Users pay me some amount in advance and then I deduct cost of each trip from user balance.
I have many tables ... The main table where i record km, time, total cost, refund for driver ... in a parent referenced table I have the split of the trip cost for each passenger (I didn't manage to do it in the main table). Then there is a table for the drivers (this is just a list where i pick up names of authorized drivers, i could have done it without a table writing the names in the enum of the field Drivers, but I thought it was easy to reach the google sheet to write another driver down rather than entering the appsheet editor).
There is a table for some statistic, a table where I have the journal entry of the credit given by users and a table that summarize credit, debit, refund for drivers and the final balance (I have google sheet formulas doing that). I run the app in prototype mode because I couldn't afford any plan ☹️ so I unfortunately cannot use, autentication, bot or automation (I wanted notification to be sent users for a certain balance level for example ...). This might show you the weakness of my App. Anyway I enjoyed so much to work on it 😍... and I'm proud of how it work! 😎

[WillowMobileSys]

---

@andreagiordano wrote:

I would like to re-design my app so that it has a more clear workflow. Where do I start from?

---

First, you can mark an app as Personal Use, deploy it and you should be allowed to use the app normally, including all features under the Core plan - I believe including notifications to customers.  You can read more about that in this post.

About the app...

I understand that this is a small app (in usage terms) used only by you for which you record a few of your customers.  But what if this small business venture expands?  You want to be ready for that in some way and with just a little extra effort now you can be set for that future growth.  I assume you would like to see this business grow, so...I recommend thinking of building this app as if it is for an already well established business model.

Where to start in app re-design?

Understand what real-world "objects" or "entities" your app interacts with and determine what details you want to collect under each.  Each of these collections translates into your "core" data tables.  For example from reading your post, I see you have these potential tables:

Cars - assume you will eventually have more than one car.  For these cars you'll likely want track the Make, Model, Color, License plate, Active, etc.  

Drivers - obvious - Name, Location (driver address), Driver License #, Reimbursement Balance (amount to be paid to driver)

Customers - the individuals paying for services.  Name, Location (customer address), Payment Method, Balance, etc

Locations - use this table for ALL addresses.  It will ensure the address is entered only ONCE and is represented exactly the same each time used.  You can then easily track the most frequented Locations   - Location Name, Address 

Trips - details about each paid trip taken - Customer, Car, Start Location, End Location, Distance, Number of Passengers, etc.

Trip Payments - a transaction for each paid trip -  Customer, Trip Cost, Customer Starting Balance, Driver Payment, etc

Driver Payments - a record of the transaction to Reimbursement Balance

Replenishment - transactions that add to Customer balance - Customer, Replenishment amount, Payment Method Used, etc

There will be other columns of data you wish to track.  There may be other tables you need as your core data.  There definitely will need to be additional tables used as utility or supporting tables.  Don't hesitate adding more tables if they help facilitate an easy to use AND easy to implement app.

[andreagiordano]

yea .. I understand that is an app design exercise. I will think about it and see I I can figure out another way to organize process and data. Actually this is my first app and I started it by chance (somebody suggested me) and with zero expectation. The result was far better and far easier than I expected. So I got more eand more involved and eager to create something usable and complete. 

Thanks a million. 

[henrylester]

hy ,dear

Displaying data from three different tables in a meaningful way typically involves using a database query that joins these tables based on some shared or related data. The method you use will depend on the database management system you're working with (e.g., MySQL, PostgreSQL, SQL Server, etc.) and the relationship between these tables (e.g., primary keys, foreign keys).

Here's an example using SQL to retrieve data from three tables using JOIN statements:

Let's assume you have three tables: `table1`, `table2`, and `table3`.

Example tables:

```sql
table1:
| id | name |
|----|---------|
| 1 | Record1 |
| 2 | Record2 |

table2:
| id | table1_id | description |
|----|-----------|-------------|
| 1 | 1 | Desc1 |
| 2 | 2 | Desc2 |

table3:
| id | table2_id | value |
|----|-----------|-------|
| 1 | 1 | Val1 |
| 2 | 2 | Val2 |
```

To retrieve data from these tables where they have relationships, you can use JOIN clauses in your SQL query:

```sql
SELECT t1.name, t2.description, t3.value
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
JOIN table3 t3 ON t2.id = t3.table2_id;
```

Explanation of the query:

- `SELECT`: Specifies the columns you want to retrieve.
- `table1 t1`, `table2 t2`, `table3 t3`: Alias the tables for brevity (`t1`, `t2`, `t3` are aliases).
- `JOIN`: Connects the tables based on their relationships.
- `ON`: Specifies the condition for the join, usually based on common columns (e.g., foreign keys).

This query fetches data from `table1`, `table2`, and `table3` where they're related through their primary and foreign keys.

Adapt the query according to your actual table names, column names, and relationships established in your database schema.

Remember to replace `name`, `description`, `value`, and other column names with the actual column names in your tables.

Understanding your database schema and relationships is crucial when crafting SQL queries involving multiple tables.

[WillowMobileSys]

As indicated in his original post, this poster is using Google sheets as his data source and not an SQL database. 

In AppSheet, everything operates within defined tables.  We don't have the ability to hold in-memory datasets to operate on - such as what might be done in a service that submits a SELECT SQL query to join table data and then process that dataset to obtain an end result p - which is stored somewhere else or sent to the client/app side where it can be viewed by the end user.

To do what you are suggesting with SQL for an AppSheet app, the database would need to use a view or run a stored procedure and populate a table - both of which an AppSheet app can pull in as an app-side table to operate on.

AppSheet, and most no-code platforms, is a bit of a different animal to the usual 2-tier/3-tier approach and take a little bit to get used to. 

I hope this helps!