App Formula Error Message

behavior
(Maurice Whelan) #1

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.

(Levent Kulacoglu) #2

@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]>>

1 Like
(Maurice Whelan) #3

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?

(Levent Kulacoglu) #4

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

(Maurice Whelan) #5

Thanks again

(Levent Kulacoglu) #6

Your welcome @MauriceWhelan

(Maurice Whelan) #7

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.

(Levent Kulacoglu) #8

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.

(Maurice Whelan) #9

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.

(Levent Kulacoglu) #10

@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]

(Maurice Whelan) #11

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

(Levent Kulacoglu) #12

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

(Maurice Whelan) #13

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

(Levent Kulacoglu) #14

@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:

(Maurice Whelan) #15

Thank you @LeventK

(Levent Kulacoglu) #16

You’re very welcome @MauriceWhelan