Sum of item from another table that matches this row

[Dave_Willett]

Using a virtual column in the table “Console” I want to sum the value of [Total_11] from the table “AppQuotes.csv” which matches this [JobID], I thought the following formula would do the trick but it returns an error, Invalid App Formula. Can anyone help with the syntax?

SUM(AppQuotes.csv[Total_11],[JobID]=[_THISROW].[JobID])

Kind Regards

[Suvrutt_Gurjar]

Hi @Dave_Willett,

You may wish to try an expression something like below

SUM(SELECT(AppQuotes.csv[Total_11] , [JobID]=[_THISROW].[JobID]))

Basically, you will need a SELECT statement to compose a list all values from the column
[Total_11] matching the criteria denoted by [JobID]=[_THISROW].[JobID] . The SUM statement then will add all those values in the list.

[vsingh]

It's not working in physical column & virtual column data not recorded in database. So please help on this.

[MultiTech]

Just FYI, it’s advisable to remove any special characters from your table & column names.

[Dave_Willett]

Suvrutt_Gurjar:

SUM(SELECT(AppQuotes.csv[Total_11] , [JobID]=[_THISROW].[JobID]))

You mean the ‘.csv’ ?

[Dave_Willett]

That seems to work Suvrutt, thank you

[MultiTech]

Not necessarily the whole “.csv” but the dot. Same for any of the other special characters (., ], [, , /, etc.).

[Dave_Willett]

Point taken, I am rigid with naming convention in VB6 and Access VBE, just wasn’t sure with using Appsheet, I’ll get there.

[MultiTech]

AppSheet has compensated in most places, especially in the editor, to handle special characters; it’s when you get to workflow templates and other integrations that the magic of appsheet fades away.

[Aleksi]

@Dave_Willett Just curious… are you using references betweeen these two tables?

[Dave_Willett]

Hi Aleksi. If I think what you mean I’m using the formula to pull data from another table and showing the result in a view, the Show_If becomes very useful to hide zero data and a pretty nice front end.

[Aleksi]

Are you using Ref field for that purpose?

[Dave_Willett]

The formula is in a Virtual Column in table “Console”:

Then pulled through a slice and eventually used on the form “Home”:

Resulting in a total on the form:

Is this what you meant?

[Aleksi]

For me it sounds you could make the calculation like SUM([Customer Quote][Total_11]). The reason is this… because you already has a virtual list “Customer Quote”, you don’t need to read the whole AppQuotes.csv table again. You can just read the sum from that virtual list.

[Dave_Willett]

Interesting… I’ll do some testing later. Thank you for the advice Aleksi, always appreciate wisdom from you guys.

Kind Regards

[merchandiser_Co]

HI
I’m writeed that code. but dond worked. I think because the data saved in main table before sub table. please I want help
SUM(SELECT(purchases_Details[total_amount] , [invoice_number]=[_THISROW].[invoice_number]))
thanks

[Steve]

Try instead:

SUM([Related Purchases][total_amount])