Hi,
I want to create a data table based on 2 Sharepoint lists, Employee and Application Record.
The data table has EmployeeName column based on the Employee list
Name is people column
Besides EmployeeName, there are AnnualLeave, CalamityLeave,CarryForwardLeave and etc for each of the employee based on Application Record list. Each of the columns sums the Approval Status: Approved and Total Days Applied.
I tried this formula below but it doesn't display all names and sums up all employees Total Days Applied.
ClearCollect(employeeName, ShowColumns(Employee,"Name"));
ClearCollect(var,ForAll(employeeName,Name).DisplayName);
ClearCollect(annualApproved,Filter('Application Record','Leave Type'.Value="Annual Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(calamityApproved,Filter('Application Record','Leave Type'.Value="Calamity Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(carryForwardApproved,Filter('Application Record','Leave Type'.Value="Carry Forward Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(emergencyApproved,Filter('Application Record','Leave Type'.Value="Emergency Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(hospitalizationApproved,Filter('Application Record','Leave Type'.Value="Hospitalization Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(maternityApproved,Filter('Application Record','Leave Type'.Value="Maternity Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(paternityApproved,Filter('Application Record','Leave Type'.Value="Paternity Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(sickApproved,Filter('Application Record','Leave Type'.Value="Sick Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(quarantineApproved,Filter('Application Record','Leave Type'.Value="Quarantine Leave" && 'Approval Status'.Value="Approved"));
ClearCollect(table1,
Table( {
EmployeeName: var.DisplayName,
AnnualLeave: Sum(annualApproved,'Total Days Applied'),
CalamityLeave:Sum(calamityApproved,'Total Days Applied'),
CarryForwardLeave:Sum(carryForwardApproved,'Total Days Applied'),
EmergencyLeave:Sum(emergencyApproved,'Total Days Applied'),
HospitalizationLeave:Sum(hospitalizationApproved,'Total Days Applied'),
MaternityLeave:Sum(maternityApproved,'Total Days Applied'),
PaternityLeave:Sum(paternityApproved,'Total Days Applied'),
SickLeave:Sum(sickApproved,'Total Days Applied'),
QuarantineLeave: Sum(quarantineApproved,'Total Days Applied')}
));
I need to create Name and then their respective leave type and Total Days Applied for themselves. Use Filter? How to do that?
Thank you.
