Answered

Collection code - distinct and with - what's wrong with this?

(0) Share

Report

Posted on by sasrsc

678

What's wrong with this code..
I want to get the distinct values of metroId from HotelRFP and then loop through them and get the metroCityName (and eventually other columns) from it and put that into a collection. I'm getting an error on the distinct part of this.

With(
 {_t,Distinct(HotelRFP,metroId)}
,
ClearCollect(colMetrosUsed,
 
 ForAll(
 _t,
 Patch({
 MetroName: LookUp(colMetros,ID=_t.Result,cityName)
 }
 )
 )
 )
)

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@sasrsc

Please consider changing your Formula to the following:

ClearCollect(colMetrosUsed, With({_t : Distinct(HotelRFP,metroId)}, ForAll(_t, {MetroName: LookUp(colMetros, ID=Result,cityName)} ) ) )

Your use of Patch is not needed as you are not patching anything. The ForAll function is a function that returns a table - not a For Loop! So, in this case, you are just simply defining the return record schema as intended.

I hope this is helpful for you.

Was this reply helpful? Yes No
sasrsc 678 on at

Like (0)

Report
because I like to learn... this is my final product (using your suggested change). I have one question... why don't you have to do ID=_t.Result. You did ID=Result. I don't understand why that worked. Yet, _t.Result doesn't work.
ClearCollect( colMetrosUsed, With( { _t: Distinct( HotelRFP, metroId ) }, ForAll( _t, With( { _m: LookUp( colMetros, ID = Result ) }, { metroName: _m.field_1, metroCode: _m.Title } ) ) ) )

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@sasrsc

This is because _t is a table of records. You only want to reference a value, not a table.

Everything is on "context"! Meaning, when you reference a column name for something, PowerApps will reference the "closest" thing to it in context. So, since Result is used, it looks for the closest reference - which is the record of the ForAll table iteration.

Also, you can refine your formula a bit more to:

ClearCollect(colMetrosUsed, With({_t: Distinct(HotelRFP, metroId)}, ForAll(_t, With(LookUp(colMetros, ID = Result), { metroName: field_1, metroCode: Title } ) ) ) )

You'll notice in the second With, there is no variable name provided. This is because With will provide an entire record for the context of the With scope. When it encounters field_1, for example, it will look to the closest reference of that...which is the LookUp record result.

I hope this is clear and helpful.

Was this reply helpful? Yes No
sasrsc 678 on at

Like (1)

Report

As usual what a great explanation. I'd sign up for a class with you anytime 😉

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@sasrsc

Good to know!! That might be coming soon 😉

Was this reply helpful? Yes No