cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syhrh
Dual Super User
Dual Super User

Add new column to collection after split concat method

Hi, I'm trying to create new columns from my collection, balancelist. I tried using the split concat method in creating the column, col but it only add 1 column, which is Result and then when I want to create another collection, col2 that references col, it doesn't work because the Result is different in col and col2

ClearCollect(col, Split(Left(Concat(balancelist, AnnualLeave &", "&CalamityLeave&", "&CarryForwardLeave&", "&EmergencyLeave&", "&HospitalizationLeave&", "&MaternityLeave&", "&PaternityLeave&", "&SickLeave&", "&QuarantineLeave&", "), 
Len(Concat(balancelist,AnnualLeave&", "&CalamityLeave&", "&CarryForwardLeave&", "&EmergencyLeave&", "&HospitalizationLeave&", "&MaternityLeave&", "&PaternityLeave&", "&SickLeave&", "&QuarantineLeave&", "))-2), ", "));

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:CountRows(Filter(col,Result="AnnualLeave"))/CountRows(col)},
    {LeaveType:"CalamityLeave",Available:CountRows(Filter(col,Result="CalamityLeave"))/CountRows(col)},
    {LeaveType:"CarryForwardLeave",Available:CountRows(Filter(col,Result="CarryForwardLeave"))/CountRows(col)},
    {LeaveType:"EmergencyLeave",Available:CountRows(Filter(col,Result="EmergencyLeave"))/CountRows(col)},
    {LeaveType:"HospitalizationLeave",Available:CountRows(Filter(col,Result="HospitalizationLeave"))/CountRows(col)},
    {LeaveType:"MaternityLeave",Available:CountRows(Filter(col,Result="MaternityLeave"))/CountRows(col)},
    {LeaveType:"PaternityLeave",Available:CountRows(Filter(col,Result="PaternityLeave"))/CountRows(col)},
    {LeaveType:"SickLeave",Available:CountRows(Filter(col,Result="SickLeave"))/CountRows(col)},
    {LeaveType:"QuarantineLeave",Available:CountRows(Filter(col,Result="QuarantineLeave"))/CountRows(col)}
    )
    );

anonymous21_1-1657683768219.pnganonymous21_2-1657684034297.png

anonymous21_3-1657684067385.pngeach row is a different user in the balancelist. 

 

I want to create a dataTable in Power Apps that in the items, is col2 to display LeaveType and Available based on balancelist and only current user logged in. Can anyone please help me with how to do this?

 

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions

The issue here is User().FullName may differ from the Author's Name in the SharePoint. In your SharePoint what is your person column called?

 

I would then change your lookup as suggested by @Adrian_Celis to use email something like:

{LeaveType:"AnnualLeave", Available:LookUp(balancelist,PersonFieldName.Email=User().Email).AnnualLeave},

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

View solution in original post

8 REPLIES 8
Adrian_Celis
Super User
Super User

Hi @syhrh 

Assuming that the column name of the email of the user in balancelist is named EmailColumn, then you can do something like this:

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:LookUp(balancelist,EmailColumn=User().Email).AnnualLeave},
    {LeaveType:"CalamityLeave",Available:LookUp(balancelist,EmailColumn=User().Email).CalamityLeave},
    {LeaveType:"CarryForwardLeave",Available:LookUp(balancelist,EmailColumn=User().Email).CarryForwardLeave},
    {LeaveType:"EmergencyLeave",Available:LookUp(balancelist,EmailColumn=User().Email).EmergencyLeave},
    {LeaveType:"HospitalizationLeave",Available:LookUp(balancelist,EmailColumn=User().Email).HospitalizationLeave},
    {LeaveType:"MaternityLeave",Available:LookUp(balancelist,EmailColumn=User().Email).MaternityLeave},
    {LeaveType:"PaternityLeave",Available:LookUp(balancelist,EmailColumn=User().Email).PaternityLeave},
    {LeaveType:"SickLeave",Available:LookUp(balancelist,EmailColumn=User().Email).SickLeave},
    {LeaveType:"QuarantineLeave",Available:LookUp(balancelist,EmailColumn=User().Email).QuarantineLeave}
    )
    );

 No need to create col1

@Adrian_Celis 

I don't have email column in the balancelist, only Name column with people type. I used this formula but still no values.

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:LookUp(balancelist,Name=User().FullName).AnnualLeave},
    {LeaveType:"CalamityLeave",Available:LookUp(balancelist,Name=User().FullName).CalamityLeave},
    {LeaveType:"CarryForwardLeave",Available:LookUp(balancelist,Name=User().FullName).CarryForwardLeave},
    {LeaveType:"EmergencyLeave",Available:LookUp(balancelist,Name=User().FullName).EmergencyLeave},
    {LeaveType:"HospitalizationLeave",Available:LookUp(balancelist,Name=User().FullName).HospitalizationLeave},
    {LeaveType:"MaternityLeave",Available:LookUp(balancelist,Name=User().FullName).MaternityLeave},
    {LeaveType:"PaternityLeave",Available:LookUp(balancelist,Name=User().FullName).PaternityLeave},
    {LeaveType:"SickLeave",Available:LookUp(balancelist,Name=User().FullName).SickLeave},
    {LeaveType:"QuarantineLeave",Available:LookUp(balancelist,Name=User().FullName).QuarantineLeave}
    )
    );

anonymous21_0-1657694002278.png

 

 

The issue here is User().FullName may differ from the Author's Name in the SharePoint. In your SharePoint what is your person column called?

 

I would then change your lookup as suggested by @Adrian_Celis to use email something like:

{LeaveType:"AnnualLeave", Available:LookUp(balancelist,PersonFieldName.Email=User().Email).AnnualLeave},

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

Hi @syhrh 

If it is a Person Column, then let's use the email of that to compare so we can avoid duplicates (If there are two or more users with the same name). Try this:

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:LookUp(balancelist,Name.Email=User().Email).AnnualLeave},
    {LeaveType:"CalamityLeave",Available:LookUp(balancelist,Name.Email=User().Email).CalamityLeave},
    {LeaveType:"CarryForwardLeave",Available:LookUp(balancelist,Name.Email=User().Email).CarryForwardLeave},
    {LeaveType:"EmergencyLeave",Available:LookUp(balancelist,Name.Email=User().Email).EmergencyLeave},
    {LeaveType:"HospitalizationLeave",Available:LookUp(balancelist,Name.Email=User().Email).HospitalizationLeave},
    {LeaveType:"MaternityLeave",Available:LookUp(balancelist,Name.Email=User().Email).MaternityLeave},
    {LeaveType:"PaternityLeave",Available:LookUp(balancelist,Name.Email=User().Email).PaternityLeave},
    {LeaveType:"SickLeave",Available:LookUp(balancelist,Name.Email=User().Email).SickLeave},
    {LeaveType:"QuarantineLeave",Available:LookUp(balancelist,Name.Email=User().Email).QuarantineLeave}
    )
    );

 

@phipps0218 I re-check my person's column name and use your formula and it works! Thank you.

Can I ask how to change this? To make the purple becomes white or invisible

anonymous21_0-1657695740280.png

 

@syhrh I am glad that worked! Please do accept the solution to help others find it quicker.

Changing that from purple, is this a Data Table or a Gallery? If Data Table, change the SelectedFill Colour.

@Adrian_Celis I tried your formula as well and it works too. Thank you

@phipps0218 It's a data table and it works. Thank you!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,373)