Sum of item from another table that matches this r...

Dave_Willett · 08-30-2019 09:52 AM

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

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])

Sum of item from another table that matches this row