Showing results for 
Search instead for 
Did you mean: 
Advocate II
Advocate II

Collection with add new columns from same data file

Here is gallery end result of what I currently have done in a Power App program.  I like it.




To obtain this result, I first have to get Collection of all records in a job task file.  Each records includes Job#, Task (type of work), and various information related to the task.  The collection is pulled from a SQL table with the following structure type.



The first issue was to get all the more than 2000 limit of relevant records into one collection.  I get through that with a series of sequential collections and merges to pull all records into one collection (conTaskList).


Second issue was to produce a  collection (conJobList) for the gallery where one Job gallery record would include multiple columns from the  conTaskList collection.  I am using a long AddColumns function which adds columns doing a Lookup based on Job and Task # filter of conTaskList for each Task's data (Description, Sub assigned,  Start & Finish dates, etc.).  Sample portion below:



It all works fine, but it is sloww, and will get slowwwwwer as more jobs are entered.  I probably need to work out an archive type of function to keep a manageable maximum collection record size, but even that will result in poor performance.


Maybe if I skipped the intermediary conTaskList collection and did lookups from the SQL table direct?


I have wondered if there is a way to create a SQL View that I could create and just pull that into a collection instead.  I'm sure that would be faster, and then I probably wouldn't have to worry about collection limit for a long time.


Anyway, any suggestions?




Super User
Super User


Creating a sql view would definitely be the way to keep it efficient. Create your query there.

Advocate II
Advocate II

Any direction on where I might go for guidance in creating such a view?


Helpful resources

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (3,072)