cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lknudson1
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.

 

lknudson1_1-1633194646189.png

 

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.

 

lknudson1_2-1633195316134.png

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:

 

lknudson1_3-1633196514122.png

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

 

2 REPLIES 2
Drrickryp
Super User
Super User

@lknudson1 

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

lknudson1
Advocate II
Advocate II

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

 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

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