Start Expression Error

[Shaun_Stringer]

Hi,

I am having trouble with a Start Expression in a report template.

<<Start: SELECT(Field Data[Key],AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To],IN([Watershed],[_THISROW].[Watershed]), IN([Site Name],[_THISROW].[Site Name]), IN([Purpose of Visit],[_THISROW].[Purpose of Visit])))>>

I am receiving this error: 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.

If I test the expression in a virtual column it seems to work and does display the filtered rows that I am expecting to see. Also, if I remove the last three clauses of the AND() and just use the date conditions it works well in the report template;

<<Start: SELECT(Field Data[Key],AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To]))>>

I feel like I am missing something obvious. Thanks in advance for your help.

[Suvrutt_Gurjar]

Could you mention the type of  the columns associated with [_THISROW] in the expression, namely [ Watershed], [Site Name] and [Purspose of Visit]

[Shaun_Stringer]

Yes. The Field Data table is where all the filtered rows should be coming from. The Reports table is the interface where Users will query the data they want from Field Data. The Site Name column in the Field Data table is a Ref to a parent table (Site Information).

[Suvrutt_Gurjar]

Thank you. Please try 

ISNOTBLANK (INTERSECT([Purpose of Visit],[_THISROW].[Purpose of Visit]))

in place of

IN([Purpose of Visit],[_THISROW].[Purpose of Visit])

In the filter expression.

This is so because [Purpose of Visit] are enumlist columns in both the tables and IN() requires the first argument to be a single element.

[Shaun_Stringer]

That did not work though there is more to the error message.

Error Message

Error encountered in step with name [Field Log Report PDF]: Error: Task 'Field Log Report PDF' Attachment template. Expression 'SELECT(Field Data[Key],AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To],IN([Watershed],[_THISROW].[Watershed]), IN([Site Name],[_THISROW].[Site Name]), ISNOTBLANK(INTERSECT([Purpose of Visit],[_THISROW].[Purpose of Visit]))))' is invalid due to: Parameter 2 of function IN is of the wrong type. Error: Task 'Field Log Report PDF' Attachment template. Start expression 'SELECT(Field Data[Key],AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To],IN([Watershed],[_THISROW].[Watershed]), IN([Site Name],[_THISROW].[Site Name]), ISNOTBLANK(INTERSECT([Purpose of Visit],[_THISROW].[Purpose of Visit]))))' 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.

[Suvrutt_Gurjar]

My suggestion will be please add the remaining two IN() expressions one by one and test the  longer expression. 

This will let us know which iN() is failing to analyze further. Plesse retain the Intersect based sub expression as that seems to be working.

[Shaun_Stringer]

Unfortunately, that did not work either. If I change either of the IN() expressions, the error still noted a bad parameter 2 of the IN function. Once everything is changed to INTERSECT, I get the following...

Error encountered in step with name [Field Log Report PDF]: Error: Task 'Field Log Report PDF' Attachment template. Expression 'SELECT(Field Data[Key],AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To], ISNOTBLANK(INTERSECT([Watershed],[_THISROW].[Watershed])), ISNOTBLANK(INTERSECT([Site Name],[_THISROW].[Site Name])), ISNOTBLANK(INTERSECT([Purpose of Visit],[_THISROW].[Purpose of Visit]))))' is invalid due to: INTERSECT takes two arguments of type List.. Error: Task 'Field Log Report PDF' Attachment template. Start expression 'SELECT(Field Data[Key],AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To], ISNOTBLANK(INTERSECT([Watershed],[_THISROW].[Watershed])), ISNOTBLANK(INTERSECT([Site Name],[_THISROW].[Site Name])), ISNOTBLANK(INTERSECT([Purpose of Visit],[_THISROW].[Purpose of Visit]))))' 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.

Could changing the enumlist columns to base type list be helpful here?

[Suvrutt_Gurjar]

Sorry. I did not suggest INTERSECT() to be used for [Site Name] and [Watershed] columns. It will simply not work for those columns as those are not list type columns.

---

@Suvrutt_Gurjar wrote:

please add the remaining two IN() expressions one by one and test the  longer expression. 

---

---

@Suvrutt_Gurjar wrote:

This will let us know which iN() is failing to analyze further.

---

What I meant was 

1. Please test with INTERSECT() only for [Purpose of Visit] column. Test the expression once with [Watershed] column and once with [Site Name] column.

Something like 

SELECT(

               Field Data[Key],

                                           AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To],           

                                      IN([Watershed],[_THISROW].[Watershed]),

                                     ISNOTBLANK (INTERSECT([Purpose of Visit],[_THISROW].[Purpose of Visit]))

                                          )

             )

And then as follows

SELECT(

               Field Data[Key],

                                           AND([Date]>=[_THISROW].[Date From], [Date]<=[_THISROW].[Date To],           

                                      IN([Site Name],[_THISROW].[Site Name]),

                                     ISNOTBLANK (INTERSECT([Purpose of Visit],[_THISROW].[Purpose of Visit]))

                                          )

             )

This will probably help in knowing which of the two IN() expressions is failing.