Expressions wrapped in parenthesis

DaveWillett · 04-04-2022 10:37 AM

Having trouble producing a result, I know it's where the parenthesis are placed in the expression.

"<<(SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID])))*
(SUM(SELECT(MobileEstimateCustomers[Labour Rate],[JobID]=[_THISROW].[JobID])))+

SUM(SELECT(MobileEstimateCustomers[Other Total],[JobID]=[_THISROW].[JobID]))+
SUM(SELECT(MobileEstimateCustomers[SubTotal],[JobID]=[_THISROW].[JobID]))>>"

The expression with the following image should produce 1625 in the Sub Total not 2500.00. The [Labour Rate] is 50 and the first part of the expression returns correct as 27 * 50 = 1350

Parts, Materials and Other Total = 275, again this is correct. So 1350+275 should return 1625 but as you can see it returns 2500, I'm putting it down to parenthisis placement in the 2nd part of the expression.

(forget VAT and Total to Pay, I haven't got that far yet)

Can anyone assist?

Cheers

Marc_Dillon

Paste your expression into the expression assistant with the QREW tool extension and it'll show where each parentheses set pairs up.

Also make sure you test each portion of your expression individually when you face problems like this.

Beyond that generic advice, I really can't make heads or tails of your post.

graham_howe

It appears that you are pulling a column called SubTotal so my first question would be what is the calculation for that column?

MobileEstimateCustomers[SubTotal]

The second point which is odd is that you are adding the subtotal in your expression, not calculating it. If I add a bit of indenting to your parenthesis, you might be able to see more clearly:

(
  SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID]))
) 
*
(
  SUM(SELECT(MobileEstimateCustomers[Labour Rate],[JobID]=[_THISROW].[JobID]))
) 
+
SUM(SELECT(MobileEstimateCustomers[Other Total],[JobID]=[_THISROW].[JobID]))
+
SUM(SELECT(MobileEstimateCustomers[SubTotal],[JobID]=[_THISROW].[JobID]))

This is a bit like saying (RR+REP+PAI) * Rate + Other + SubTotal. Is that what you really meant?

DaveWillett

Yes I see my Error...

It should be:

"<<(SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID])))*
(SUM(SELECT(MobileEstimateCustomers[Labour Rate],[JobID]=[_THISROW].[JobID])))+
SUM(SELECT(MobileEstimateCustomers[Other Total],[JobID]=[_THISROW].[JobID]))>>"

Thanks for opening my eys, it was a long day yesterday...

DaveWillett

Finally, how do I format the expression to be Currency and add the £ at the start?

"<<(SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID])))*
(SUM(SELECT(MobileEstimateCustomers[Labour Rate],[JobID]=[_THISROW].[JobID]))*20/100)+
(SUM(SELECT(MobileEstimateCustomers[Other Total],[JobID]=[_THISROW].[JobID]))*20/100)+
(SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID])))*
(SUM(SELECT(MobileEstimateCustomers[Labour Rate],[JobID]=[_THISROW].[JobID])))+
SUM(SELECT(MobileEstimateCustomers[Other Total],[JobID]=[_THISROW].[JobID]))>>"

UPDATE

"£<<(SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+..................................

graham_howe

Is the expression going into a virtual column? If so then you could set the type of that column to be "Price" and then select the CurrencySymbol. If [Labour Rate] and [Other Total] are already type "Price" then the default of the expression will be a price as well.

Josh_Klassen

I'm not sure if this would be helpful or not but I always try to create columns to give results (and not show them to the user)

to do math equations.

For example columns would be [number of hours], [labor rate], [labor total,[Parts Total],[GST Percent],[GST in dollars]

then do simple equations like [number of hours]*[labor rate] as a formula for [labor total] then continue on like this. So then that way I have a column that shows me every step of the math equation and then I can find the error. In your specific example the formula should be as simple as [labor total]+[parts total] giving you a value in [subtotal].

Hope this helps.

DaveWillett

Thanks for the tips guys, much appreciate it.

I do have VC's for some calculations but this one has to be in the doc template. There is a part of the App which determines what type of job the user is requesting, this fires certain things to happen. All good if the job type stays as it is. However if the job type changes then the rows are already written before the change so something else has to happen. I looked at bulk update, it seemed far to intrusive for my purpose. Hence these expressions in the template, they sweep and mop up any mistakes and produce the correct results regardless. I could have taken a whole new approach but this seemed the most logical.

So again thanks again for the input guys.