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?
Solved! Go to 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].
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!
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
You should provide more context and details about your tables; their columns and relationships.
Because I loop in my template with in child and i want to accessing a column in a parent record
More Details:
Relevant tables:
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
Can you share your tables schema? The tables with their columns and column types
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].
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!
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.
User | Count |
---|---|
42 | |
25 | |
25 | |
17 | |
12 |