cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marial16
Helper V
Helper V

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

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
eka24
Super User III
Super User III

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.

v-yutliu-msft
Community Support
Community Support

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.

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.

 

 

 

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

 

 

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.

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

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.

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (42,862)