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:
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
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!
Solved! Go to Solution.
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.
Itโs a lot easier in an email report. A single โreport tableโ cannot easily contain data from multiple other tables.
Nope.
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.
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!
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 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
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
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.
Itโs a lot easier in an email report. A single โreport tableโ cannot easily contain data from multiple other tables.
Nope.
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.
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?
Thatโs entirely up to you, but future readers might appreciate knowing the issue was resolved.
Marked as Solved. Thank you @Steve
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:
Go to the Inline UX View for _SalesOrderDetailsinTimeFrame.
Itโs a system generated view. It should be named SalesOrderDetailsinTimeFrame_Inline.
Select Group by Product Description.
Add a Group Aggregate by SUM:: Qty
Hope this helps.
User | Count |
---|---|
43 | |
28 | |
24 | |
24 | |
13 |