Merge information from multiple tables

[TalkNet_TechnoS]

Hello Appsheet Community

I have:

1. Table with Money Revenue from Sales of Products
2. Table with Income of money for Provision of Services
3. Table with Daily Expenses
4. Table with Goods Purchase Expenses
5. Table with Expenses for Salaries of Personnel and Commissions

I need to generate a report view, monthly, yearly and between eligible dates.

I can’t think of a way to do it, it must also be easy to understand with the naked eye and if possible accompanied by graphics.
I did some tests with the “Dashboard” view but the information is separated.
The idea is to generate a type report:

Revenue XXX 100 Expenses XXX 50
Total: $ 50

I didn’t find app examples.
Ideas or suggestions?

[Bellave_Jayaram]

You can create a PARENT table whose sole purpose is to refer to these 5 tables as CHILD tables and generate a report using START expressions.

[WillowMobileSys]

Did you see this sample app?

Sales Report App

As @Bellave_Jayaram mentions, you will most likely want to create Parent table that combines all the other tables together as children. Then you can add Summary columns that will enable you to run expressions to gather and compute values as needed.

The Start/End expressions @Bellave_Jayaram talked about are, I believe, used exclusively in document templates. They mainly allow you to cycle through a list of rows and apply the same format to each row within the document.

You asked for Options. Here’s a couple I can think of

Option 1)

You could generate your reports using doc templates, save them and surface in your app the links to those files for the users to open and view. You could also send these files by email on a schedule. Example below.

A row in my app in Deck view. Icon with doc+mag glass opens a list of files.

The list of files - in my case they are a history of Estimates/Invoices for that row.

Tapping on a file row opens the file in a browser window. Below is on laptop. In your case this could be report file generated and saved to a file.

---

Option 2)

In your post, you seem to imply you want a visual report with graphics (I assume you mean charts) in the app itself. AppSheet is not really geared for generating a visual report. Mobile devices have a small amount of screen real estate to show your typical reports.

HOWEVER, after creating a Parent table, you may find that you can more easily stitch together some views of data and charts that make sense.

For good user experience on mobile, remember to keep your reported info in small consumable chunks allowing the user to drill into the detail IF needed.

[TalkNet_TechnoS]

@Bellave_Jayaram
@WillowMobileSystems
Thank you both for the contributions!
I don’t quite understand creating a main table that combines all the other tables.
Would it be a virtual table (if that exists) or a spreadsheet?
What columns would that table have? I imagine that each column would be the Key Id of each of the tables to which I refer, right?
and then create virtual columns with the data I want to extract from each table?

[WillowMobileSys]

I would think of it as a Report table. Columns might be

• Report ID - Unique for each record
• Report Frequency - an enum list of Monthly, Quarterly, Yearly, Adhoc
• Report Start - the start date of the Frequency period
• Report End - the end date of the Frequency period
• Total Sales Revenue - sum of values from Table 1
• Total Services Revenue - sum of values from Table 2
• Total Daily Expenses - sum of values from Table 3
• Total Purchase Expenses - sum of values from Table 4
• Total Personnel Expenses - sum of values from Table 5
• Total Revenue - Total Sales Revenue + Total Services Revenue
• Total Expenses - Total Daily Expenses + Total Purchase Expenses + Total Personnel Expenses
• Net Income - Total Revenue - Total Expenses

After creating this table/sheet, I would load into AppSheet and then add a Report ID column to each of the other 5 tables. Make this column a Ref column AND edit the column to “turn on” the “Is Part Of” property. This creates Parent/Child relationships between the Report table and the other 5 tables which gives your report access to all the rows from the other table to include in the reports.

Now you can add a report request and generate a report file, if you want.

Or have a Dashboard view call upon a Report row to show the report details.

Anyway, one suggestion.