cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dBrand
Helper III
Helper III

Using a WITH inside COLLECT/ADDCOLUMNS

I am looking to speed up loading a collection. I need to do lookups as it fills the collection, and all the data is looked up from the same record. See code example below. Thanks!

 

ClearCollect(coll_gal_settled,
    AddColumns(
        Filter('[dbo].[JobLog]', FinalizedDate=Blank(), SettlementDate<>Blank()),
         "MgrLastName", LookUp('[dbo].[view_JobLog_Links]', vJobLog_Pkey=PKey, ManagerLName),
         "MgrFullName", LookUp('[dbo].[view_JobLog_Links]', vJobLog_Pkey=PKey, ManagerFName & " " & ManagerLName),

         "DocMgrLastName", LookUp('[dbo].[view_JobLog_Links]', vJobLog_Pkey=PKey, DocMgrLName),
         "DocMgrFullName", LookUp('[dbo].[view_JobLog_Links]', vJobLog_Pkey=PKey, DocMgrFName & " " & ManagerLName),

.......

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @dBrand ,

Your question was on the With() which is a "hidden" Delegation limitation, so the result needs to be under 2,000 records. Unless you can "pre-filter "inside the statement to return less than that, then you cannot use it in your exercise. I have tried using it "inside" at the start of AddColumns before, but it is not valid code in that function for some reason (it will not recognise the With value)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

Hi @dBrand ,

Assuming your second table '[dbo].[view_JobLog_Links]' has less than 2,000 records

With(
   {
      wLinks:
      AddColumns(
         '[dbo].[view_JobLog_Links]',
         "Manager",
         ManagerFName & " " & ManagerLName,
         "DocManager",
         DocMgrFName & " " & DocMgrLName
      )		 
   },
   ClearCollect(
      coll_gal_settled,
      AddColumns(
         Filter(
            '[dbo].[JobLog]', 
            FinalizedDate=Blank() &&
            SettlementDate<>Blank()
         ),
         "MgrLastName", 
         LookUp(
	     wLinks, 
             vJobLog_Pkey=PKey
         ).ManagerLName,
         "MgrFullName",  
         LookUp(
            wLinks, 
            vJobLog_Pkey=PKey
         ).Manager,
         "DocMgrLastName", 
         LookUp(
            wLinks, 
            vJobLog_Pkey=PKey
         ).DocMgrLName,
         "DocMgrFullName", 
         LookUp(
            wLinks,
            vJobLog_Pkey=PKey
         ).DocManager
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks for the suggestion. Unfortunately, the second table will have more than 2,000 records. Any other way to do this in an efficient (faster) way?

Hi @dBrand ,

Your question was on the With() which is a "hidden" Delegation limitation, so the result needs to be under 2,000 records. Unless you can "pre-filter "inside the statement to return less than that, then you cannot use it in your exercise. I have tried using it "inside" at the start of AddColumns before, but it is not valid code in that function for some reason (it will not recognise the With value)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Thanks. If there is a way without WITH, I am open for that too. I thought it was needed to be able to do this. Otherwise I will look to see if there is a way to pre-filter the data.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,670)