PowerApps으로 만드는 조직도 솔루션 - 04. DB 상세 설명 및 구조 변경

 

PowerApps으로 만든 조직도 솔루션 제안서

 

설정 화면에 버튼을 만들고 각 버튼에 설정

아래 3개의 버튼을 만드는 이유는 각 버튼별 구성을 이해하기 위함이다.

실제 리셋 버튼에 설정하여 한번에 설정을 변경하는 것을 권장한다.

 

 

위의 버튼을 차례대로 초기화하여 값을 설정하면 앱에서 사용하는 모든 DB 설정이 완료되게 된다.

 

부서 정보 초기화
OrgOriginalDepartment 값을 OrgDepartment SharePoint Lists로 변경

 

 

[소스코드] - 부서 정보 초기화

// SharePoint 목록 초기화 // RemoveIf(OrgDepartment,true);  Network Error 발생 Remove(OrgDepartment,OrgDepartment); ForAll(                     // ForAll 아래 OrgOriginalDepartment 행의 개수 만큼 실행     OrgOriginalDepartment,       Collect(                // 기존 데이터에 추가로 생성, 없으면 만들어 짐.         OrgDepartment,       // SharePoint Lists 혹은 Collection         {                  // SharePoint Lists의 경우 이미 목록을 만들어 놓아야 함.             Company: Company,       // 직원 정보를 불러와 해당 값에 넣는다.             Name: Name,             Description: Description,             CompanyID:LookUp(OrgCompany,Name=Company,ID),   // 회사의 이름으로 회사의 ID값을 가져오기             Level:CountA(Split(Description,"/")),   // Description의 값에 / 갯수를 확인하여 Level 설정 예) 영업본부/영업1팀 Level 값은 2             // 상위 부서의 ID를 가져외서 ParentID에 넣기             ParentID : With({varParentName:If(CountA(Split(Description,"/")) >1,Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value)},If(IsBlank(varParentName),0,LookUp(OrgDepartment,Name=varParentName,ID))),                         // OrgOriginalDepartment ID 값을 가져와 5자리의 텍스트로 변경             No: Text(ID,"10000"),             // OrderNo로 부서 화면에 나타나는 순서를 결정             // 5단계의 자리수로 표시 10000 00000 00000 00000 00000 00000 Description 값을 가져와 / 갯수로 단계 확인 후 각 단계별 값을 설정하는 방법             OrderNo:             If(                 CountA(Split(Description,"/"))=1,LookUp(OrgCompany,Name=Company,OrderNo) & Text(ID,"10000") & "0000000000000000000000000",                 CountA(Split(Description,"/"))=2,LookUp(OrgCompany,Name=Company,OrderNo) & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000") & "00000000000000000000",                 CountA(Split(Description,"/"))=3,LookUp(OrgCompany,Name=Company,OrderNo) & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-2).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000") & "000000000000000",                 CountA(Split(Description,"/"))=4,LookUp(OrgCompany,Name=Company,OrderNo) & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-3).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000")& Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-2).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000") & "0000000000",                 CountA(Split(Description,"/"))=5,LookUp(OrgCompany,Name=Company,OrderNo)& Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-4).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-3).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000")& Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-2).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000")  & "00000"             )         }     ) );

 

 

[소스코드] -  직원 정보 초기화

