Reading value from another Sharepoint list base on dropdown list value

(0) Share

Report

Posted on by Ajwou

Hello,

Not sure if this is question for PowerApp or SharePoint lists.

I have an app, that uses a SharePoint list (Main List) with the following columns:

where Type of Task is 'Choice' column - populated in PowerApp as Dropdown.

Then I have another SharePoint list with columns below with pre-defined values:

So each task type has given assignee ie.

Washing Judy

Cleaning Peter

etc.

What I am trying to do in the App/Main List is when certain 'Type of Tasks' is selected

, the app would also record the Assignee from the other list based on the Task Type.

I have tried playing with Lookup value in the lists and also lookup within PowerApps but cannot get it working.

Please can anyone help.

Kind Regards,

Ivo

Categories:

Building Power Apps

I have the same question (0)

All responses (17)

Answers (1)

NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like (0)

Report

Hi @Ajwou ,
In which column (of the main list) are you trying to save Assignee's value based of task selected and what is the column type ?

Was this reply helpful? Yes No
Ajwou on at

Like (0)

Report

Hi @NandiniBhagya20 ,

I was planning on having another column in the Main List. I deleted the column when testing various options. Ideally I would like to select the task type in the app, and then based on task type in the 'reference' list I would call the assignee in the main list. Ideally as a person (from the organisation) so later email for example could be sent to that particular person.

Was this reply helpful? Yes No
NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like (1)

Report

Hi @Ajwou ,
You can try using something like below -
1. Add a person type column to your master list named - "Assignee"
2. In your form control add this Assignee column and use the below formula in Default Selected Items property -
With({varName: LookUp(ChildList, 'Task Type'.Value = TypeOfTaskDataCard.Selected.Value).'Assignee Name' }, {
Claims: "i:0#.f|membership|" & Lower(varName.Email),
Email: varName.Email,
DisplayName: varName.DisplayName,
Picture: "",
Department: "",
JobTitle: ""
})

Assuming 'Task Type' is choice column in child list which has assignees

Was this reply helpful? Yes No
Ajwou on at

Like (0)

Report

Hi @NandiniBhagya20 ,
Thank you very much for looking into this.
The 'Task Type' in the child list is a 'single line of text' type. Simply one column with Task Types, another with Names and one more with Emails (in the Child list).

I am currently getting these errors when using the formula above (deleted .Value on the 'Task Type' column:
Please would you have any further suggestions?

Was this reply helpful? Yes No
NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like (0)

Report

Hi @Ajwou ,
Assignee Name is which type of column in your SP list (single line of text or person & group - single/multiselect) ?

Was this reply helpful? Yes No
Ajwou on at

Like (0)

Report

Hi @NandiniBhagya20

In the main list, Assignee is there as Person - Single select.
In the child list, Assignee Name is a single line of text.

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report
Hi @Ajwou ,

Firstly @NandiniBhagya20 is on the right track here, but you have caused yourself far more grief than necessary with your data structure. I personally would simply have both Assignee fields as text (I do not use Person columns at all - why bother if you are only storing the name and email) or at the least have the one in your second list as a Person column so you can get the email from there, but with what you have, you need to get the email address for your Person column in the main list, so the Default would be something like this

With( { wName: LookUp( SecondListName, TaskType = TypeOfTaskDropdownName.Selected.Value ).'Assignee Name' }, With( { wEmail: First( Office365Users.SearchUser( { searchTerm: wName, top: 999 } ) ).Mail }, { Assignee: { Claims: "i:0#.f|membership|" & Lower(wEmail), Email: wEmail, DisplayName: wName, Picture: "", Department: "", JobTitle: "" } } ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Ajwou on at

Like (0)

Report

Hi @WarrenBelz ,

I wasn't sure about the right data structure here. The Assignee field doesn't even have to be shown in the app. The aim is to log the details provided from the app, and then based on the selected/recorded 'task' add an assignee from a predefined list.
I have tried the code above and getting the following:
Not sure what I am missing here.
I currently have thh main list Assignee as 'person' type and secondary list as 'text'

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@Ajwou ,

Please see correction - dangers of free-typing code (was missing field name)

Was this reply helpful? Yes No
Ajwou on at

Like (0)

Report

@WarrenBelz
Yes, that fixed the issues with the code. I set is as 'Default' parameter for the Assignee ComboBox field, however when I fill in the TypeOfTask it doesn't generate any value - in the ComboBox or Parent List . I have both lists connected to the app.

Was this reply helpful? Yes No