I have an expression that is working in the e...

[Daniel_Armstron]

I have an expression that is working in the expression builder but not in the app.

It is using data from two tables Product and PurchaseOrderDetails. I am try to make an action to change the value in a column to add the value in this expression:

Sum(select(PurchaseOrderDetails[Quantity], and([ProductID]=[_thisrow].[ProductID], or([Order Status] = “”, [Order Status] =“Submitted”, [Order Status] =“Ordered”))))

To a column [bought] but the action changes it to 0 , which is a different result from the expression builder.

If I put the expression in a virtual column then create the same action it works.

I was hoping someone could tell me why?

[Aleksi_Alkio]

Try to use it first without [Order Status]="" and see what happens.

[Daniel_Armstron]

The action is not performed, 14 stays at 14, and not showing changes to 0.

[Daniel_Armstron]

[bought] + 4

[Steven_Coile]

@Daniel_Armstrong All of the quotation marks in the expression you posted originally are “intelligent quotes”, in that the leading pair is rightside-up and the trailing pair are upside-down. It’s possible different parts of AppSheet handle intelligent quotes differently, and therefore interpret your expression in different ways, resulting in the inconsistent results you’re seeing.

Can you try replacing the intelligent quotes with regular double-quotes? Alternatively, remove the quotation marks entirely around Submitted and Ordered, and replace [Order Status] = “” with isblank([Order Status]).

[Daniel_Armstron]

+Steve Coile That is very interesting! I will check that out

Thanks for the tip

[Daniel_Armstron]

+Steve Coile I still couldn’t figure it out, but at least I learned about the quotes. I just put it in a virtual column its all good. I don’t want to mess with it any more. Thank you for your help.

[Aleksi_Alkio]

What should be the correct value in your test?

[Daniel_Armstron]

14 should become 28, because there are 14 in the sum.

The weird part is it works in the expression builder, and works when I put the sum in a vertical column, and use that action to add the two columns but not when it is a column + the sum.

[Steven_Coile]

@Daniel_Armstrong So is the value of [bought] changed from 14 to 0, or does it remain at 14 (as if 0 was added)? I’m confused because you say, “I am trying to […] to add the value […]” rather than “to set the value”.

[Daniel_Armstron]

+Steve Coile Oh sorry [bought] stays at 14 when I use the sum in the expression

[Steven_Coile]

@Daniel_Armstrong So I’m guessing you have an action with:

If this condition is true of true

Do this of Data: set the value of a column

Set this column of bought

To this value of [_this] + Sum(select(PurchaseOrderDetails[Quantity], and([ProductID]=[_thisrow].[ProductID], or([Order Status] = “”, [Order Status] =“Submitted”, [Order Status] =“Ordered”))))

Is this correct?

[Daniel_Armstron]

+Steve Coile

Yes, it sums [bought] and” the purchases that are coming soon(the expression). It doesn’t use an if condition, but it filters out certain types of orders.

I really just wanted to know what causes the expression to work in expression builder but not work in the app. Can actions not run expressions from multiple tables?

[Steven_Coile]

@Daniel_Armstrong I would expect the action to work as desired. Does the action work if you remove the existing formula and replace it with a fixed value, like 42, just to confirm it?

[Daniel_Armstron]

Yes