Collection with add new columns from same data file

[lknudson1]

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?

Thanks!

Larry

[Drrickryp]

@lknudson1 

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

[lknudson1]

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