//RemoveIf(OrgEmployee,true); Remove(OrgEmployee, OrgEmployee); ForAll(                     // ForAll 아래 OrgOriginalDepartment 행의 개수 만큼 실행     OrgOriginalEmployee,       Collect(                // 기존 데이터에 추가로 생성, 없으면 만들어 짐.         OrgEmployee,       // SharePoint Lists 혹은 Collection         {                  // SharePoint Lists의 경우 이미 목록을 만들어 놓아야 함.             Company: Company,       // 직원 정보를 불러와 해당 값에 넣는다.             EmpNo : EmpNo,             Department: Department,             Department1: Department1,             Department2: Department2,             Name: Name,             Jobtitle:Jobtitle,             Position:Position,             Mail:Mail,             Mobile:Mobile,             Phone:Phone,             ShortNumber:ShortNumber,             JoinDate:JoinDate,             ResignationDate:ResignationDate,             Specialize:Specialize,             CompanyID:LookUp(OrgCompany,Name=Company,ID),             Level: LookUp(OrgDepartment,Name=Department,Level) + 1,             DepartmentID : LookUp(OrgDepartment,Name=Department,ID),             No: If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90001",With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+1,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1},     Max(var1,var2,var3))),             //OrderNo: Replace(LookUp(OrgDepartment,Name=Department,OrderNo),28,32,If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90001",First(Sort(OrgEmployee,No,SortOrder.Descending)).No+1)),                         OrderNo: Replace(LookUp(OrgDepartment,Name=Department,OrderNo),28,32,                         If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90001",                             With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+1,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1},     Max(var1,var2,var3))                         )                     ),                         Level1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),LookUp(OrgDepartment,Name=Department1,Level)+1),             DepartmentID1 : If(!IsBlank(LookUp(OrgDepartment,Name=Department1,ID)),LookUp(OrgDepartment,Name=Department1,ID)),                         //No1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),If(IsBlank(First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1),"90001",First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1)),             No1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90002",With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+2,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+2,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+2},     Max(var1,var2,var3)))),             //OrderNo1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),Replace(LookUp(OrgDepartment,Name=Department1,OrderNo),28,32,If(IsBlank(First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1),"90001",First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1))),             OrderNo1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),                         Replace(LookUp(OrgDepartment,Name=Department1,OrderNo),28,32,                             If(IsBlank(First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1),"90002",                                 With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+2,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+2,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+2}, Max(var1,var2,var3))                             )                         )                       ),             Level2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),LookUp(OrgDepartment,Name=Department2,Level)+1),             DepartmentID2 : If(!IsBlank(LookUp(OrgDepartment,Name=Department2,ID)),LookUp(OrgDepartment,Name=Department2,ID)),                         //No2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),If(IsBlank(First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2),"90001",First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1)),             No2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90003",With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+3,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+3,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+3},     Max(var1,var2,var3)))),                                     //OrderNo2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),Replace(LookUp(OrgDepartment,Name=Department2,OrderNo),28,32,If(IsBlank(First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2),"90001",First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1)))             OrderNo2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),                             Replace(LookUp(OrgDepartment,Name=Department2,OrderNo),28,32,                                 If(IsBlank(First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2),"90003",                                     With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+3,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+3,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+3}, Max(var1,var2,var3))                                 )                             )                         )                     }     ) );

 

[소스코드] -  컬렉션 초기화

// ClearCollect 은 기존 컬렉션을 지우고 목록의 값을 컬렉션으로 만들기 // OrgDepartment로 ColDepartment 컬렉션을 만들기 ClearCollect(ColDepartment,AddColumns(OrgDepartment,NewID,ID)); // ColDepartment에 OrgCompany 값 (회사 목록)을 추가하여 OrderNo를 동일한 자리수로 만들기 ForAll(     OrgCompany,     Collect(         ColDepartment,         {             No: Text(OrderNo,"00"),             CompanyID : ID,             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true         }     ) ); // OrgEmployee로 ColEmployee 컬렉션을 만들기 ClearCollect(ColEmployee,AddColumns(OrgEmployee,NewID,ID)); // ColEmployee에 OrgCompany 값 (회사 목록)을 추가하여 OrderNo를 동일한 자리수로 만들기 ForAll(     OrgCompany,     Collect(         ColEmployee,         {             No: Text(OrderNo,"00"),             CompanyID : ID,             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true         }     ) ); // Clear(ColOrgAllList); // ColOrgAllList 컬렉션에 회사 정보 넣기 // 회사는 Level 0 으로 설정 ForAll(     OrgCompany,     Collect(ColOrgAllList,         {             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true,             Type:"Company"         }     ) ); // ColOrgAllList 컬렉션에 부서 정보 (ColDepartment 값을 넣기) ForAll(     Filter(ColDepartment,Level<>0),     Collect(ColOrgAllList,         {             Company:Company,             Name: Name,             DepartmentID: ID,             Level: Level,             OrderNo: OrderNo,             Visible:true,             Type:"Department"         }     ) ); // 직원 정보는 겸직을 처리하기 위해 아래와 같이 3번의 ForAll 함수 사용 ForAll(     Filter(ColEmployee,!IsBlank(No) And Level<>0),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             EmpNo : EmpNo,             Name: Name,             DepartmentID: DepartmentID,             Department:Department,             EmployeeID:ID,             Level: Level,             No:No,             OrderNo: OrderNo,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 1         }     ) ); ForAll(     Filter(ColEmployee,!IsBlank(No1)),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             EmpNo : EmpNo,             Name: Name,             DepartmentID: DepartmentID1,             Department:Department1,             EmployeeID:ID,             Level: Level1,             No:No1,             OrderNo: OrderNo1,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 2         }     ) ); ForAll(     Filter(ColEmployee,!IsBlank(No2)),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             EmpNo : EmpNo,             Name: Name,             DepartmentID: DepartmentID2,             Department:Department2,             EmployeeID:ID,             Level: Level2,             No:No2,             OrderNo: OrderNo2,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 3         }     ) );

 

