If statement for Material in SharePoint List where column equals two values

[Holden87]

I have the example below where I need to see if a textinput is in a SharePoint list where a 2nd column contains two values.

Example:
'RequestList' - SharePoint List
'RequestList'.'Material' - The first column in my Sharepoint List
'RequestList'."OrderStatus' - The second column in my SharePoint List, this is a choice column.
DataCardValue1 - The text input I am trying to evaluate


I am attempting to write an If statement for an OnSelect property of a button.  The evaluation needs to Check:

DataCardValue1.Text is in 'RequestedList'.'Material' where 'RequestedList'.'OrderStatus' is either "Pending Approval" or "Open".
Based on the result, I will then Update a context variable based on the result.

If(DataCardValue1.Text in "RequestedList'.'Masterial', UpdateContext({locVar1:true}), UpdateContext({locVar2:true}))

This works fine, but I cannot figure out how to also check the 'OrderStatus' column of this Material as well to determine if the OrderStatus column contains either of those two values for this record.  Is this possible without being in a gallery as I am attempting to check for an existing record in SharePoint against a NewForm.  Any help would be greatly appreciated.

Thank you,

Eric

[C-Papa]

Hi, using update context is expecting a string or Boolean etc but your query will return more than one record as multiple items in the data source may meet the criteria, regardless if there is actually only one. 

you would be better off using clearcollect() to collect all the items that meet the criteria and then counting the rows in the collection, if the number of rows = 0 then no matches have been found. 

i would do something like this on button select:

ClearCollect(col_temp,Filter('RequestList',And(DataCardValue1.Text in 'Masterial',Or('OrderStatus' = "Pending Approval",'OrderStatus' = "Open")));Set(var_row_count,countrows(col_temp));Clear(col_temp)

then set the text value of the button the be var_row_count. if the text is 0 the it doesn't exist. if its equal to or more than 1 then it does. 

[Holden87]

Thank you for your prompt response.  I agree with the approach, but it seems the Or('OrderStatus' = "Pending Approval",'OrderStatus' = "Open") section is not yielding any results as the col_temp collection doesn't have any records and the var_row_count is 0.  I want to reiterate the 'OrderStatus' column is a choice column in SharePoint, does this change the approach?

[C-Papa]

Hi, sorry didn't spot it was a choice column, you need to change it to:

Or('OrderStatus'.Value = "Pending Approval",'OrderStatus'.Value = "Open")

[Holden87]

Hello C-Papa,

Thank you for the help.  Great use of the formula, but rows are being counted still if the material with anything in the 'Order Status' column, so the Or('OrderStatus'.Value = "Pending Approval",'OrderStatus'.Value = "Open") is not working.  Very close, but note quite right.  Any other recommendations?

[C-Papa]

how about without the Or():

ClearCollect(col_temp,Filter('RequestList',And(DataCardValue1.Text in 'Masterial','OrderStatus'.Value = "Pending Approval",'OrderStatus'.Value = "Open")));Set(var_row_count,countrows(col_temp));Clear(col_temp)

[Holden87]

If the formula is updated to your recommendation, if the material is present in the SharePoint list, the row count will always be 0.  The only other solution I can think of would be to set a collection with the OrderStatus column options I want to filter on.  Thoughts?

[Holden87]

Looked at this a little further and the Or('OrderStatus'.Value = "Pending Approval",'OrderStatus'.Value = "Open") is counting EVERY occurrence of this value no matter what the material is, so it looks like that piece of the statement isnt looking at material.

[WarrenBelz · Accepted solution]

Hi @Holden87 ,

A bit more complex to manage the Delegation issue with your in Filter, but providing the newest 2,000 items (and you have your limit set to this) that have a status of either Pending Approval or Open will be what you need, you can then filter in a Delegable manner looking into the Material field. Also you do not need the collection - this will set the Variable to the number of matching rows.

With(
   {
      wRows:
      With(
         {
            wStatus:
            Sort(
               Filter(
                  'RequestList',
                  'OrderStatus'.Value = "Pending Approval" ||
                  'OrderStatus'.Value = "Open"
               ),
               ID,
               Descending
            )
         },
         Filter(
            wStatus,
            DataCardValue1.Text in 'Material'
         )
      )
   },
   Set(
      var_row_count,
      CountRows(wRows)
   )
)

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.

[Holden87]

Works like a charm!  Thank you for providing this solution!