App Formula Error Message

MauriceWhelan · 03-18-2019 04:07 PM

Hi. I am getting the following error when trying to use a Start expression in a Virtual Column

Error
‘could not be parsed due to exception: The given key was not present in the dictionary’

Expression
<<Start:SELECT(Create Works Order [Product Description],AND([Date]=[_THISROW].[Date],[Cylinder]=[_THISROW].[Cylinder 4],[AM/PM]=[_THISROW].[Cylinder 4 AM/PM]))>>

Could anyone help with the right structure for the Start Expression? Thanks.

LeventK

@MauriceWhelan
With Start expression, you need to specify a key column value. The error indicates that [Product Description] column is not the key column for Create Work Order table. Your Start expression then should follow a starting column. Your expression shall look like:

<<Start:SELECT(Create Works Order[KeyColumnName],AND([Date]=[_THISROW].[Date],[Cylinder]=[_THISROW].[Cylinder 4],[AM/PM]=[_THISROW].[Cylinder 4 AM/PM]))>><<[Product Description]>>

MauriceWhelan

Thank you @LeventK. As a follow on from this, if I put the [Virtual Column Name] into my Google Doc template, is that all I need to put in to show the row data captured with the above expression?

LeventK

@MauriceWhelan following correctly syntaxed Start expression, you can specify any column name you want, including a Virtual Column Name.

MauriceWhelan

Thanks again

LeventK

Your welcome @MauriceWhelan

MauriceWhelan

Hi @LeventK. Weirdly I am still getting the same error when I inserted the key column from that table in to the formula. Could you advise on anything else I may be missing? Many thanks.

LeventK

Hi @MauriceWhelan
It’s a bit hard to predict the reason without knowing your app structure actually. The error indicates that there is a problem with the key column value. Even your syntax can be wrong (trailing spaces, semantic errata etc.). Are you trying to initiate the start expression from another table? Provided you can fill me in so that I might have a better understanding of what you are trying to do and where the possible error is. Thnx.

MauriceWhelan

Thank you @LeventK. I have a table called Create Works Order and another table called Retention Analysis. As the user fills out the Retention Analysis form I am automatically pulling in some values from Create Works Order i.e. [Max Charge Number] and [Total Cubic Metres]. The [Total Cubic Metres] value relates to 1 or more rows in the Create Works Order Table and these are the values I need to pull into the Retention Analysis Form too so I can include them in the email I want to send for each new record. So I created a Virtual Column in the Retention Analysis table called [Description] and put the above formula in the AppFormula field. Hope that helps a bit.

Am I structuring it correctly?

Thanks again.

LeventK

@MauriceWhelan
Start/End expressions are for workflow email templates only, using those expressions in an AppFormula field won’t work actually. Provided you want to show related data from another table as per your choices in the form, you should be using either a REF_ROWS or a SELECT expression, which will create a list.

Another approach is using ref & de-ref. Provided you have a ref between your Create Work Order (Parent) and Retention Analysis (Child) tables, than you can simply fetch any field value from the Parent table with a de-ref expression i.e.:

[RefColumnNameInChildTable].[ColumnNameYouWantToFetchFromParentTable]

MauriceWhelan

This is great feedback @LeventK. Thank you very much.

LeventK

You’re very welcome @MauriceWhelan, it’s my pleasure to be helped of.

MauriceWhelan

Hi @LeventK. I used the VC with select expression to pull out the line items. Worked great, thank you again. Only small issue i have is the values came out in the mail template as
Item, Item, Item.
Is there anyway I can present them
Item
Item
Item
Either through the app or in the template itself?

I have 2 other columns to extract and ideally I would like to present the column headings in the template as follows (with the values presented vertically underneath)

Product Description ---- Quantity ---- Cubic Metres
Value 1 Value 1 Value 1
Value 2 Value 2 Value 2

LeventK

@MauriceWhelan
The best way for this could be creating a Google Doc template for the email body and use the Start/End expressions which will retrieve the table data as you specify. You can also refer to the below pages for further reference:

MauriceWhelan

Thank you @LeventK

LeventK

You’re very welcome @MauriceWhelan

Hasan_ashenk

Hi @LeventK sorry I took part in this question

I want to make a share button on behavior–>action , I want to retrieve text data just like the default share button from appsheet, can you help me create a formula / tutorial for making it

The attached video is an example of what I want to make, photo capture is the data that I want to take for me to share on WhatsApp, please help me and thanks!

Screenshot_2020-12-27-10-16-15-70_4f4b4dc22922b128424eca33c4390726|236x500

Hasan_ashenk

I’ve tried using this formula, but can’t retrieve all the text data that has been stored

LeventK

@Hasan.ashenk
I will take a look and get back to you.

Hasan_ashenk

Dear Sir @LeventK

Please help me about my question, thanks

Hasan_ashenk

I’ll wait, thanks