위의 3개 버튼 설정을 리셋 버튼에 설정하여 한번에 구성이 가능하다.

 

리셋 버튼 만 눌러서 초기화 가능 (데이터가 많으면 시간이 오래 걸릴 수 있음.)

// SharePoint 목록 초기화 // RemoveIf(OrgDepartment,true);  Network Error 발생 Remove(OrgDepartment,OrgDepartment); ForAll(                     // ForAll 아래 OrgOriginalDepartment 행의 개수 만큼 실행     OrgOriginalDepartment,       Collect(                // 기존 데이터에 추가로 생성, 없으면 만들어 짐.         OrgDepartment,       // SharePoint Lists 혹은 Collection         {                  // SharePoint Lists의 경우 이미 목록을 만들어 놓아야 함.             Company: Company,       // 직원 정보를 불러와 해당 값에 넣는다.             Name: Name,             Description: Description,             CompanyID:LookUp(OrgCompany,Name=Company,ID),   // 회사의 이름으로 회사의 ID값을 가져오기             Level:CountA(Split(Description,"/")),   // Description의 값에 / 갯수를 확인하여 Level 설정 예) 영업본부/영업1팀 Level 값은 2             // 상위 부서의 ID를 가져외서 ParentID에 넣기             ParentID : With({varParentName:If(CountA(Split(Description,"/")) >1,Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value)},If(IsBlank(varParentName),0,LookUp(OrgDepartment,Name=varParentName,ID))),                         // OrgOriginalDepartment ID 값을 가져와 5자리의 텍스트로 변경             No: Text(ID,"10000"),             // OrderNo로 부서 화면에 나타나는 순서를 결정             // 5단계의 자리수로 표시 10000 00000 00000 00000 00000 00000 Description 값을 가져와 / 갯수로 단계 확인 후 각 단계별 값을 설정하는 방법             OrderNo:             If(                 CountA(Split(Description,"/"))=1,LookUp(OrgCompany,Name=Company,OrderNo) & Text(ID,"10000") & "0000000000000000000000000",                 CountA(Split(Description,"/"))=2,LookUp(OrgCompany,Name=Company,OrderNo) & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000") & "00000000000000000000",                 CountA(Split(Description,"/"))=3,LookUp(OrgCompany,Name=Company,OrderNo) & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-2).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000") & "000000000000000",                 CountA(Split(Description,"/"))=4,LookUp(OrgCompany,Name=Company,OrderNo) & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-3).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000")& Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-2).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000") & "0000000000",                 CountA(Split(Description,"/"))=5,LookUp(OrgCompany,Name=Company,OrderNo)& Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-4).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-3).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000")& Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-2).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(With({varParentName:Index(Split(Description,"/"),CountA(Split(Description,"/"))-1).Value},LookUp(OrgOriginalDepartment,Name=varParentName,ID)),"10000") & Text(ID,"10000")  & "00000"             )         }     ) ); //RemoveIf(OrgEmployee,true); Remove(OrgEmployee, OrgEmployee); ForAll(                     // ForAll 아래 OrgOriginalDepartment 행의 개수 만큼 실행     OrgOriginalEmployee,       Collect(                // 기존 데이터에 추가로 생성, 없으면 만들어 짐.         OrgEmployee,       // SharePoint Lists 혹은 Collection         {                  // SharePoint Lists의 경우 이미 목록을 만들어 놓아야 함.             Company: Company,       // 직원 정보를 불러와 해당 값에 넣는다.             EmpNo : EmpNo,             Department: Department,             Department1: Department1,             Department2: Department2,             Name: Name,             Jobtitle:Jobtitle,             Position:Position,             Mail:Mail,             Mobile:Mobile,             Phone:Phone,             ShortNumber:ShortNumber,             JoinDate:JoinDate,             ResignationDate:ResignationDate,             Specialize:Specialize,             CompanyID:LookUp(OrgCompany,Name=Company,ID),             Level: LookUp(OrgDepartment,Name=Department,Level) + 1,             DepartmentID : LookUp(OrgDepartment,Name=Department,ID),             // 사번이 하나도 없으면 90001 기존 값이 있으면 기존 값에 +1             No: If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90001",With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+1,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1},     Max(var1,var2,var3))),             //OrderNo: Replace(LookUp(OrgDepartment,Name=Department,OrderNo),28,32,If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90001",First(Sort(OrgEmployee,No,SortOrder.Descending)).No+1)),                         OrderNo: Replace(LookUp(OrgDepartment,Name=Department,OrderNo),28,32,                         If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90001",                             With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+1,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1},     Max(var1,var2,var3))                         )                     ),                         Level1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),LookUp(OrgDepartment,Name=Department1,Level)+1),             DepartmentID1 : If(!IsBlank(LookUp(OrgDepartment,Name=Department1,ID)),LookUp(OrgDepartment,Name=Department1,ID)),                         //No1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),If(IsBlank(First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1),"90001",First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1)),             No1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90002",With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+2,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+2,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+2},     Max(var1,var2,var3)))),             //OrderNo1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),Replace(LookUp(OrgDepartment,Name=Department1,OrderNo),28,32,If(IsBlank(First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1),"90001",First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+1))),             OrderNo1: If(!IsBlank(LookUp(OrgDepartment,Name=Department1,Level)),                         Replace(LookUp(OrgDepartment,Name=Department1,OrderNo),28,32,                             If(IsBlank(First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1),"90002",                                 With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+2,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+2,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+2}, Max(var1,var2,var3))                             )                         )                       ),             Level2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),LookUp(OrgDepartment,Name=Department2,Level)+1),             DepartmentID2 : If(!IsBlank(LookUp(OrgDepartment,Name=Department2,ID)),LookUp(OrgDepartment,Name=Department2,ID)),                         //No2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),If(IsBlank(First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2),"90001",First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1)),             No2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),If(IsBlank(First(Sort(OrgEmployee,No,SortOrder.Descending)).No),"90003",With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+3,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+3,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+3},     Max(var1,var2,var3)))),                                     //OrderNo2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),Replace(LookUp(OrgDepartment,Name=Department2,OrderNo),28,32,If(IsBlank(First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2),"90001",First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+1)))             OrderNo2: If(!IsBlank(LookUp(OrgDepartment,Name=Department2,Level)),                             Replace(LookUp(OrgDepartment,Name=Department2,OrderNo),28,32,                                 If(IsBlank(First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2),"90003",                                     With({var1:First(Sort(OrgEmployee,No,SortOrder.Descending)).No+3,var2:First(Sort(OrgEmployee,No1,SortOrder.Descending)).No1+3,var3:First(Sort(OrgEmployee,No2,SortOrder.Descending)).No2+3}, Max(var1,var2,var3))                                 )                             )                         )                     }     ) ); // ClearCollect 은 기존 컬렉션을 지우고 목록의 값을 컬렉션으로 만들기 // OrgDepartment로 ColDepartment 컬렉션을 만들기 ClearCollect(ColDepartment,AddColumns(OrgDepartment,NewID,ID)); // ColDepartment에 OrgCompany 값 (회사 목록)을 추가하여 OrderNo를 동일한 자리수로 만들기 ForAll(     OrgCompany,     Collect(         ColDepartment,         {             No: Text(OrderNo,"00"),             CompanyID : ID,             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true         }     ) ); // OrgEmployee로 ColEmployee 컬렉션을 만들기 ClearCollect(ColEmployee,AddColumns(OrgEmployee,NewID,ID)); // ColEmployee에 OrgCompany 값 (회사 목록)을 추가하여 OrderNo를 동일한 자리수로 만들기 ForAll(     OrgCompany,     Collect(         ColEmployee,         {             No: Text(OrderNo,"00"),             CompanyID : ID,             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true         }     ) ); // Clear(ColOrgAllList); // ColOrgAllList 컬렉션에 회사 정보 넣기 // 회사는 Level 0 으로 설정 ForAll(     OrgCompany,     Collect(ColOrgAllList,         {             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true,             Type:"Company"         }     ) ); // ColOrgAllList 컬렉션에 부서 정보 (ColDepartment 값을 넣기) ForAll(     Filter(ColDepartment,Level<>0),     Collect(ColOrgAllList,         {             Company:Company,             Name: Name,             DepartmentID: ID,             Level: Level,             OrderNo: OrderNo,             Visible:true,             Type:"Department"         }     ) ); // 직원 정보는 겸직을 처리하기 위해 아래와 같이 3번의 ForAll 함수 사용 ForAll(     Filter(ColEmployee,!IsBlank(No) And Level<>0),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             EmpNo : EmpNo,             Name: Name,             DepartmentID: DepartmentID,             Department:Department,             EmployeeID:ID,             Level: Level,             No:No,             OrderNo: OrderNo,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 1         }     ) ); ForAll(     Filter(ColEmployee,!IsBlank(No1)),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             EmpNo : EmpNo,             Name: Name,             DepartmentID: DepartmentID1,             Department:Department1,             EmployeeID:ID,             Level: Level1,             No:No1,             OrderNo: OrderNo1,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 2         }     ) ); ForAll(     Filter(ColEmployee,!IsBlank(No2)),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             EmpNo : EmpNo,             Name: Name,             DepartmentID: DepartmentID2,             Department:Department2,             EmployeeID:ID,             Level: Level2,             No:No2,             OrderNo: OrderNo2,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 3         }     ) );

 

