How to Calculate the Total sales of the each product by each dealer

[PradeePDP]

I have a query about the Appsheet Expression, I do have 5 dealers and the Business name is ABC, DEF, GHI, IJK, and MNO and they have brought Product 1, P2, P3, P4, P5 multiple times, how do I calculate by each dealer brought how many pcs of Product 1 -5 for further reference plz check the SCREENSHOT

@Suvrutt_Gurjar @aurac 

[Suvrutt_Gurjar · Accepted solution]

Before responding to your query, you may want to confirm and think if you will add a product P6, P7 and so on in future. In that case, you may want to think of a different data schema. Adding new columns for new products many not be a very appropriate way.

[Suvrutt_Gurjar · Accepted solution]

Before responding to your query, you may want to confirm and think if you will add a product P6, P7 and so on in future. In that case, you may want to think of a different data schema. Adding new columns for new products many not be a very appropriate way.

[PradeePDP]

Yes, Mr. Gurjar, I may add probably but not necessary now!!! maybe I add the products after a year.. If you have a solution I will be greatful to know that too

[PradeePDP]

actually, I have tried to change the schema but since I didn't have any prior experience in coding or Expression I field to change the format still i am trying a way for that too

[Suvrutt_Gurjar · Accepted solution]

---

@PradeePDP wrote:

I have tried to change the schema but since I didn't have any prior experience in coding or Expression I field to change the format still i am trying a way for that too

---

You may want to take a look at the following article to start with

Data: The Essentials - AppSheet Help

References between tables - AppSheet Help

The below article has some sample apps that you can refer to :

Get started by using the feature samples - AppSheet Help

The sample app below matches your requirement. You can consider the Dealers in your case as Customers in the sample app. In the sample app, the Customers place orders and each order has products from the Products table..

Order Capture How-to - AppSheet

Note: While the articles referred are certainly useful, regarding the sample app referred, please note that you as app creator, please take final call in the app design you will have based on your needs. Community is best used for some specific queries related to expression , an app feature etc.  In case of app design, the community may suggest based on the overview information provided. But since the community has no way of knowing your long term goals with the app, you are the final decision maker in your app design.

Regarding your current design , you may need to use SELECT() statement for each of the product columns in a VC for dynamic calculation of added quantities. However VCs are sync time expensive for multirow expressions. You may want to instead use Group by feature etc. 

SELECT() - AppSheet Help

[PradeePDP]

Thank you so much, let me gothrough this basics first 

[Suvrutt_Gurjar]

You are welcome.

[PradeePDP]

My Dealer data sheet is not getting updated according to the sales sheet when I enter the product sales qty,

Sum(Select(sales Sheet[Product 1],[_thisrow]=[Business Name])) - Finally this Expression is working but I need to update my Business partner for each transaction which will be impossible to do one by one manually, Google Suggest me to apply an action all the row!!! but i have no idea about setting an action, especially for this case kindly help me 

[Suvrutt_Gurjar]

My request will be to open a different post for this latest query. Also please share relevant table and column details while describing your issue, or else the community will not be able to respond.

Edit: from your little description, it sounds that you may need to use reference actions or spreadsheet formulas as in the sample app for immediate updates of relatedtables.