cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mindea
Helper II
Helper II

AddColumns from collection to new collection

I can't get the second collection to pull data from the first collection.

 

ClearCollect(colCustAndAM, AddColumns(Customers,"AMName",LookUp(AccountManagers,ID = AccountManagerID, FullName)));

ClearCollect(colOpps,AddColumns(Opportunities,"AccountManagerName",LookUp(colCustAndAM,ID=AccountID,AMName)));

 

The first collection works great. It shows AMName column completely filled in. And if I create a label with LookUp(colCustAndAM,ID=982,AMName) I correctly see "Joe Blow"

Problem: AccountManagerName column in the colOpps collection is blank. I can see colOpps with AccountID of 982, but nothing in the AccountManagerName column fills in.

Any ideas what I'm doing wrong?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Mindea ,

I see that in the original post now, so we are only dealing with the second collection - a bit of overkill below, but see what it gets

ClearCollect(
   colOpps,
   AddColumns(
      Opportunities,
      "AccountManagerName",
      LookUp(
         colCustAndAM As aLU,
         aLU.ID = AccountID,
      ).AMName
   )
);

 

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

 

View solution in original post

9 REPLIES 9
WarrenBelz
Super User
Super User

Hi @Mindea ,

I cannot see your data, but this code

ClearCollect(
   colCustAndAM, 
   AddColumns(
      Customers,
      "AMName",
      LookUp(
         AccountManagers,
         ID = AccountManagerID, 
         FullName
      )
   )
);
ClearCollect(
   colOpps,
   AddColumns(
      Opportunities,
      "AccountManagerName",
      LookUp(
         colCustAndAM,
         ID = AccountID,
         AMName
      )
   )
);

assumes that

  • You are adding a column to the Customers list called AMName which is the FullName field in a record from the AccountManagers list where the AccountManagerID matches the ID in Customers.
  • You are then adding a field to the Opportunities list called AccountManagerName with is the AMName field you have added above where the ID in Opportunities matches the AccountID in the collection(ultimately Customers)

Is there anything incorrect there ?

 

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

See my correction below

  • You are adding a column to the Customers list called AMName which is the FullName field in a record from the AccountManagers list where the AccountManagerID matches the ID in Customers. the AccountManagerID in Customers matches ID in AccountManagers
  • You are then adding a field to the Opportunities list called AccountManagerName with is the AMName field you have added above where the ID in Opportunities matches the AccountID in the collection(ultimately Customers) the AccountID in Opportunities matches the ID in the collection (ultimately Customers)

Hi @Mindea ,

Yes that is correct - did my head in a bit typing it all out. How many records are in Customers and Opportunities (and what is your Delegation limit) as none of this is Delegable.

Hi, @WarrenBelz 

There are 1590 Opportunities, and 1125 Customers. Data row limit is set to 2000.

Thank you very much for your continuing efforts.

Hi @Mindea ,

Basic debugging question - when you look at colCustAndAM, do you see the AMName field populated with the correct data ? And (I know obvious question) do all the AccountID fields in Opportunities reflect the ID in the relevant field in Customer ?

Hi, @WarrenBelz 

Regarding your questions:

  1. when you look at colCustAndAM, do you see the AMName field populated with the correct data? Yes
  2. do all the AccountID fields in Opportunities reflect the ID in the relevant field in Customer ? Yes

Hi @Mindea ,

I see that in the original post now, so we are only dealing with the second collection - a bit of overkill below, but see what it gets

ClearCollect(
   colOpps,
   AddColumns(
      Opportunities,
      "AccountManagerName",
      LookUp(
         colCustAndAM As aLU,
         aLU.ID = AccountID,
      ).AMName
   )
);

 

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

 

Hi, @WarrenBelz 

Got it! Your post led me to the realization that my formula was wrong. AName. 

I had this . . . LookUp(colCustAndAM,ID=AccountID,AMName)

instead of this

. . . LookUp(colCustAndAM,ID=AccountID).AMName

 

Thank you soooo much!

Mindea.

(that sound you hear in the distance is me kicking myself. It will go away in an hour or so!)

 

 

 

@WarrenBelz 

Additionally, it wouldn't work without the inclusion of ". . . As aLU, aLU.ID . . ." so thanks for that, too.

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.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

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