Hi all - Help! Having an issue where my temp...

expressions
(Cyril Khamsi) #1

Hi all - Help!

Having an issue where my template for invoicing is suddenly no longer working.

I’m getting the following error:

Errors": "Error: Workflow rule ‘Generate Invoice’ field ‘Attachment’ expression ‘select(bookings[booking key],[booking key]=[_thisrow].[load ref],true)’ is invalid due to: Error in expression ‘[Invoice ID].[load ref]’ : Unable to find column ‘load ref’.

Error: Workflow rule ‘Generate Invoice’ Start expression ‘select(bookings[booking key],[booking key]=[_thisrow].[load ref],true)’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table.

I’ve checked multiple times that the names are still spelled correctly and keys are set appropriately, so I’m not sure what’s driving the problem. And I’ve made no related changes to the template as far as I can find. Any updates that may be affecting functionality here?

The template snap is pasted below. For a given invoice, I’m asking it to pull all the related bills, and for each bill pull the related job details. The structure in a table embedded within a table. I haven’t changed anything here for several weeks.

1 Like
(Philip Garrett) #2

@Cyril_Khamsi

Can you provide the following so I can take a look? 1. Your account id from the Account pane 2. The app name

Sorry for the problem.

(Cyril Khamsi) #3

App ID 482252072

(Philip Garrett) #4

@Cyril_Khamsi

There was a change. Please give this a try.

Replace [_thisrow] with [_thisrow-1]

Then let me know if it fixes the problem.

[_thisrow-1] means the table one level up from this one. [_thisrow-2] means the table two levels up from this one. And so forth.

(Cyril Khamsi) #5

@Philip_Garrett_Appsh I think I get the general idea of it… In my case, I should consider references to: Invoice [_THISROW] Bill [_THISROW-1] Booking [_THISROW-2]

Does this apply for all select statements in the template or just the ones that initiate a new table <<Start: xxxx>>?

(Philip Garrett) #6

@Cyril_Khamsi

It is supposed to work everywhere.

It is new, so let me know if you have problems.

(Philip Garrett) #7

@Cyril_Khamsi

It looks like you may be miles ahead of me. Have you fixed the template problems already?

As an aside, I am really impressed with your app. You do great work!

(Cyril Khamsi) #8

@Philip_Garrett_Appsh Thank you!! That’s encouraging to hear. And yup it’s working. I applied your rules and I think doing all-caps on THISROW made a difference too.

(Philip Garrett) #9

@Cyril_Khamsi

Really glad to hear that it worked for you.

Sorry for the pain I caused you.

I hope the new [_THISROW-n] syntax makes your life easier.

(Matheus Rodrigues de Souza) #10

I faced the same problem. This post solved it. This is such a big change, shouldn’t be more divulged?

(Elan Bailey) #11

@Philip_Garrett_Appsh This worked for me as well. I have to say I did spend a couple of hours trying to figure out the error before finding your post.

I’m not sure when you made this change but my apps had been working fine and only started throwing an error when I went in and made some changes to the app today. So I didn’t associate the error with a change that you might have made but rather thought it was an issue that I had created with my update.

+Praveen Seshadri [Appsheet] Is there a way that we could have someone on your team publish upcoming release notes ahead of these changes being made live on production? I don’t read a tonne of emails these days but I always read your Feature Fridays emails and would be equally happy to read through a pre-release email on a weekly basis to get ahead of any necessary changes to my app.

(Levent KULAÇOĞLU) #12

I really haven’t understood this change @Philip_Garrett_Appsh Why should I be referring to up row before? What’s the meaning? Should we now return back to all our customer app for whom we have used workflows like this and change all the expressions?? This must be a joke really.

(Cyril Khamsi) #13

@Philip_Garrett_Appsh

Looks like that’s not recognized - here’s the new error:

