Call a Variable value for email Body

Dr_Hector_River · 11-14-2019 02:14 PM

I am trying to customize the stock management demo app. I have three tables: Products, Sales, Purchases.

What I am trying to do is trigger an email with the following template:

On <<[TimeStamp]>> there was a material withdrawal of product <<[Product Barcode]>> for the amoun ot <<[Quantity]>> pieces, leaving a current stock of <<[Current Stock]>> pieces.

The problem is that all variables, for the exception of <<[Current Stock]>> are read and parsed correctly in the email.

I believe the problem is that the variable <<[Current Stock]>> is a virtual variable on the Products Table and it is no present on the Sales Table, from which the email is triggered.

I’ve looked everywhere to find a way to call from the table Products the variable <<[Current Stock]>> so I can insert it in the email.

Can someone help me with how should I fill the template so the <<[Current Stock]>> is shown after the materials withdrawal?

LeventK

@Dr_Hector_Rivera
What kinda AppFormula do you have in your [Current Stock] virtual column?

Dr_Hector_River

Thanks for the reply!

SUM([Related Purchases][Quantity]) - SUM([Related Sales][Quantity]) + [Initial Stock]

LeventK

@Dr_Hector_Rivera
It’s possible that your workflow is triggered before AppSheet calculates the value of the AppFormula in your [Current Stock] virtual column. I don’t know what kinda conditional rule you are using to trigger the workflow, but you may try with adding below expression in your conditional rule:

[_THISROW_BEFORE].[Current Stock] <> [_THISROW_AFTER].[Current Stock]

OR

NOT([_THISROW_BEFORE].[Current Stock] = [_THISROW_AFTER].[Current Stock])

Dr_Hector_River

The email just pastes both formulas as text:

El 11/14/2019 4:54:54 PM se registró salida del material Hil-014 por la cantidad de 3 quedando [_THISROW_BEFORE].[Current Stock] <> [_THISROW_AFTER].[Current Stock]

LeventK

@Dr_Hector_Rivera
The expression that I had provided has to be places in your conditional rule that triggers your workflow:

I don’t know your app structure or how your tables might be related but you can retrieve a certain column’s value with an ANY(SELECT(…)) or LOOKUP(…) expression:

ANY(SELECT(Products[Product Description], [Product ID]=[_THISROW].[Product ID]))

OR

LOOKUP([_THISROW].[Product ID],"Products","Product ID","Product Description")

Dr_Hector_River

The condition is

So it automatically sends out the email after the stock is taken out and the new value is generated
But I am still confused as to how format the Email body to call that virtual column.
Sorry if I look too naive, but my specialization is eocnometrics, and I am just wondering into programming

LeventK

@Dr_Hector_Rivera
I notice that we are not in the same page regarding the terminology. What you are showing is the Update Event property which is set to ALL_CHANGES. No problem with that, because it denotes that your workflow will be triggered upon every action taken: adds, updates & deletes. What I was mentioning was the Conditional Rule of your workflow, which is an expression that will be checked if the rule expression evaluates to TRUE before it’s triggered. Check this out:

As your [Current Stock] column is a virtual column, and possibly your workflow’s conditional rule is set to TRUE rather than an evaluating expression, the workflow is triggered before the appformula in your virtual column is calculated.

The expression that I have given, shall be a conditional rule expression (or a part of it) so that AppSheet need to evaluate the value of this column and therefore calculate it.

Dr_Hector_River

Also while we’re on the subject, and you have been very nice to answer me. How can I also call another variable (not virtual) for example <<[Product Description]>> from the Table products on the withdrawal email

Dr_Hector_River

I think I got it!!! I am using the following template (The app will be deployed in spanish) but you get the idea

El <<[TimeStamp]>> se registró salida del material <<[Product Barcode]>> <<ANY(SELECT(Products[Product], [Product Barcode]=[_THISROW].[Product Barcode]))>> por la cantidad de <<[Quantity]>> piezas quedando en stock <<ANY(SELECT(Products[Current Stock], [Product Barcode]=[_THISROW_AFTER].[Product Barcode]))>>

The problem is that the formula is reading the Current Stock and printing a fixed value of 80 and not the real current stock after the calculation!

Thank you Thank you!!

LeventK

As you are triggering the workflow from a row event, you can directly refer to the column name, you don’t need to explicitly use an ANY(SELECT(…)) or LOOKUP(…) expression in this case:

El <<[TimeStamp]>> se registró salida del material <<[Product Description]>> <<[Product Barcode]>> por la cantidad de <<[Quantity]>> piezas quedando en stock <<[Current Stock]>>

Phyo_Arkar_Moe

I have one, I want to use Vlookup formular differend sheets only one file with Android Phone. Please help me how to move another sheet jionning formlar.

Dr_Hector_River

Once again thank you for answering me, but

I get this error:
Workflow rule ‘On Change in Sales’ action ‘Action 1’ Body template. Expression ‘[Current Stock]’ is invalid due to: Unable to find column ‘Current Stock’.

The problem is that Current Stock is the virtual variable in the table Products, differente from the Tables Sales (where I am getting all the other variables)

If I use the <<ANY(SELECT(Products[Current Stock], [Product Barcode]=[_THISROW_AFTER].[Product Barcode]))>> I get a return value different from the calculation that appears. It is even a fixed value regardless of the amount I take out of the inventory.

LeventK

@Dr_Hector_Rivera
Do apologize, it’s my bad…
So your [Current Stock] virtual column is in Products table, where you’re triggering the workflow from Sales table. Am I correct?

Provided my assumption is correct, do you have a REF relation between your Products and Sales tables?

Dr_Hector_River

Yes, the Prodcut Barcode is a Ref as seen in this image