AddColumns from collection to new collection

[Mindea]

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?

[WarrenBelz]

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

[Mindea]

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)

[WarrenBelz]

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.

[Mindea]

Hi, @WarrenBelz 

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

Thank you very much for your continuing efforts.

[WarrenBelz]

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 ?

[Mindea]

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

[WarrenBelz · Accepted solution]

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

[Mindea]

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

[Mindea]

@WarrenBelz 

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