cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dhanushka_Aus
Frequent Visitor

Unable to get specific internal Collection column values from Nested Collection rows

Hi Powerapps team,

I'm having a problem in working in my project which is to involving in
1) Creating a collection using external API extracted data (already done)
2) Display the "Meeting type" and "Appointment Date" on the Powerapps form it self
3) Sending these details to Excel/Sharepoint list (which I will then sent to outlook calendar)

 

I'm in my step No.2, My Initial Collection is "TouchpointDB". This is a Nested Collection in which TouchpointDB->ResponceElement->Value Field (Please refer screenshots).


In my design to display the "Meeting type" and "Appointment Date" on the Powerapps form it self, 1st row of value field is the "Meeting type" and 3rd row of value field is the "Appointment Date".
At this moment there are 3 rows in TouchpointDB (which will increase once live.)

So Ideally i needs to get a table with below manner.

 

 Value (Row1)Value (Row 3)
TouchpointDB.ResponseElement (Row 1)Office Meeting3/1/2020
TouchpointDB.ResponseElement (Row 2)Client Meeting3/2/2020
TouchpointDB.ResponseElement (Row 3)Office Meeting3/3/2020


I tried to extract these information using putting a new formula to the button as below.

ClearCollect(ImportantTouchpointResponce,
Remove(
FirstN(TouchpointDB.ResponseElements),
FirstN(TouchpointDB.ResponseElements)
)
)

But it failed🤔. Highly appreciate if you could help me in this please.

 

THanks
Dhanushka_Aus

2 ACCEPTED SOLUTIONS

Accepted Solutions

For some reason, my response kept getting rejected, so I have attached a document that shows the steps I used to arrive to your result. 

This is how my collections look like: 

ResponseElement2.JPGResponseElement1.JPG

OriginalTable.JPGRequiredTable.JPG

 

Let me know if this helps. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

 

Thanks!
Hardit Bhatia
https://thepoweraddict.com

View solution in original post

ilton
Resolver II
Resolver II

Hi! @Dhanushka_Aus 

 

try:

 

ClearCollect(ResponseElement1, {ElementId: 101, Value: "Office Meeting"}, {ElementId: 102, Value: "O-1"}, {ElementId: 103, Value: "01.04.2020"});
ClearCollect(ResponseElement2, {ElementId: 101, Value: "Client Meeting"}, {ElementId: 102, Value: "O-2"}, {ElementId: 103, Value: "02.04.2020"});
ClearCollect(firstCollection, {TouchPoindDbRow: 1, Field1: "aaa", Field2: "ee", ResponseElement: ResponseElement1}, {TouchPoindDbRow: 2, Field1: "dd", Field2: "ff", ResponseElement: ResponseElement2});
// the code aboe is just to form the initial collection
// and the code bellow is to map the colletion into the one you need
ClearCollect(RequiredCollection, ForAll(firstCollection, {TouchPointDbRow: TouchPoindDbRow, N101: LookUp(ResponseElement, ElementId = 101).Value, N102: LookUp(ResponseElement, ElementId = 102).Value}))

 

Note that I had to map the fields 101 to N101 because fields should not start with numbers

InitialCollection.png

Initial Collection Response element.png

RequiredCollection.png

View solution in original post

8 REPLIES 8
ilton
Resolver II
Resolver II

Hi! @Dhanushka_Aus 

If all you need is to get the first 2 elements and storing then in a list

try:

ClearCollect(ImportantTouchpointResponce, FirstN(TouchpointDB.ResponseElements, 2))

 

Hi @ilton ,

 

Thank you for your response. Actually that is not the case. I need to do some thing like below.

 

original table

1.JPG

 

Required table

 

2.JPG

 

Hope this clears.

 

Appreciate your help.

 

Danushka

For some reason, my response kept getting rejected, so I have attached a document that shows the steps I used to arrive to your result. 

This is how my collections look like: 

ResponseElement2.JPGResponseElement1.JPG

OriginalTable.JPGRequiredTable.JPG

 

Let me know if this helps. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

 

Thanks!
Hardit Bhatia
https://thepoweraddict.com

Were you able to test it out? Let me know if it solved the problem.

Thanks,
Hardit Bhatia
https://thepoweraddict.com
ilton
Resolver II
Resolver II

Hi! @Dhanushka_Aus 

 

try:

 

ClearCollect(ResponseElement1, {ElementId: 101, Value: "Office Meeting"}, {ElementId: 102, Value: "O-1"}, {ElementId: 103, Value: "01.04.2020"});
ClearCollect(ResponseElement2, {ElementId: 101, Value: "Client Meeting"}, {ElementId: 102, Value: "O-2"}, {ElementId: 103, Value: "02.04.2020"});
ClearCollect(firstCollection, {TouchPoindDbRow: 1, Field1: "aaa", Field2: "ee", ResponseElement: ResponseElement1}, {TouchPoindDbRow: 2, Field1: "dd", Field2: "ff", ResponseElement: ResponseElement2});
// the code aboe is just to form the initial collection
// and the code bellow is to map the colletion into the one you need
ClearCollect(RequiredCollection, ForAll(firstCollection, {TouchPointDbRow: TouchPoindDbRow, N101: LookUp(ResponseElement, ElementId = 101).Value, N102: LookUp(ResponseElement, ElementId = 102).Value}))

 

Note that I had to map the fields 101 to N101 because fields should not start with numbers

InitialCollection.png

Initial Collection Response element.png

RequiredCollection.png

Hi @PowerAddict ,

 

Amazing and thank you so much. It worked. This is just so cool. It worked perfectly and gives me the answer. I'm going to work on this. Thanks again and sorry for my delay on this.

 

This will change my projects way of delivery. 😁

 

Thanks

Dhanushka

Hi @ilton ,

 

Thank you so much. I went through both yours and PowerAddict's solution and got a combination of it. My task got completed. It is amazing to see how you guys helping us new members on solving the problems. Hats off to both of you. My project loos promising.

Thanks again and so sorry for my delay to response.

 

Thanks

Dhanushka

Glad I could help! Good luck with the rest of your app! Feel free to reach out in case of any questions!

Thanks,
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,862)