Need help with nested loops

(0) Share

Report

Posted on by Aysan

271

Hi,

I am working on a project in which I need to generate a report. to Excel, in order to keep the SharePoint list size in control, I had to write the timesheet in weekly order, for instance

I need to be able to export the info to csv so it looks like for each row

Sunday	7/2/2023	0
Monday	7/3	8
Tuesday	7/4	8
Wednesday	7/5	8
Thursday	7/6	0
Friday	7/7	0
Saturday	7/9	0

In my power app I am trying to create a json format and send it to flow and create a csv file , the part that I really need help is creating the loops

Clear(ColExportData);
Notify("Exporting CSV..",NotificationType.Information);
ForAll([1,2,3,4,5,6,7],
ForAll('TimeSheet App_TimeEntries',
Collect(ColExportData,
{Name: ThisRecord.Employee.DisplayName,
Role:ThisRecord.Role,
Department:ThisRecord.Employee.Department,
Location:Office365Users.UserProfile(ThisRecord.Employee.Email).Country,
Date:DateAdd(ThisRecord.'Week Start',1,TimeUnit.Days),
Day:Text(DateValue(Index(Split(ThisRecord.Title,"-"),2).Value),"dddd"),
Project:ThisRecord.BillTo.Value,
Hours:Text(ThisRecord.Created,"yyyy-mm-dd"),
ApprovalStatus:ThisRecord.Status.Value
}
)
));
'TimeSheet-ExportV2'.Run(JSON(ColExportData,JSONFormat.IncludeBinaryData))

The highlighted part is where I need help . I tried to duplicate somehow what I have in the first solution ( which was using powerautomate to write the info to excle file - which now failes due to time out)

the formula for "Date":formatDateTime(addDays(split(items('Apply_to_each')?['Title'], '-')?[1], items('Apply_to_each_4')), 'yyyy-MM-dd')

Day:

formatDateTime(formatDateTime(addDays(split(items('Apply_to_each')?['Title'], '-')?[1], items('Apply_to_each_4')), 'yyyy-MM-dd'),'dddd')

Hours:variables('VARhours')?[string(items('Apply_to_each_4'))]

I am wondering if there is any way I can have the same result in power apps

Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Scott_Parker 1,090 on at

Like
a
(0)

Report
Copy link

Link copied!

If you're trying to export a file, I strongly recommend going back to Power Automate. If you're having timeout issues because of a large number of rows being written to Excel, this video shows you have to output many rows in a single action.

This video will also show you how to make use of the Select() action which makes manipulating arrays of data in Power Automate way faster than when using an Apply to each.

Was this reply helpful? Yes No
Verified answer

Aysan 271 on at

Like
a
(0)

Report
Copy link

Link copied!

For someone who might look for a solution, I ended up using several collections. and filtering them and end up having the result I wanted in a final collection:
Clear(obj);
ForAll('TimeSheet App_TimeEntries',
//Collect(ColHours, ShowColumns('TimeSheet App_TimeEntries',"Sun","Mon","Tues","Weds","Thurs","Fri","Sat")));
Collect(obj, {Id:1, Value:Sun ,sid:ID}, {Id:2, Value:Mon,sid:ID}, {Id:3, Value:Tues,sid:ID}, {Id:4, Value:Weds,sid:ID}, {Id:5, Value:Thurs,sid:ID}, {Id:6, Value:Fri,sid:ID}, {Id:7, Value:Sat,sid:ID}));

Clear(Coltimeex);
ForAll('TimeSheet App_TimeEntries',
Collect(Coltimeex,{
Date: ForAll(Sequence(7),Text( DateAdd('Week Start',Value,TimeUnit.Days),DateTimeFormat.ShortDate) ),
Day:ForAll(Sequence(7),Text(DateAdd('Week Start',Value,TimeUnit.Days),"dddd")),
sid:ForAll(Sequence(7),ID)
}));

ClearCollect(COldate,DropColumns(RenameColumns(Ungroup(Coltimeex,"Date"),"Value","Date"),"Day"));
ClearCollect(COldays,DropColumns( RenameColumns( Ungroup(Coltimeex,"Day"),"Value","Day"),"Date"));

ClearCollect(ColUngroup,
ForAll(Sequence(CountRows(COldate)),
{
Date: Index(COldate,Value).Date,
Day:Index(COldays,Value).Day,
Hours:Index(obj,Value).Value,
Sid: Index(obj,Value).sid
}
)
);

Clear(ColExportData);
ForAll([1,2,3,4,5,6,7],
ForAll('TimeSheet App_TimeEntries',
Collect(ColExportData,
{Name: ThisRecord.Employee.DisplayName,
Role:ThisRecord.Role,
Department:ThisRecord.Employee.Department,
Location:Office365Users.UserProfile(ThisRecord.Employee.Email).Country,
Project:ThisRecord.BillTo.Value,
ApprovalStatus:ThisRecord.Status.Value,
ESid:ThisRecord.ID
}
)
));
ClearCollect(Colmerge, ColUngroup,ColExportData);
Clear(Colmerge);
ForAll(ColUngroup,
If(!IsBlank(LookUp(ColExportData,ESid=ColUngroup[@Sid])),
Collect(Colmerge,
{

Date:ColUngroup[@Date],
Day:ColUngroup[@Day],
Hours:ColUngroup[@Hours],
ApprovalStatus:LookUp(ColExportData,ESid=ColUngroup[@Sid]).ApprovalStatus,
Name:LookUp(ColExportData,ESid=ColUngroup[@Sid]).Name,
Role:LookUp(ColExportData,ESid=ColUngroup[@Sid]).Role,
Department:LookUp(ColExportData,ESid=ColUngroup[@Sid]).Department,
Location:LookUp(ColExportData,ESid=ColUngroup[@Sid]).Location,
Project:LookUp(ColExportData,ESid=ColUngroup[@Sid]).Project
}
)));
ClearCollect(ColFinal,
DropColumns(Colmerge,"Sid","ESid"));
In the end, I am sending the collection as a JSON file to a flow and creating a csv table and generating the download link to the app, shoutouts to @RezaDorrani for this fantastic video https://youtu.be/tQCBWMR7T64

Was this reply helpful? Yes No