cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdvantonder
Helper I
Helper I

Lookup function returns blank value when not

Hi all
I have two SP lists, ListA and ListB. I try to set a variable to the ID from ListB based on multiple conditions in a Lookup.
To explain, my variable (varIDListB) is:

Set(varIDListB, Text(LookUp('ListB', Column1 = Text(galleryItemsListA.Selected.ID) && Column2 = "blue" && Column3 = "round", ID)))

I want to return the ID of the ListB item where the following conditions are met:
- The selected item's ID from a gallery of data source ListA is the same value in Column1 of ListB

- Column2's value in ListB is "blue"

- Column3' value in ListB is "round"
All three columns are text columns. I did a label text by setting the Text property of the label to:

Text(LookUp('ListB', Column1 = Text(galleryItemsListA.Selected.ID) && Column2 = "blue" && Column3 = "round", ID))

And the value returned in the label was "9", which is correct. However, when calling the variable varIDListB with the same function, it is blank.

I want to use this ID to patch an existing record in ListB:

If(
    IsBlank(varIDListB),
    //If it is blank, it is a new record
    Patch(
        'ListB', Defaults('ListB'),
        {
            IDListB: Text(galleryItemsListA.Selected.ID),
            Complete: "Yes")
        }
    ),
    //If it is not blank, update existing record
    Patch(
        'Procurement Tasks', {IDListB: varIDListB},
        {
            IDListB: Text(galleryItemsListA.Selected.ID),
            Complete: "Yes")
        }
    )    
);

  

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@mdvantonder 

Oops, my bad...I forgot that using Patch and UpdateIf in the context of a With statement causes that issue.

This is because Patch returns a record and UpdateIf returns a Table and that (for some reason) confuses the With statement.

 

Change to the following:

With({_itmID: 
      Coalesce(
          LookUp('ListB', 
              Column1 = Text(galleryItemsListA.Selected.ID) && 
              Column2 = "blue" && 
              Column3 = "round", ID
          ), 
          0
      ),
      galID: galleryItemsListA.Selected.ID
   },
      
    If(
        _itmID = 0,
        Patch('ListB', Defaults('ListB'),
            {
                IDListB: Text(galID),
                Complete: "Yes"
            }
        ),
        //If it is not blank, update existing record
        First(
            UpdateIf(
               'Procurement Tasks', IDListB = Text(_itmID),
                {
                   IDListB: Text(galID),
                   Complete: "Yes"
                }
            ) 
        )   
    )
)   

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
RandyHayes
Super User
Super User

@mdvantonder 

You do have an error in your formula - extra ending parens on the Complete columns.

 

Please consider changing your Formula to the following:

With({_itmID: 
      Coalesce(
          LookUp('ListB', 
              Column1 = Text(galleryItemsListA.Selected.ID) && 
              Column2 = "blue" && 
              Column3 = "round", ID
          ), 
          0
      ),
      galID: galleryItemsListA.Selected.ID
   },
      
    If(
        _itmID = 0,
        Patch('ListB', Defaults('ListB'),
            {
                IDListB: Text(galID),
                Complete: "Yes"
            }
        ),
        //If it is not blank, update existing record
        UpdateIf(
            'Procurement Tasks', IDListB = Text(_itmID),
            {
                IDListB: Text(galID),
                Complete: "Yes"
            }
        )    
    )
)    

You can do away with the variable you are trying to create.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes 

Thanks, I applied your solution. I just receive an error with the UpdateIf() part of the function:
"Invalid argument type (Table). Expecting a Record value instead."
I changed to the following of the UpdateIf() part of your solution [since I slipped to change it in my example 😕 ]:

        //If it is not blank, update existing record
        UpdateIf('ListB', IDListB = Text(_itmID),
            {
                Column1: Text(galID),
                Complete: "Yes"
            }
        )

What I understand is this part evaluates whether the ID in ListB (IDListB) is the same as the Lookup ID (_itmID), and if so, update accordingly. Yet, I receive the error message. 

 

RandyHayes
Super User
Super User

@mdvantonder 

Oops, my bad...I forgot that using Patch and UpdateIf in the context of a With statement causes that issue.

This is because Patch returns a record and UpdateIf returns a Table and that (for some reason) confuses the With statement.

 

Change to the following:

With({_itmID: 
      Coalesce(
          LookUp('ListB', 
              Column1 = Text(galleryItemsListA.Selected.ID) && 
              Column2 = "blue" && 
              Column3 = "round", ID
          ), 
          0
      ),
      galID: galleryItemsListA.Selected.ID
   },
      
    If(
        _itmID = 0,
        Patch('ListB', Defaults('ListB'),
            {
                IDListB: Text(galID),
                Complete: "Yes"
            }
        ),
        //If it is not blank, update existing record
        First(
            UpdateIf(
               'Procurement Tasks', IDListB = Text(_itmID),
                {
                   IDListB: Text(galID),
                   Complete: "Yes"
                }
            ) 
        )   
    )
)   

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Thanks! This worked perfectly 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,822)