Template Issue

Drew_D · 05-12-2021 05:53 PM

I’ll start by saying that I’ve looked for an answer on this but I can’t find one or simply don’t know what to search for. I have some reports setup that worked at one point however they’re no longer sending the information in the template and instead filling the first cell with <templateReference:id=1>

Anyone know what this is? how to fix it?

This is what the template looks like:

This is the emailed report:

Steve

I’ve seen several community members note this behavior recently. I’d call this a bug. Please contact support@appsheet.com for help with this. I’ll also escalate internally.

Zhifeng_Lin

Hi Drew,
could you share your template?

As for reference on how to use excel templates, here is a public help doc – Microsoft Excel Email Templates | AppSheet Help Center

Thanks!

Drew_D

Please find the template at this link: https://drive.google.com/file/d/1zUpCDF-cmURWpw_ovPfNIYPjafVXOrnR/view?usp=sharing

Alan_Thorp

Is there an answer to this issue?

I have a similar problem. I think that it is to do with there not being any rows in the table to display, either through the table being empty or the filter/select method not returning any rows to export. I’m trying to find a workaround that removes it, but haven’t had any luck so far.

I’ve tried putting an IF() condition on the start statement like this:

<<Start: 
IF(
  COUNT(FILTER("Thing_Table", ([UniqueID] = [_THISROW].[UniqueID])))=0,
  LIST(),
  FILTER("Thing_Table",([UniqueID] = [_THISROW].[UniqueID]))
)>>
<<SELECT(Thing_Table[Thing_name], ([UniqueID] = [_THISROW].[UniqueID]))>>

That didn’t work and returned the error:
Start expression expression should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values.

I also tried putting the IF() condition on the following expression:

<<Start: FILTER("Thing_Table",([UniqueID] = [_THISROW].[UniqueID]))>>
<<IF(
  SELECT(Thing_Table[Thing_name], ([UniqueID] = [_THISROW].[UniqueID]))="",
  "",
  SELECT(Thing_Table[Thing_name], ([UniqueID] = [_THISROW].[UniqueID]))
)>>

That didn’t work either.

So finally I tried using the <<If: yesno-expression>> method detailed here, all in the first cell like this:

<<If: COUNT(FILTER("Thing_Table", ([UniqueID] = [_THISROW].[UniqueID])))>0>>
<<Start: FILTER("Thing_Table",([UniqueID] = [_THISROW].[UniqueID]))>>
<<SELECT(Thing_Table[Thing_name], ([UniqueID] = [_THISROW].[UniqueID]))>>

*** finishing off in the last cell like this ***

<<[Thingamyjig]>><<End>><<EndIf>>

No luck with that either. I got the following message back from the Monitor:
Expression ‘If: COUNT(FILTER(“Thing_Table”, ([UniqueID] = [_THISROW].[UniqueID])))>0’ is invalid due to: Unable to find function ‘IF: COUNT’, did you mean ‘COUNT’?.

I note that the Excel export is not listed for this method in the webpage that I linked above, but I thought that I’d give it a shot anyway. Look like this it an unresolved issue. If someone knows a way around this problem please let me know.

Drew_D

This is a brief response but yes I’ve found the issue to be related directly to having no records to display for the table.

Steve

Alan_Thorp:

I’ve tried putting an IF() condition on the start statement like this:
<<Start: 
IF(
  COUNT(FILTER("Thing_Table", ([UniqueID] = [_THISROW].[UniqueID])))=0,
  LIST(),
  FILTER("Thing_Table",([UniqueID] = [_THISROW].[UniqueID]))
)>>
<<SELECT(Thing_Table[Thing_name], ([UniqueID] = [_THISROW].[UniqueID]))>>
That didn’t work and returned the error:
Start expression expression should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values.

The problem here is that the data type of the result of an IF() expression is taken from the then expression. LIST() returns an empty list of (I believe) Text; FILTER(...) returns a list of Ref. But because the then expression is list-of-Text, the else expression result is converted to a list of Text.

<<Start>> wants a list of Ref. A list of Text is an error.

Believe it or not, you should be able to fix this particular problem by simply restructuring your expression:

<<Start: 
IF(
  COUNT(FILTER("Thing_Table", ([UniqueID] = [_THISROW].[UniqueID]))) <> 0,
  FILTER("Thing_Table",([UniqueID] = [_THISROW].[UniqueID])),
  LIST()
)>>

Alan_Thorp

Hi Steve,

Thanks for your advice, I appreciate your reply. The code worked, but the original issue is still there. This is that when the returned number of rows is zero we get a results returned in the first cell that is something like:

<<templateReference:id=7>>

If the <<If: >> statements worked for XLSX export I think that this would be easy to solve.

It’s not really a problem for me in this instance, but I don’t suppose that you have any other suggestions for how to get around this issue?

Thanks again.

Steve

That’s clearly a bug. I encourage you to report it to Support.

https://www.appsheet.com/Support/Contact

Alan_Thorp

Right, okay. I tried that, but the support page isn’t working and refers me to the support page that isn’t working. Hmmmm.

I tried yesterday and today. Maybe I’ll try it again in a few days.

Br-y-an

Are you able to provide details of what occurred when trying to open a support case? Was there an error message?

ecitonapplicati

We are also getting this when creating an xls file from template when the lists are empty. This is not the default behaviour when creating a pdf file (then it just empty):

Was this ever resolved by anyone?

Steve

Please contact AppSheet Support for help with this.