Unanswered

ClearCollect to pull data from sql filtered from a collection

(0) Share

Report

Posted on by MattSmall

I hope you can help as I am stuck. I have an SQL database that I want to search and return rows, based on the selection of a separate collection. The difficulty I cannot figure out is that the collection has two columns, one of which shows the column in the sql database and the second is the unique number in that column that can pull the specific rows.

SelectedTrackingCodes (The collection)

TrackingCodeType	TrackingCodeNb
GP	123456
CDT	654321
IT	123789

AllData (On Prem SQL)

Gp	Cdt	It
123456
	654321
		123789

I have tried this

ClearCollect(SelectedData,LookUp(SelectedTrackingCodes, TrackingCodeType = "GP", Filter(AllData,Gp=TrackingCodeNb))); and multiple other versions with filter, but cannot figure out how to pull the row or rows for Gp 123456 using the collection as a reference for both the column lookup. Then it should pull Cdt 654321 and its row or rows of data, and so on until all the items in the collection have been pulled into the new collection.

I have tried to look for the answer myself through the community, but nothing seems to fit the requirement I have to select the column first.

Any help would be greatly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

SpongYe 5,603 Super User 2025 Season 2 on at

Like (1)

Report
Hi @MattSmall

You can try:
Clear(SelectedData); // Clear the SelectedData collection before populating it ForAll( SelectedTrackingCodes, If( TrackingCodeType = "GP", Collect(SelectedData, Filter(AllData, Gp = TrackingCodeNb)), TrackingCodeType = "CDT", Collect(SelectedData, Filter(AllData, CDT = TrackingCodeNb)), TrackingCodeType = "IT", Collect(SelectedData, Filter(AllData, IT = TrackingCodeNb)) ) );
If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

Was this reply helpful? Yes No
MattSmall 16 on at

Like (0)

Report

Thanks @SpongYe for responding so promptly.
Your code threw up some errors, but has giving me options which I am exploring. Will let you know if I am successful.

Was this reply helpful? Yes No
MattSmall 16 on at

Like (1)

Report

@SpongYe thanks this worked perfectly you are a star.

Was this reply helpful? Yes No