You’re offline. This is a read only version of the page.
I have encountered several occasions inside a PowerApp where Lookup (even First(Filter())) does not work as intended.
So I created a very simple App to demonstrate.
3 Collections are created, and displayed via Gallery. The original App is way more complex, but this explains the basic issue.
The First Collection is the Index Collection, that consist of a Field used as ID for Lookup and an Index field.
Share
ReportClearCollect(
IndexCollection,
{
Title: "Title1",
Index: "1"
},
{
Title: "Title2",
Index: "2"
},
{
Title: "Title3",
Index: "3"
}
)
The second Collection represents the Data that needs the Index reference:
ClearCollect(
MyData,
{
Title: "Title1",
Text: "Additional Column"
},
{
Title: "Title2",
Text: "Additional Column2"
},
{
Title: "Title3",
Text: "Additional Column3"
}
)
The Idea now is to do a ForAll on the Gallery, displaying the MyData (again, the real life scenario is a bit more complex) and creating a new Collection with Lookup information from the the Index Collection
ForAll(
Gallery2.AllItems,
Collect(
myResults,
{
Title: ThisRecord.Title,
Index: LookUp(
IndexCollection,
Title = ThisRecord.Title
).Index
}
);
);
This now creates a new Collection "myResults", but it has the Value "1" for every single Index.
If instead of doing it inside of the ForAll, I use the same Code (without the This.Record Part), it works as intended.
LookUp(
IndexCollection,
Title = "Title1"
).Index
And just out of curiosity I added another Collection inside the ForAll, to Capture Each Value
Collect(
StatusMessage,
"Title: "& ThisRecord.Title & " Value for Lookup: " & LookUp(
IndexCollection,
Title = ThisRecord.Title
).Index
);
The Result is the same:
Hi @RandyHayes,
I am trying to use the same approach for the solution you provided above.
I am attempting to use a Lookup within a forAll in order to match the date in my Principle & Interest collection to the date in my Paydown table and have the Amounts in my Paydown table taken away from the Principle amounts in my Principle & Interest collection.
All responses (
Answers (
ClearCollect(
MyPrincipleAndInterestTable,
ForAll(Sequence(DateDiff(varStrtDate,varEndDate)) As Num,
Collect(
myPrincipleAndInterest,
{
ItemNumber: Num,
ItemDay: DateAdd(varStrtDate, Num.Value ,Days),
Interest: Principle_1 * IntValue_1,
Principle: Principle_1.Text
}
))
);
ClearCollect(
MyPrincipleAndInterestTableWithPaydowns,
ForAll(
MyPrincipleAndInterestTable As _items,
{
ItemNumber: _items.Value.ItemNumber,
ItemDay: _items.Value.ItemDay,
Interest:_items.Value.Interest,
Principle: LookUp(PaydownGallery_1.AllItems, PaydownDate = _items.Value.ItemDay, Principle - Amount)
}
));
At the moment I'm getting this error on the second collection - show below (These types cant be compared Datetime/Table)
Yup, As is a much needed operator as you have to realize that ThisRecord will refer to the function for which it is contained. In your case, it was the LookUp - not the ThisRecord that you wanted!
As for the formula you have now, yes, you can use that method, but it is a waste and a performance hog to use the ForAll as a For/Loop.
In your case, you are iterating through every item and creating a record and taking the expensive action of collecting it row by row into the collection. And your ForAll results are dumped into nowhere.
With the formula I provided, your ForAll creates a Table of records and then assigns them all to the collection in one shot.
Perfect !
I didn't know about the "As" inside the ForAll.
Oh, and by the way it still works with the the ForAll as kind of a pseudo Loop (ignoring the resulting table 😁) and the Collect inside, with that little addition "As _items".
ForAll(
Gallery2.AllItems As _items,
Collect(
myResults,
{
Title: _items.Title,
Index: LookUp(
IndexCollection,
Title = _items.Title
).Index
}
);
);
This is because ThisRecord will then refer to the ThisRecord of the LookUp.
Please consider changing your Formula to the following:
Collect(
myResults,
ForAll(
Gallery2.AllItems As _items,
{
Title: _items.Title,
Index: LookUp(
IndexCollection,
Title = _items.Title,
Index
)
}
)
);Note: ForAll returns a table, it's not a For/Loop. Simply assign the results to the myResults you are trying to collect.
I hope this is helpful for you.
#1
WarrenBelz
146,731
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,075
Most Valuable Professional
