Merge two arrays for Export option in Power Apps

[yashagrawal]

Dear Team,

I am creating an export option from Power Apps to a sharepoint List.

The Sharepoint list is a issues tracker. - Having inputs like ID, Title (Description of tasks), End Date. - Let's Call this Task Table

This sharepoint list is also connected to another sharepoint list which stores comments and each row in this Comments Table have and ID acting as a forgign key for the connection.

In the export I want to give export of the tasks, and each task will have 1 column of comments - wherein all the comments of that tasks will be listed.

My approach to the solution: 

I would like to thank @RezaDorrani - for sharing this insight in the video titled "Export Power Apps data to Excel in CSV format". For a single sharepoint file, I am able to work it out.

Working on similar lines i am facing challenge to get the desired Output.

The steps of my approach are as listed below.

1.) Get Tasks table and Comments table.

2.) Filter the required tasks.

3.) Apply to each on the Filter Tasks - For each task, Filter the comments, then parse it. Then for each comment obtained, append it to a string variable. Now after going through all the Comments - append it to an array variable. Then repeat for next task.

So for 2 tasks - i am getting an array of 2 elements each element corresponding to each task, which are accessible by index [0] and [1] resp.

Now for export I need to create a CSV Table.

In Create CSV Table I am unable to use the item number kind of reference for the array variable which contains the text.

I Tried to add index number in the Task Table, using the Select and Map function. After adding not able to parse Json since it is creating the array with 1 element only. 

Currently i get all the columns in Parse Json, but in this approach, the Parse json restricts itself and show only the "Body and Item property", which then poses challenge to create csv.

Also while parsing after pasting the Array in JSON Schema - The schema coverts it into a type of object instead of array. And then gives an error. Maybe array is not getting generated correctly. 

Request to please suggest the proper approach to solve this.

[v-jefferni]

Hi @yashagrawal ,

Do you want to merge two SharePoint lists to a collection in Power apps, and create a csv file?

Based on your description, I have made a simple test for your reference.

SharePoint lists for test:

Merge two lists to a collection by the following formula:

As shown below, you could use a label to display the JSON string of collection in power apps.

Click “Generate from sample” in Parse JSON Action, and copy JSON string into it.

Click “Done” button, and it will generate Schema automatically.

Result:

Best regards,

[yashagrawal]

Dear @v-jefferni , Thanks for the revert.

I tried to work upon the solution as suggested by you below is the finding listed.

While creating CSV only the first comment is being shown not all.

I am explaining the requirements with the use of the tables.

1.) I have a table/ Sharepoint List which have tasks, (Date1<Date2<Date3.....)

Table Task
ID	Title	Created	Target
1	Task 1	Person 1	Date 1
2	Task 2	Person 2	Date 2
3	Task 3	Person 3	Date 3
4	Task 4	Person 4	Date 4
5	Task 5	Person 5	Date 5

 Secondly, I have Comments Table: The Task_ID is relation between the both tables and act as foreign key,

Table Comments
ID	Title_Comment	Task_ID	Created	Created Date
1	Comment 1	1	Person 1	Date 1
2	Comment 2	2	Person 2	Date 2
3	Comment 3	1	Person 1	Date 3
4	Comment 4	3	Person 3	Date 4
5	Comment 5	4	Person 5	Date 5
6	Comment 6	1	Person 1	Date 6
7	Comment 7	5	Person 5	Date 7
8	Comment 8	1	Person 2	Date 8
9	Comment 9	2	Person 5	Date 9
10	Comment 10	3	Person 2	Date 10

The final Result is based upon the task tracker Table with only 1 column coming from Comments Table:

Final Result
ID	Title	Created	Target	Comment
1	Task 1	Person 1	Date 1	Date-8 : Person 2 - Comment 8; Date-6 : Person 1 - Comment 6; Date-3 : Person 1 - Comment 3; Date-6 : Person 1 - Comment 1;
2	Task 2	Person 2	Date 2	Date-2 : Person 2 - Comment 2; Date-9 : Person 5 - Comment 9;
3	Task 3	Person 3	Date 3	Date-10 : Person 2 - Comment 10; Date-4 : Person 3 - Comment 4;
4	Task 4	Person 4	Date 4	Date-5 : Person 5 - Comment 5;
5	Task 5	Person 5	Date 5	Date-7 : Person 5 - Comment 7;

The Comments Column should contain all the text with respect to each Task.

The first date should be the newest one - then the Displayname of the person who created it and then the text.

If there are more than 1 comments, it should come below it in a new line.

Presently with the solution - I am getting only the first comment from the list of comments. But there are multiple comments as well.

Please suggest.

[v-jefferni]

Hi @yashagrawal ,

You could refer to the following code

Best regards,

[yashagrawal]

Dear @v-jefferni ,Thanks using the concat function now am able to combine all comments in the single cell.

But i am still facing challenge in 2 steps.

1.) the exact text from comments is only containing the comments text. I also want to add Date and Displayname of Author. I tried to do it as per the code below but not able to crack it. Request to please suggest. 

ClearCollect(exp_tt_2,ForAll(Points_List As pt,{Point_ID: pt.Point_ID,Title: pt.Title,Comment: Concat(Filter(Comments,'Point Id' = pt.Point_ID).Comment,Concatenate(Comments.Comment,Created,Author.DisplayName),Char(10))})); 

2.) Since there are lot of tasks and many users use this app, this feature will enable export of all the tasks and will reduce the performance. Also only 500 tasks will come.

I want to filter these tasks based on the following criteria:

My Tasks List is having 3 columns, namely:

1.) Owner - which is a person or group column

2.) Author - By default our - Sharepoint person or group column

3.) Contributor - which is Multiple person or group column

A task would be related to the user if his name appears in any of the 3 above columns.

At the onstart of the app I get the from the User() the mail ID.

then I need to apply check on First(Split(varUser.Email,"@")).Result. Suppose my mail Id is abcxyz123@pqrs.in

then first split... code returns me abcxyz123. Now I want to check whether this text i.e. abcxyz123 is in Onwer mail id, Contributors (multiple persons column), or Author. If yes, then filter else do not filter.

This filter will be on the Tasks List. 

Request to please suggest.

[v-jefferni]

Hi @yashagrawal ,

All comments order by time:

Code for filter looks like this:

Best regards,

[yashagrawal]

Dear @v-jefferni ,

It is mostly done. Just need one last help.

presently as per the flow, first i am creating a Collection by filtering on the Mail id (suggested above) by the below formula.

ClearCollect(exp_tt_1,SortByColumns(Filter(Points_List,Or(split_user in Owner.Email,split_user in Wellwisher.Email,split_user in Concat(Contributors,Email)),"Created",Descending));

and then using this exp_tt_1 colelction to make the array for export.

ClearCollect(exp_tt_2,ForAll(exp_tt_1 As pt,{Point_ID: pt.Point_ID,Customer_Name:pt.'Customer Name'}));

Set(varttfilelink,Task_Tracker_Export_2.Run(JSON(exp_tt_2,JSONFormat.IncludeBinaryData)).filelink);
Launch(varttfilelink);

Now problem is that: Upon creating the Array in the first place, I am max able to work on 2000 rows and the formulas give delegation warnings due to in function.

Now suppose there comes 2500 rows in sharepoint list, then my formula would not work. Please suggest so that this challenge could be overcome.