2 datasources default value code

[Anonymous]

Hi,

I've been trying to get a part of my code to work for days for my Power App but I can't quite get it down. I will provide the vision I have in mind along with the attempts I've tried so you can get a more clear idea as to what my objective is:

I have 2 sharepoint lists connected to my powerapp. One SP list is called DOA and has columns that include Role (choice column) Incumbent and Delegate (person columns) and Active Incumbent (Yes/No column). My other SP list called CVAA is the one I'm using for my form in my powerapps. It has a datacard called Privacy/ATIP Approval which is a person dropdown that displays emails. I want my code to work as the following: it will first check the DOA list to find a row with the Privacy/ATIP Approval choice selected as the Role. Then, it will check to see if there is an Active Incumbent. If the value of Active Incumbent is Yes, I would like the Incumbent's email in that row to be displayed as the default value of that dropdown Privacy/ATIP Approval datacard. If Active Incumbent's value is No, I would like the email 123@gmail.com to be displayed as the default value for the dropdown datacard. I have done multiple attempts and I will paste them below so you can get an idea of what I'm trying to go for (I believe I'm mainly struggling with the syntax):

If(
    'DOA'.Role.Value = "Privacy/ATIP Director" && 'DOA'.'Active Incumbent'.Value = true,
    'DOA'.Incumbent.Email,
    "joshua.john@catsa.gc.ca"
)

If(
    LookUp('DOA', Role.Value = "Privacy/ATIP Director").'Active Incumbent',
    LookUp('DOA', Role.Value = "Privacy/ATIP Director").Incumbent.Email,
    "joshua.john@catsa.gc.ca"
)

I get several errors with these attempts and I'm struggling as I'm sure it's a very small problem somewhere that's causing everything to not work. Please let me know if there is any other information you need I really don't know what to do. Any help/advice is greatly appreciated!

[WarrenBelz]

Hi @Anonymous ,

I am not totally clear here, but is this what you are after ?

With(
   {
      wRole:
      LookUp(
         'DOA', 
         Role.Value = 'Privacy/ATIP Director'.Selected.Role
      )
   },
   Coalesce(
      wRole.'Active Incumbent'.Email,
      "joshua.john@catsa.gc.ca"
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Anonymous]

When checking for a valid row in my DOA list, I need to first see if in that row, the Privacy/ATIP Director was selected as the Role and I also need to see if Active Incumbent is set to Yes. If those two checks are satisfied, I want the default value of the datacard to be the Incumbent's email. Else, the email would be 123@gmail.com.

If(
    'DOA'.Role.Value = "Privacy/ATIP Director" && 'DOA'.'Active Incumbent'.Value = true,
    'DOA'.Incumbent.Email,
    "123@gmail.com"
)

I feel the above chunk of code best describes my goal it's just a matter of getting the right syntax as I am really struggling to get the hang of Power Apps syntax. Sorry if this is a bit confusing, let me know if this helps or if you need more information!

[WarrenBelz]

Hi @Anonymous ,

Still not totally clear as I cannot see your data, but is this what you want ?

With(
   {
      _Role:
      LookUp(
         'DOA', 
         Role.Value = "Privacy/ATIP Director" &&
         'Active Incumbent'
      )
   },
   Coalesce(
      _Role.Incumbent.Email,
      "joshua.john@catsa.gc.ca"
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Anonymous]

Sorry for the delay in response. When using the function provided above, I run into various issues. I get the error "Expected Record value" throughout the code and it also says "The function 'Coalesce' has some invalid arguments". I've been trying to decode and fix these errors the last few days but I've had no luck. I really appreciate your help though.

I'll attach below images of both sharepoint lists so you can get a better idea what data I have:

DOA List Example:

Columns^

Example Entry^

CVAA List:

Important columns^

Example entry^

Let me know if this helps. Sorry for all this back and forth. Once again, I truly appreciate all the help provided!

[WarrenBelz]

@Anonymous ,

Please see amended post

[Anonymous]

Hi, I appreciate the quick reply. Below is what my screen looks like:

I'll try debugging it on my own for today along with other stuff but the error "The function 'Coalesce' has some invalid arguments" has gone away and just the "Expected Record value" error remains throughout the chunk of code. Once again, I really really appreciate all the advice you've offered, it means a lot.

[WarrenBelz]

Hi @Anonymous ,

The function simply returns the first non-blank value, so effectively the "long" way on everything would look like this

If(
   IsBlank(
      LookUp(
         'DOA', 
         Role.Value = "Privacy/ATIP Director" &&
         'Active Incumbent'
      ).Incumbent.Email
   ),
   "joshua.john@catsa.gc.ca",
   LookUp(
         'DOA', 
         Role.Value = "Privacy/ATIP Director" &&
         'Active Incumbent'
      ).Incumbent.Email
   )
)

[Anonymous]

Hi,

Apologies for the later reply. When I try doing even the "long way" with the code provided above, I run into several issues:

I feel it may have to do with another areas of my app that may be lacking so I tried playing around with that but still not too sure and wasn't able to solve the issue. I appreciate the help.

[WarrenBelz]

@Anonymous ,

We seem to have gone off track with a few values (see amended post)