cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NewNW
Helper II
Helper II

Adding drop down items based on the login user Items in SharePoint

Hi All,

I have a Sharepoint online list as follows

 

User     WorkType    Sites

A          Full Time       x,y

B          Full Time       y,z,k

C         Contract        x,y,k

D         Contract        x

 

On my power app I have a drop down, I want to fill  the drop down based on the logged in user's Sites.

eg- if the log in user is C then the dropdown called Site fill like below

Site -> X

            Y

            K

Can someone please help me to do this, find the SharePoint record relevant to the logged in user and get the site and separate the single string and display in drop down. User must be able to select only one option, not a multiple selctions.

 

Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @NewNW ,

Could you please share a bit more about the User column and the Sites column in your SP list? Are they both Single text type columns?

Is the User column a Person column in your SP List?

 

I assume that the User column is a Person column, and the Sites column is a Single text type column in your SP list, is true?

I have made a test on my side, please take a try with the following workaround:

The data structure of my SP list as below:5.JPG

Note: The User column is a Person type column, and the Sites column is a Single text type column.

 

App's configuration as below:6.JPG

 

7.JPG

Set the Items property of the Site Dropdown box to following:

Split(LookUp('20190503_case5', User.Email=User().Email, Sites), ",")

On your side, you should type:

Split(LookUp('Your SP List', User.Email = User().Email, Sites), ",")

Note: If you use ComboBox control to fill the Site options within your app, above formula may not fill the values within the Site ComboBox, please take a try with the following workaround to fix this issue.

 

Above formula may cause a Delegation warning issue, in order to get rid of this issue, please take a try with the following workaround:

Set the OnStart property of the App control or OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, 'Your SP List')

Set the Items property of the Site Dropdown box to following:

Split(LookUp(RecordsCollection, User.Email = User().Email, Sites), ",")

More details about the Split function, please check the following article:

Split function

 

Best regards,

Community Support Team _ Kris Dai
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

7 REPLIES 7
v-xida-msft
Community Support
Community Support

Hi @NewNW ,

Could you please share a bit more about the User column and the Sites column in your SP list? Are they both Single text type columns?

Is the User column a Person column in your SP List?

 

I assume that the User column is a Person column, and the Sites column is a Single text type column in your SP list, is true?

I have made a test on my side, please take a try with the following workaround:

The data structure of my SP list as below:5.JPG

Note: The User column is a Person type column, and the Sites column is a Single text type column.

 

App's configuration as below:6.JPG

 

7.JPG

Set the Items property of the Site Dropdown box to following:

Split(LookUp('20190503_case5', User.Email=User().Email, Sites), ",")

On your side, you should type:

Split(LookUp('Your SP List', User.Email = User().Email, Sites), ",")

Note: If you use ComboBox control to fill the Site options within your app, above formula may not fill the values within the Site ComboBox, please take a try with the following workaround to fix this issue.

 

Above formula may cause a Delegation warning issue, in order to get rid of this issue, please take a try with the following workaround:

Set the OnStart property of the App control or OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, 'Your SP List')

Set the Items property of the Site Dropdown box to following:

Split(LookUp(RecordsCollection, User.Email = User().Email, Sites), ",")

More details about the Split function, please check the following article:

Split function

 

Best regards,

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

Thanks,

this is perfectly working, only thin , can you tell me how to refresh / reload the data table based on the drop down selct change.

Thanks.

 

v-xida-msft
Community Support
Community Support

Hi @NewNW ,

Yeah, I think you are using ComboBox control to fill the Sites values, is it true?

 

If you only want the user to select one option once time within the ComboBox, please set the SelectMultiple proeprty of the ComboBox to following:

false

Please take a try with above solution, check if the issue is solved.

 

Best regards,

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

Thanks mate, all good.

Could you yplease tell me how can I refresh/ reload the Data tabel on dropdown select change.

Thanks. 

v-xida-msft
Community Support
Community Support

Hi @NewNW ,

Have you solved your problem?

 

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

In addition, if you want to refresh the Data Table within your app when the selected value within your ComboBox has been changed, I think the OnChange proeprty of the ComboBox could achieve your needs.

Set the OnChange property of the ComboBox to following:

Refresh('Data Source')   /* <-- 'Data Source' represents the data source your Data Table connencts to */

hope above solution helps.

 

Best regards,

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

Thanks for your reply, yes I can refresh the data source, but see my dtdt table it have more than one data source. 

If(dpdSites.Selected.Value ="Higton",(Filter('CarDetails-H',
Not(
Title in Filter('BookingDetails-H',( ((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
<= (DateValue(Text(dtpPickupDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) >=
(DateValue(Text(dtpDropOffDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0))) ||
((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
>= (DateValue(Text(dtpPickupDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0))) ||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
>= (DateValue(Text(dtpPickupDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0)))||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
<= (DateValue(Text(dtpDropOffDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) >=
(DateValue(Text(dtpPickupDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)))
)).Title
)
)),(Filter('CarDetails-C',
Not(
Title in Filter('BookingDetails-C',( ((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
<= (DateValue(Text(dtpPickupDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) >=
(DateValue(Text(dtpDropOffDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0))) ||
((DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(PickupTime), 0, 0))
>= (DateValue(Text(dtpPickupDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0))) ||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
>= (DateValue(Text(dtpPickupDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)) &&
(DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(DropoffTime, 0,0)) <=
(DateValue(Text(dtpDropOffDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0)))||
((DateValue(Text(DropoffDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(DropoffTime), 0, 0))
<= (DateValue(Text(dtpDropOffDate.SelectedDate,"[$-en-US]mm/dd/yyyy")) + Time(Value(dpdDropoffTime.Selected.Value), 0, 0)) &&
(DateValue(Text(PickupDate, "[$-en-US]mm/dd/yyyy")) + Time(PickupTime, 0,0)) >=
(DateValue(Text(dtpPickupDate.SelectedDate, "[$-en-US]mm/dd/yyyy")) + Time(Value(dpdPickupTime.Selected.Value), 0, 0)))
)).Title
)
)))

All good now thank you very much for your help. 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,177)