“Errors”: "Error: Workflow rule ‘Generate Invoice’ field ‘Attachment’ expression ‘select(bookings[booking key],[booking key]=[_thisrow-1].[load ref],true)’ is invalid due to: Error in expression ‘[_thisrow-1].[load ref]’ : Unable to find column '_thisrow-1

(Cyril Khamsi) #14

In the above example, I’m in an invoice which has many associated bills. From each bill, I do another select statement to pull up booking details - however, each bill is associated with one booking. In other words, the select statement is pulling in only one booking per bill - not actually a list. Could this be part of the problem?

The reason I do this is because I need to pull data from further references to the booking. In the above, I’m trying to pull in the label of the origin, which rests in a locations table. I don’t see another way to get there without adding more and more and more virtual columns to bookings (it has a lot of virtual columns already).

(Philip Garrett) #15

@Cyril_Khamsi

Thanks for the additional info. Not sure if you saw my post above. They may have crossed. Can you provide your account id and app name so I can take a look?

(Philip Garrett) #16

@Levent_KULACOGLU @Cyril_Khamsi

This is in response to Levent’s post. It attempts to explain the problem and what I am doing about it. It is a little complicated so bear with me.

Workflows and Reports can include Attachment and Body templates that display multiple levels of embedded tables. When we are processing such templates we need to construct a “binding context” for each level of embedded table. The “binding context” is what we use to resolve references to the fields of the table within the template.

Consider a three table hierarchy with the table names GrandParent, Parent, and Child. Then consider a workflow template that displays these three tables with GrandParent at the outer level, and Parent embedded under GrandParent, and Child embedded under Parent.

When we processing the Child table embedded template we need to have a three level “binding context” with Child pointing to Parent pointing to GrandParent like this:

GrandParent “binding context”

^ Parent “binding context”

^ Child “binding context”

Recently a customer reported that a field in a workflow template was not being handled correctly. I investigate the problem and discovered that I was not “linking up” the “binding contexts” correctly. The Child “binding context” did not point to the Parent “binding context” and the Parent “binding context” did not point to the GrandParent “Binding context”.

When I fixed that problem, it uncovered a problem in how [_THISROW] is handled within workflow and report templates.

Within expressions [_THISROW] refers to the table that is at the root of the “binding context” hierarchy.

Since the workflow rules were not constructing the “binding context” hierarchy correctly, [_THISROW] was going up one level in the hierarchy rather than to the root of the hierarchy. When I fixed the binding hierarchy problem, that affected how [_THISROW] worked.

This introduced a real dilemma about how to best fix this problem. After a long discussion, it seemed like the best solution was to make use of the new [_THISROW] feature.

We had recently enhanced [_THISROW] to make it much more powerful, but we had not yet announced this new feature.

Here is the enhanced

[_THISROW] syntax:

[_THISROW]

=> Goes up to the top level. This was the only option we offered before this new enhancement. [_THISROW-1] => Goes up one level. [_THISROW-2] => Goes up two levels. [_THISROW-3] => Goes up three levels. [_THISROW-n] => Goes up n levels.

Note that you must write exactly “[_THISROW-n]”. There cannot be any embedded white spaces in the string. The number must be a constant. In other words, the value “n” is a constant number value. It can never be an expression.

This feature is really powerful because you can use [_THISROW-n] in embedded workflow templates to retrieve field values from parent, grandparent, great-grandparent, etc. records. It eliminates the need to add virtual columns that replicate field values in child records when you have a multilevel embedded workflow templates.

I am hoping that relatively few customers are going to be adversely affected by this change. I am trying to work closely with those customers who are affected to help them through the problem. I am sorry I could not think of a better alternative.

1 Like
(Cyril Khamsi) #17

@Philip_Garrett_Appsh Yes, must have crossed. 240713, LINK III. Thank you for your help!

(Philip Garrett) #18

@Cyril_Khamsi

Thanks so much Cyril.

Looking now.

Did my long explanation above make sense?