extract data from Collection

(0) Share

Report

Posted on by marial16

443

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

eka24 20,923 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (0)

Report
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,

Was this reply helpful? Yes No
marial16 443 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
marial16 443 on at

Like (0)

Report

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"

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (1)

Report
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,

Was this reply helpful? Yes No
marial16 443 on at

Like (0)

Report

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

Was this reply helpful? Yes No
marial16 443 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft on at

Like (1)

Report
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,

Was this reply helpful? Yes No
marial16 443 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
marial16 443 on at

Like (0)

Report

Hello,
I need more guidance since creating both collections 'broke' the internal filter i used to get my country value based on the division selected value in the form.
i use as correctly indicated 'currentuserCountry' in the Country dd Items field. And 'Distinct(currentuserDivision)' in the Division dd Items field.
I filtered the Country dd to only get countries corresponding to domestic or international division using Filter(currentuserCountry,division.Id = DivisionDD.Selected.Result) but it doesnt work.
This is how i initially declared my collections on the main screen 'onVisible'
ClearCollect(currentuserDivision,ShowColumns(Divisions,"Title","ID"));
ClearCollect(currentuserCountry,ShowColumns(Countries,"Title","ID","division"));

Was this reply helpful? Yes No