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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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