Filter a gallery by using another table with lookup

(0) Share

Report

Posted on by msqmomani92

Hello everyone,

This is the first time I have posted on Power Apps Community, usually, I find my answers but I'm stuck.

I have two SharePoint lists

- MandatoryTraining

ID	CourseTitle	CourseDuration
1	Course1	60
2	Course2	120
3	Course3	130

- EmployeeMandatoryTraining

EmployeeEmailTraining	MandatoryTrainingID
Jone@bla.com	1
jone@bla.com	2
steve@bla.com	1

MandatoryTrainingID is a lookup column.

what I'm trying to achieve is for example when Jone enter the app a gallery will show only the training that he has

Course1	60
Course2	120

I tried the below formula, but I'm receiving an error "Power Apps can't convert this number to a record"

Filter([@MandatoryTrainings], ID in Filter([@EmployeeMandatoryTraining],EmployeeEmailTraining.Value=User().Email).MandatoryTrainingID)

any suggestions?

Thank you in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (2)

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
Hi @msqmomani92 ,

The structure should be like this (noting that MandatoryTrainingID is a Lookup column)

AddColumns( Filter( EmployeeMandatoryTraining, EmployeeEmailTraining = User().Email ), CourseTitle, LookUp( MandatoryTraining, ID = MandatoryTrainingID.Value ).CourseTitle, CourseDuration, LookUp( MandatoryTraining, ID = MandatoryTrainingID.Value ).CourseDuration )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
msqmomani92 13 on at

Like (0)

Report

@WarrenBelz Thank you so much for the help,
The formula seems to be working, however, the gallery shows records but with no info, Thisitem.CourseTitle doesn't work.
also what if I want to retrieve all info, not only Course title and Course Duration, is that possible

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@msqmomani92 ,

The code is based on what you posted and should work. You would be better making the MandatoryTrainingID field a number column rather than a Lookup, however the fact it does not error means it should work. You might check your data.

Was this reply helpful? Yes No
Verified answer

msqmomani92 13 on at

Like (0)

Report

This one should do the trick:
Filter(MandatoryTrainings,
ID in ForAll(Filter(EmployeeMandatoryTraining, EmployeeEmailTraining.Value = User().Email).MandatoryTrainingID,MandatoryTrainingID.Value)
)

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
@msqmomani92 ,

The code I provided should have worked based on what you posted. However if you want a bit of Delegation management here (using the newest records from MandatoryTrainings if the list is over your Data Row Limit or to simply mask the warning if under this)

With( { _Employee: Filter( EmployeeMandatoryTraining, EmployeeEmailTraining.Value = User().Email ), _Manditory: Sort( MandatoryTrainings, ID, SortOrder.Descenting ) }, Filter( _Mandatory, ID in _Employee.MandatoryTrainingID.Value ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No