Report from multiple tables

[Faustin_Ngassa]

Hi,

I am building a sales report.

I have a SalesOrders Table with a SalesOrderDetails child table that lists the products sold in the Parents SalesOrder

I have a GoodsReceivedNotes Table with a GoodsReceivedNoteDetails child table that lists the products delivered

I would like to make a report showing, for a user provided date range and customer name, the following results:

• the list of products from the SalesOrderDetails
• the quantity ordered for each product
• the quantity delivered for each product

I am not sure what the best way to go about is.
I have created a SalesReport table in Google Sheets and a form in my app with

• Customer Id
• StartDate
• EndDate

Then a VC called _SalesOrderDetailsInTimeFrame with the formula
SELECT(
SalesOrderDetails[SalesOrderDetail Id],
AND( [SalesOrder Id].[Customer Id] = [Customer Id],
[_SalesOrderDetail_Date] >= [StartDate],
[_SalesOrderDetail_Date] <= [EndDate] )
)

The VC returns a table that lists the products ordered in the correct time frame and customer.

I could similarly create a VC that returns the list of products delivered in the same time frame and customer.

My question is: How do I display in the same “table” , that list of products with the SUM of quantities ordered, SUM of quantities delivered, Grouped by Products?

Is there a better way to do what I am trying to accomplish in a totally different (maybe easier) fashion using maybe slices and views, etc?

Thank you!

[Steve]

Making some guesses about your data:

<<Start: FILTER("SalesOrders", AND(([Customer Id] = [_THISROW].[Customer Id]), ([SalesOrder_Date] >= [_THISROW].[StartDate]), ([SalesOrder_Date] <= [_THISROW].[EndDate])))>>
<<Start: [Related SalesOrderDetails]>>

<<[Product Id].[ProductName]>>
<<[SalesOrderDetail_ProductQty]>>

<<End>>
<<End>>

I’m not clear how the GoodsReceivedNotes and details tables are related to the SalesOrders and details tables, so I can’t offer a suggestion there. Details welcome!

[Faustin_Ngassa]

Hi @Steve thank you for the quick reply!

Here are some details about the goal of the app and the structure of the main tables

The SalesOrders table

The SalesOrderDetails table

The GoodsReceivedNotes table

And finally the GoodsReceivedNoteDetails table

In a real world application, a Customer’s request is recorded in a SalesOrder that contains a SalesOrderDetails with a list of the Products ordered with their respective quantities (column Volume). The products go out for delivery and at reception, the products are counted by the Customer and a GoodsReceivedNote and corresponding GoodsReceivedNoteDetails are created to record the Products and quantities received (VC _GoodsReceived_Equivalent_Volume) by the customer.

The GoodsreceivedNoteDetails is linked to the SalesOrder by a VC _SalesOrderId

What I would like is to show side by side in the app for a given timeframe and Customer Id,

• The SalesOrder Id
• the sum of the column Volume (Quantity ordered, from SalesOrderDetails) and
• the sum of _GoodsReceived_Equivalent_Volume (Quantity received, from GoodsReceivedNoteDetail)

The table should look something like this
SalesOrder Id Product Id Sum of Volume Som of _GoodsReceived_Equivalent_Volume.

What I have done so far, I created a physical table called SalesReport to give the user a form where the Customer, Start and End Date can be specified dynamically:

I created a VC in the SalesReport table that returns the list of SalesOrderDetails from the correct time frame but I have no idea how to move forward from here

How can I sum the Qty per Product? In this specific case, the result would be 3 lines instead of 5, with the Product Sawn Bomanga 3x10x5m having a Vol of 10 and Qty of 668

Questions:

1. Can the report table that includes data from 2 distinct tables like I am trying to create be built and visible in the app or does it have to be an email report?
2. In classic SQL this would be done using a SELECT … SUM… GROUP BY … JOIN is there an equivalent in AppSheet?
3. The fact that my SalesOrder table has a [Related SalesOrderDetails] and a [Related GoodsReceivedNoteDetails] should be useful, but how?

I come from a C# / SQL background so I feel like I am making this way more complicated than it can be. Please bear with me

[Steve · Accepted solution]

Faustin_Ngassa:

How can I sum the Qty per Product?

AppSheet is not well suited to present reports in-app like this; reports, though, are well suited. It is possible to do what you want, but it’s complicated, delicate, and heavy.

Faustin_Ngassa:

Can the report table that includes data from 2 distinct tables like I am trying to create be built and visible in the app or does it have to be an email report?

It’s a lot easier in an email report. A single “report table” cannot easily contain data from multiple other tables.

Faustin_Ngassa:

In classic SQL this would be done using a SELECT … SUM… GROUP BY … JOIN is there an equivalent in AppSheet?

Nope.

Faustin_Ngassa:

The fact that my SalesOrder table has a [Related SalesOrderDetails] and a [Related GoodsReceivedNoteDetails] should be useful, but how?

Not really, believe it or not.

The way I would think to approach this would be to have a separate Products Report table, one row per Product, with Qty and Volume virtual columns that compute their values from the “current” report table row. The “current” sales report might be setup as a dashboard view that includes a slice of the Products Report table that includes only the relevant rows.

[Faustin_Ngassa]

Hi @Steve!

Thank you for your detailed and very clear answers. As I need pretty detailed reports and a dashboard, I will use the Google Sheets API to connect to my data source and build that dashboard in PHP.

Cheers!

Faustin

PS: Should I mark your previous response as the Solution the my query?

[Steve]

Faustin_Ngassa:

Should I mark your previous response as the Solution the my query?

That’s entirely up to you, but future readers might appreciate knowing the issue was resolved.

[Faustin_Ngassa]

Marked as Solved. Thank you @Steve

[Eloy_Paredes_He]

Sorry I didn’t see this thread before.

Maybe one way to do the in app report closer to the way you want it is:

1. Go to the Inline UX View for _SalesOrderDetailsinTimeFrame.
   It’s a system generated view. It should be named SalesOrderDetailsinTimeFrame_Inline.

2. Select Group by Product Description.

3. Add a Group Aggregate by SUM:: Qty

Hope this helps.