I need to do an outer join which returns a list of admins for a given department with a list of non-admins appended to it. This is used in the UI to allow for assigning non-admins as admins or removing admin privs from existing admins.

I'm nearly there with this code but the generated sql returns a list of admins but nulls for the rest of the non-admins. The 2nd query shows what I need it to return. Is this possible with a tweak to my code or should i go down a different path?

Thanks

AttendanceAdminCollection attendanceAdmins = new AttendanceAdminCollection();

IPredicateExpression filter = new PredicateExpression();
filter.Add(EmployeeFields.Active == 1);

IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(AttendanceAdminFields.DepartmentId == CurrentDepartment);

RelationCollection relation = new RelationCollection();
relation.Add(EmployeeEntity.Relations.AttendanceAdminEntityUsingEmployeeId, JoinHint.Left).CustomFilter = customFilter;

SortExpression sort = new SortExpression();
sort.Add(AttendanceAdminFields.DepartmentId | SortOperator.Descending);
sort.Add(EmployeeFields.LastName | SortOperator.Ascending);

attendanceAdmins.GetMulti(filter, 0, sort, relation);

SELECT 
AttendanceAdmin.DepartmentId, 
AttendanceAdmin.EmployeeId 
FROM 
Employee  LEFT JOIN AttendanceAdmin  ON  Employee.EmployeeID=AttendanceAdmin.EmployeeId 
AND AttendanceAdmin.DepartmentId = 25 
WHERE Employee.Active = 1 
ORDER BY AttendanceAdmin.DepartmentId DESC,Employee.LastName ASC

SELECT 
AttendanceAdmin.DepartmentId, 
Employee.FirstName,
Employee.LastName,
Employee.EmployeeId
FROM 
Employee  LEFT JOIN AttendanceAdmin  ON  Employee.EmployeeID=AttendanceAdmin.EmployeeId 
AND AttendanceAdmin.DepartmentId = 25 
WHERE Employee.Active = 1 
ORDER BY AttendanceAdmin.DepartmentId DESC,Employee.LastName ASC

Hi, you could rather use a dynamic list or a typed list, this is the code for your case using dynamic lists:

DataTable AttendanceAdminDynamicList = new DataTable();

ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(AttendanceAdminFields.DepartmentId, 0, "DepartmentId");
fields.DefineField(EmployeeFields.FirstName, 1, "FirstName");
fields.DefineField(EmployeeFields.LastName, 2, "LastName");
fields.DefineField(EmployeeFields.EmployeeId, 3, "EmployeeId");

IPredicateExpression filter = new PredicateExpression();
filter.Add(EmployeeFields.Active == 1);

IPredicateExpression customFilter = new PredicateExpression();
customFilter.Add(AttendanceAdminFields.DepartmentId == CurrentDepartment);

IRelationCollection relations = new RelationCollection();
relations.Add(EmployeeEntity.Relations.AttendanceAdminEntityUsingEmployeeId, JoinHint.Left).CustomFilter = customFilter;

SortExpression sort = new SortExpression();
sort.Add(AttendanceAdminFields.DepartmentId | SortOperator.Descending);
sort.Add(EmployeeFields.LastName | SortOperator.Ascending);

dao.GetMultiAsDataTable(fields, AttendanceAdminDynamicList, 0, sort, filter, relations, false, null, null, 0, 0);

For more information refer to the documentation: Generated code - Using dynamic lists, SelfServicing Designer - Adding typed lists Generated code - Using the typed list classes, SelfServicing

excellent, thank you! I searched the forums and documentation however I wasn't exactly sure what to look for or how to phrase it.