You’re offline. This is a read only version of the page.
Announcements
100
Hi all,
I have an issue with one of my collection from Sharepoint.
Here is the architecture of my sharepoint List:
| Document ID | Role 1 | Role 2 | Role 3 | Role 4 | Role 5 |
| ID001 | Yes | Yes | No | No | Yes |
| ID002 | No | No | Yes | Yes | Yes |
I would like to make a collection from this with this architecture (List document IDs with Roles where Yes is selected):
| Document ID | Role |
| ID001 | Role 1 |
| ID001 | Role 2 |
| ID001 | Role 5 |
| ID002 | Role 3 |
| ID002 | Role 4 |
| ID002 | Role 5 |
Do you think it is possible? How could I achieve this?
Thank you in advance for all the help you could give!
Hi @cfoenki
Yes, it’s possible to achieve this based on your description.
You can refer to the following steps as below:
1.Here is the SharePoint list in my side:
|
2.Add a button and set the OnSelect property of button to:
Share
Report
All responses (
Answers (
ClearCollect(SampleCollect,SampleList)// SampleList is my sharepoint list, this formula aims to add a collection which use the sharepoint list value.
3.Set the OnSelect property of button to:
ClearCollect(SampleGroup,GroupBy(SampleCollect,"Title","Role1","Role2","Role3","Role4","Role5","Others"))//this formula aims to coalesce column which is hidden column in sharepoint list:
4.Set the OnSelect property of button to:
ClearCollect(SampleCollection,DropColumns(SampleGroup,"Others"))// this formula aims to drop the other column
5.Set the OnSelect property of button to:
ClearCollect(SampleCollections,RenameColumns(SampleCollection,"Title","Document ID"))// this formula aims to Rename the column name
6.Set the OnSelect property of button to:
ClearCollect(SampleCollectionA,AddColumns(SampleCollections,"Role",""))// this formula aims to add a “Role” column
7.Set the OnSelect property of button to:
ForAll(SampleCollections,
If(Role1=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role1",'Document ID':ThisRecord.'Document ID'}));
If(Role2=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role2",'Document ID':ThisRecord.'Document ID'}));
If(Role3=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role3",'Document ID':ThisRecord.'Document ID'}));
If(Role4=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role4",'Document ID':ThisRecord.'Document ID'}));
If(Role5=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role5",'Document ID':ThisRecord.'Document ID'}));)
//this formula aims to add multi records which meet the condition ‘Role=true’ and patch the right value.
8.Set the OnSelect property of button to:
ClearCollect(SampleCollectionB,DropColumns(SampleCollectionA,"Role1","Role2","Role3","Role4","Role5"))//this formula aims to drop the column ‘role1’~’role5’
9.Set the OnSelect property of button to:
ClearCollect(SampleCollectionC,RemoveIf(SampleCollectionB,Role=""))// this formula aims to remove two records which the value is blank.
|
Hope it helps!
Thanks,
Arrow
