Reading value from another Sharepoint list base on dropdown list value

[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

[NandiniBhagya20]

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 ?

[Ajwou]

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.  

[NandiniBhagya20]

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 

[Ajwou]

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?

[NandiniBhagya20]

Hi @Ajwou ,

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

[Ajwou]

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.

[WarrenBelz]

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

[Ajwou]

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'

[WarrenBelz]

@Ajwou ,

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