Parent Expressions in templates as filter criteria

[m_guenther]

When I filter in SELECT or FILTER statment with filter criteria // [_THISROW-1].[column] it does not work.

Example:

Student ID: cc48e040  

<<[_THISROW-1].[Student].[Student ID]>>

//returns cc48e040

Course Registration by Student filter with student ID as String --> works

<<SELECT([Related CourseRegistrations][Course Registration ID], ([Student ID] = “cc48e040”))>>

Course Registration by Student filter with column in parent --> does not work

<<SELECT([Related CourseRegistrations][Course Registration ID],([Student ID] = [_THISROW-1].[Student].[Student ID]))>>

whats wrong?

[Marc_Dillon · Accepted solution]

This template is running in the context of a Semester record, correct?

Presumably, the Semester Table has a [Student] column, that references a Student record, which has [Student ID] as its key column? If all that is true, you're making your expressions far more complex than they need to be. <<_THISROW].[Student].[Student ID]>> on the first line would be 100% equivalent to simply <<[Student]>>

It appears on line 10/11 that you are successfully getting a student id, but that is from the base context. You can't simply copy the same expression and use it within another context, like the SELECT expression on line 19.

If my assumptions above are correct, you should get rid of the ".[Student ID]" everywhere. And also try with a [_THISROW] or a [_THISROW-2] instead of [_THISROW-1].

[SkrOYC]

Why you need [_THISROW-1]?

Here is an awesome reference about it's usage by @Steve:

Re: FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), R... - Page 2 - Google Cloud Community

[Marc_Dillon]

You should provide more context and details about your tables; their columns and relationships.

[m_guenther]

Because I loop in my template with in child and i want to accessing a column in a parent record

[m_guenther]

More Details:

Relevant tables:

• Transcript (has one Student)
• Student (has Semesters)
• Semester (has Course Registrations for a Student)

Tempate Code

Student ID:<< [_THISROW].[Student].[Student ID]>>

CourseRegistrations: 
<<FILTER("CourseRegistration", ([Student ID] =  [_THISROW].[Student].[Student ID]))>>

<<Start:ORDERBY([Student].[Semesters],[Start])>> 
	<<[Name]>>
<< [Related CourseRegistrations]>>

Student ID: << [_THISROW-1].[Student].[Student ID]>>  //  [_THISROW-1].[Student].[Student ID]
// Result is  “cc48e040”

Course Registration by Student filter with student ID as String: – > works
<<SELECT([Related CourseRegistrations][Course Registration ID],([Student ID] = “cc48e040”))>>

// SELECT([Related CourseRegistrations][Course Registration ID], ([Student ID] = “cc48e040”))

Course Registration by Student filter with column in parent: – > does not work
<<SELECT([Related CourseRegistrations][Course Registration ID],([Student ID] =  [_THISROW-1].[Student].[Student ID]))>>

// SELECT([Related CourseRegistrations][Course Registration ID],([Student ID] =  [_THISROW-1].[Student].[Student ID]))

<<End>>

Result Test PDF

https://drive.google.com/file/d/1lBY-TDwPxknLButNyII-CltQ-z5-4a3b/view?usp=sharing

[SkrOYC]

Can you share your tables schema? The tables with their columns and column types

[Marc_Dillon · Accepted solution]

This template is running in the context of a Semester record, correct?

Presumably, the Semester Table has a [Student] column, that references a Student record, which has [Student ID] as its key column? If all that is true, you're making your expressions far more complex than they need to be. <<_THISROW].[Student].[Student ID]>> on the first line would be 100% equivalent to simply <<[Student]>>

It appears on line 10/11 that you are successfully getting a student id, but that is from the base context. You can't simply copy the same expression and use it within another context, like the SELECT expression on line 19.

If my assumptions above are correct, you should get rid of the ".[Student ID]" everywhere. And also try with a [_THISROW] or a [_THISROW-2] instead of [_THISROW-1].

[m_guenther · Accepted solution]

It works with  [_THISROW-2] as filter in the select statment.

I dont know exactly why since from my point of view, in the context I am referring to the parent and not to the grandparent. https://help.appsheet.com/en/articles/961746-template-start-expressions

Presumably, in this context, we are one hierarchy level further down within the select stament.

Thank you for your help!

[Marc_Dillon]

Also, if your hierarchy is Student>Semester>Class Reg, shouldn't the [Related Classregs] already be filtered by the Student? I feel like you shouldn't need to be doing this SELECT in the first place.