Lookup function returns blank value when not

[mdvantonder]

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")
        }
    )    
);

[RandyHayes]

@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.

[mdvantonder]

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 · Accepted solution]

@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"
                }
            ) 
        )   
    )
)   

[mdvantonder]

Thanks! This worked perfectly 🙂