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
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)