프로그램 시작할때 컬렉션 만들기

컬렉션은 앱 내에서 데이터를 저장하고 관리하는 데이터 구조로 접속 장치에 사용되고 앱을 종료하면 초기화 된다.

 

OnStart =  // 관리자 그룹인지 확인하여 관리자 그룹이면 왼쪽 메뉴가 나타나게 하고 그렇지 않은 일반 사용자는 왼쪽 메뉴가 나타나지 않게 구성 Set(varGroupMembers,Office365그룹.ListGroupMembers("1eddd77c-c080-4427-a3c7-5908af36d342").value); // varLeftSize 값이 관리자이면 false로 설정, 관리자가 아니면 true Set(varLeftSize,IsBlank(LookUp(varGroupMembers,mail=User().Email))); ClearCollect(ColDepartment,AddColumns(OrgDepartment,NewID,ID)); ForAll(     OrgCompany,     Collect(         ColDepartment,         {             No: Text(OrderNo,"00"),             CompanyID : ID,             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true         }     ) ); ClearCollect(ColEmployee,AddColumns(OrgEmployee,NewID,ID)); ForAll(     OrgCompany,     Collect(         ColEmployee,         {             No: Text(OrderNo,"00"),             CompanyID : ID,             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true         }     ) ); Clear(ColOrgAllList); ForAll(     OrgCompany,     Collect(ColOrgAllList,         {             Name: Name,             Level: 0,             OrderNo: Text(OrderNo,"00") & "000000000000000000000000000000",             Visible:true,             Type:"Company"         }     ) ); ForAll(     Filter(ColDepartment,Level<>0),     Collect(ColOrgAllList,         {             Company:Company,             Name: Name,             DepartmentID: ID,             Level: Level,             OrderNo: OrderNo,             Visible:true,             Type:"Department"         }     ) ); ForAll(     Filter(ColEmployee,!IsBlank(No) And Level<>0),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             Name: Name,             DepartmentID: DepartmentID,             Department:Department,             EmployeeID:ID,             Level: Level,             No:No,             OrderNo: OrderNo,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             JoinDate:JoinDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 1         }     ) ); ForAll(     Filter(ColEmployee,!IsBlank(No1)),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             Name: Name,             DepartmentID: DepartmentID1,             Department:Department1,             EmployeeID:ID,             Level: Level1,             No:No1,             OrderNo: OrderNo1,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             JoinDate:JoinDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 2         }     ) ); ForAll(     Filter(ColEmployee,!IsBlank(No2)),     Collect(ColOrgAllList,         {             Company:Company,             CompanyID:CompanyID,             Name: Name,             DepartmentID: DepartmentID2,             Department:Department2,             EmployeeID:ID,             Level: Level2,             No:No2,             OrderNo: OrderNo2,             Jobtitle : Jobtitle,             Mail:Mail,             Phone:Phone,             Mobile:Mobile,             ShortNumber:ShortNumber,             Visible:true,             ResignationDate:ResignationDate,             JoinDate:JoinDate,             Specialize:Specialize,             Type:"Employee",             DepartmentNum : 3         }     ) );