Split lookup inside a collection

(0) Share

Report

Posted on by Michael22902290

ForAll(List2,

Collect(List3,

Table(
//Last Name
{Last_Name:LastName,

//First Name
First_Name:FirstName,

//Payroll
Payroll:Value(Payroll),

//Shift
Shift:Shift,

//Additions
//Skills
Shift_Concat: LookUp(Name_Skills,Payroll_Skill=Payroll&&ORG_ID=vargroup,Skill_Concat),

Shift_Skill:LookUp(Name_Skills,Payroll_Skill=Payroll && ORG_ID=vargroup,Skill),
Shift_Skill_Red: LookUp(Name_Skills,Payroll_Skill=Payroll&&ORG_ID=vargroup,Name_Skill_Red),
Shift_Skill_Green: LookUp(Name_Skills,Payroll_Skill=Payroll&&ORG_ID=vargroup,Name_Skill_Green),
Shift_Skill_Blue: LookUp(Name_Skills,Payroll_Skill=Payroll&&ORG_ID=vargroup,Name_Skill_Blue),

//Daypart
DayPart:If(Value(Left(Shift,2))>=21,"N",
If(Value(Left(Shift,2))>=14,"E",
If(Value(Left(Shift,2))>=6,"D",
If(Value(Left(Shift,2))<6,"N")))),

Shift_Date:DatePicker2.SelectedDate,

ORG_ID:vargroup,

Full_Name:FirstName&" "&LastName,

ID:Blank()

})));

Hey.
I have the above formula it collecting some lookups into a collection.

I found that by collecting 4 lookups (in green) into the collection it run a little bit slow
So, I created a concatenation (separated by a comma Skill,Name_Skill_Red,Name_Skill_Green,Name_Skill_Blue ) in the DataSource and I'm now able to look up these values one time using the lookup coloured red.

how can i split these results into their respective columns in the collection. I need to keep the collection column names in light blue as they exactly match the DataSource and are used later for a Patch only where i put the first argument as the DataSource and then the entire collection as the update.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

LaurensM 12,516 Moderator on at

Like (1)

Report
Hi @Michael22902290,

Instead of splitting a concat field, you could use the With function to temporarily store the LookUp output in a scope variable. Afterwards you can use this variable to map the output to the respective columns without the need of repeating the LookUp function.

In addition to temp storing the LookUp function as a With variable, I have adjusted your daypart if structure slightly to reduce the amount of code and used ForAll as an input parameter that returns a table instead - improving performance:

Collect( List3, //ForAll will return a table of records ForAll( List2, With( { //Temp store LookUp output wNameSkill: LookUp(Name_Skills, Payroll_Skill=Payroll && ORG_ID=vargroup), //Temp store shift value wValShift: Value(Left(Shift,2)) }, { Last_Name:LastName, First_Name:FirstName, Payroll:Value(Payroll), Shift:Shift, //Additions //Skills now reference the With scope variable Shift_Concat: wNameSkill.Skill_Concat, Shift_Skill: wNameSkill.Skill, Shift_Skill_Red: wNameSkill.Name_Skill_Red, Shift_Skill_Green: wNameSkill.Name_Skill_Green, Shift_Skill_Blue: wNameSkill.Name_Skill_Blue, //Daypart //If function adjusted to avoid Value(Left()) repetitions & nested if statements DayPart: If( wValShift >= 21, "N", wValShift >= 14, "E", wValShift >= 6, "D", "N" ), Shift_Date:DatePicker2.SelectedDate, ORG_ID:vargroup, Full_Name:FirstName&" "&LastName, ID:Blank() } ) ) );

In regards to the If statement adjustment: I have written a blog post on conditional statement tips & tricks.

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!

Was this reply helpful? Yes No
Michael22902290 17 on at

Like (1)

Report

Not only did this work perfectly, it improved the run time of the collection of around 100 items from 6 minutes to 10 seconds.

Thanks 🙂

Was this reply helpful? Yes No