cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

extract data from Collection

Hi,

 

i have added all my SP list data in a collection. I want to display them in a form (dropdown fields).

my list has 3 lookup columns. User(person or group), Country, Division.

In my form based on the connected user i want to prefill the dropdown fields with the division and country.

How can i extract the data from the returned Record in my collection?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: extract data from Collection

Hi @marial16 ,

 currentusercountry,currentuserDivision are two colllections that I created.

Their data comes from current user's country and division.

Please notice this part:

Filter(PMs,Users.Email=User().Email)

The formulas about creating collections are behavior formulas, you need to set them in behavior property, like OnVisible, OnSelect, OnChange, ect.

The drop down's Items need a formula that returns a table.

The steps should be like:

1)set the screen's OnVisible:
 

Clear(currentusercountry);
ForAll(Filter(PMs,Users.Email=User().Email),
       Collect(currentusercountry,Country.Value)
       );
Clear(currentuserDivision);
ForAll(Filter(PMs,Users.Email=User().Email),
       Collect(currentusercountry,Division.Value)
       )

set drop down's Items for country:

currentusercountry

 set drop down's Items for division:

currentuserDivision

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Highlighted
Community Champion
Community Champion

Re: extract data from Collection

Can you show the Dropdown. Usually to show in a TextBox use: Dropdown.Selected.Value

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Highlighted
Community Support
Community Support

Re: extract data from Collection

Hi @marial16 ,

Do you want to get current user's country and department?

If so, you need to use office365 users connector.

Firstly, please notice that Location data type is read-only in powerapps.

I suggest you  use these fields: User(person or group), Country(lookup field), Division(lookup field)

1)You could use this formula to get current user's country:

First(Office365Users.SearchUserV2({searchTerm:User().Email}).value).Country

You could use this formula to get current user's department:

First(Office365Users.SearchUserV2({searchTerm:User().Email}).value).Department

 2)To autofill drop down, you could set country drop down's Default:

First(Office365Users.SearchUserV2({searchTerm:User().Email}).value).Country

 To autofill drop down, you could set department drop down's Default:

First(Office365Users.SearchUserV2({searchTerm:User().Email}).value).Department

3)The person drop down will be a little complex.

Firstly, you need to use combo box for this data type.

set the combo box's Items:

Choices(listname.personfieldname)

set the combo box's DefaultSelectedItems:

{  
        '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",  
        Claims: "i:0#.f|membership|" & Lower(User().Email),  
        Department: "",  
        DisplayName: User().FullName,  
        Email: User().Email,  
        JobTitle: ".",  
        Picture: "."  
    }  

 

 

Do not forget to connect with office365 User connector.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: extract data from Collection

hello,

 

the country value and division value is taken by the SP list. Not the Office365.

A user for example can have two entries with two different countries.

So based on the User column (User dd in the form) i want to bring the prefilled country/ies and division from the List to the relevant dropdowns in the form.

 

 

 

Highlighted
Helper IV
Helper IV

Re: extract data from Collection

What i am practically trying to do is extract the value from the nested table of the PMs collection and display it in a dropdown field in my Form.

I have tried to use this formula in the Items of the dropdown:  First(First(PMs).Country).Value but i get an error "Expected Table Value"

 

PMs.png

 

 

Highlighted
Community Support
Community Support

Re: extract data from Collection

Hi @marial16 ,

Do you want to display current user's countries in a drop down's Items?
Is country field and Division lookup data type?

Then you need to get value from some nested table.

If so, you could set the drop down for country field like this:

Clear(currentusercountry);
ForAll(Filter(PMs,Users.Email=User().Email),
       Collect(currentusercountry,Country.Value)
       )

you could set the drop down for Division field like this:

 

Clear(currentuserDivision);
ForAll(Filter(PMs,Users.Email=User().Email),
       Collect(currentusercountry,Division.Value)
       )

 

 

 

Best regards,

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: extract data from Collection

Yes, country field and Division are lookup data types. Is currentuserDivision & currentuserCountry another collections?

Highlighted
Helper IV
Helper IV

Re: extract data from Collection

I have tried this on the Items property of my Datacard dropdown and i keep getting an error of type: 'Behavior function is a non behavior property'

 

Are 'currentuserCountry' & 'currentusedDivision'  collections getting their values from my Lists of Countries and Divisions? (As the Country & Division columns are  lookup fields). 

 

I am not sure i understand this correct.

Highlighted
Community Support
Community Support

Re: extract data from Collection

Hi @marial16 ,

 currentusercountry,currentuserDivision are two colllections that I created.

Their data comes from current user's country and division.

Please notice this part:

Filter(PMs,Users.Email=User().Email)

The formulas about creating collections are behavior formulas, you need to set them in behavior property, like OnVisible, OnSelect, OnChange, ect.

The drop down's Items need a formula that returns a table.

The steps should be like:

1)set the screen's OnVisible:
 

Clear(currentusercountry);
ForAll(Filter(PMs,Users.Email=User().Email),
       Collect(currentusercountry,Country.Value)
       );
Clear(currentuserDivision);
ForAll(Filter(PMs,Users.Email=User().Email),
       Collect(currentusercountry,Division.Value)
       )

set drop down's Items for country:

currentusercountry

 set drop down's Items for division:

currentuserDivision

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper IV
Helper IV

Re: extract data from Collection

Correct! I had tried this formula on the 'on visible' of my screeen, but it didn't work.

 

So i had to use a button on my main screen and i got my results, setting the dd values as you described

 

Thank you!

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,319)