Introduction
When other functions are nested within a ForAll, like Collect or Filter, it can get super confusing. Especially, if the table has a LookUp column, and can result in errors (Error when trying to retrieve data from the network). Here is an example of the error (trying to collect all instructions related to all the departments and the instructors table has a lookup to the departments table):
There are multiple ways to fix this.
Option 1 - ForAll & Disambiguation operator
The 1st way is to use the disambiguation operator (since disambiguation is the main issue here).
ForAll(
colInstructors,
Collect(
colCourses,
Filter(
[@Courses],
Department.Department = colInstructors[@'Department ID'].Department
)
)
)Option 2 - ForAll & AddColumns
The 2nd way is to add the Department GUID to the colInstructors collection itself, using a completely different name, so it can be referenced directly without any need for disambiguation.
ClearCollect(
colInstructorsWithDepartmentID,
AddColumns(
colInstructors,
"DepartmentGUID",
'Department ID'.Department
)
);
ForAll(
colInstructorsWithDepartmentID,
Collect(
colCourses,
Filter(
[@Courses],
Department.Department = DepartmentGUID
)
)
)Option 3 - ForAll & As operator
The 3rd way is to use the As operator that helps solve the issue in a way similar to the disambiguation operator.
ForAll(
colInstructors As InstructorRecords,
Collect(
colCourses,
Filter(
[@Courses],
Department.Department = InstructorRecords.'Department ID'.Department
)
)
)Stay tuned for the remaining 20 tips!