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

[NewNW]

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.

[v-xida-msft · Accepted solution]

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:

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

App's configuration as below:

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,

[NewNW]

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]

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,

[NewNW]

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]

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,

[NewNW]

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
)
)))

[NewNW]

All good now thank you very much for your help. 

[Shouri7511]

Hello! I am new to PowerApps and I have a similar problem.

What if both the columns are single text type? I have a Sharepoint table with 2 columns, "User" and "Naming", both linked to 2 separate dependant dropdown in PowerApps. The column "User" contain a list of actual user ID but in text format (not a Person type column). When I select a "User" in the first dropdwon I get the filtered list of "Naming" in the second one. All good. But I would like to automatically catch the actual logged in user ID, set it as the first dropdown value and filter the second dropdown based on it.

Basically, I would like the user to only be able to select his/her assigned "Naming".

How should I go about it?

Thanks.