How do amend a basic sum function not add a sub-table field based on another field?
I currently have a basic sum formula that totals all of the 'Item Total Net' fields within a sub-table as shown on the attached screen shots, however i would like a formula that omits any of the figures that have been ticked as a provisonal cost in a seperate field.
i have tried the follwing code but it keeps on indicating that there is a symbol missing after 'where', any suggetions?
sum(select 'Section 1 Items'.'Item Total (net)' where 'Section 1 Items'.'Provisional Cost' = 0)
10 replies
-
Try:
sum(select 'Section 1 Items'.'Item Total (net)' where 'Provisional Cost' = "False")
Or (without the ")
sum(select 'Section 1 Items'.'Item Total (net)' where 'Provisional Cost' = False)
Steven.
-
Great, i'll try that.
-
Yeah it keeps on saying symbol expected at... which was what it was doing earlier? any ideas?
-
which is directly after 'where'?
-
Maybe this is Better:
---
let i := (select 'Section 1 Items' where 'Provisional Cost' = False);
sum(i.'Item Total (net)')
---
ps: can you try if this works also:
---
sum((select 'Section 1 Items' where 'Provisional Cost' = False).'Item Total (net)')
---
Steven
-
Nearly, but it adds the same amounts up for all the different quotes making all the sums for section one the accross all of them?
-
*Makes the amounts in section one the same accross all the quote numbers.
-
This might do it:
---
let me := this
let i := (select 'Section 1 Items' where 'Provisional Cost' = False and Id = me);
sum(i.'Item Total (net)')
---
Steven
-
Problem solved, i ended up not too sure by luck (more of a process of elimination) adapting the follwing:
sum(select 'Section 1 Items'['Provisional Cost' = 0].'Item Cost Total') this as per previous formula's ending up selecting all the records within the table, literally adding up all the section 1 costs across all invoices, i just removed the 'select' statement and now it seems to be working seemlessly.
sum('Section 1 Items'['Provisional Cost' = 0].'Item Cost Total')
Thanks for all your help Steven.
-
I'm a complete newbie ...... But I thought you could do all your database and management in Ninox withOUT coding? I was looking for a different (much more elementary) question, and stumblied upon this one. Intrigued because I plan to calculate MANY things later, I read through all your Q and A -- I'm sad to say Im not going to be able to "grasp" the concept of all that typing with " and ' and =0 stuff !! (I think it's called "code!) well....certainly not in time for the urgent organizational demands I need to meet by ....yesterday! I just purchased this app and annual subscription yesterday after playing around with it for a while! Yikes! I'm sure glad that at least there are videos and help sources like these interactions on here. Carry on ..... I'm going to go purchase the Dummies Book for Code For Ninox! LOL! Tracy
Content aside
- 3 yrs agoLast active
- 10Replies
- 3004Views