2 queries. 1 updates the table, the other doesn't

Queries and Resources
1

Hello.
I have 2 queries to search a table by differnent values. One query updates the table, the other doesn't, although clicking "save and run" returns the expected results.

This query works:

select * from dbo.SavedInventory WHERE ItemName LIKE {{'%' + textInput2.value + '%'}}

And only presents the relative items in the table
image

This query does not update the table. The table continues to show all values.

select * from dbo.SavedInventory WHERE ItemNum LIKE {{'%'+ textInput1.value + '%'}}

But when I execute it manually, it does return the expected results.

2

Hey there @DCB!

For some additional context here would you mind sharing a couple of extra things?

  1. How are you passing data to this table?
  2. How is each being triggered when you don't run it manually?

Happy to help investigate here :slightly_smiling_face:

3

For the search by name, I just populate the field and the table auto-updates.

textInput#.value

^ This is using the value from the text boxes in the picture; Where 'Barcode Search' is
textInput1.value and textInput2.value is the name search

4

I see, and what is being passed to the table's "Data" field?

If you want to have two search fields for the same table, you may need to combine the two queries into something like:

select * from dbo.SavedInventory WHERE ItemName LIKE {{'%' + textInput2.value + '%'}} AND ItemNum LIKE {{'%'+ textInput1.value + '%'}}
5

Ah, it's {{ NameSearch_SavedInventory.data }}
I tried adding {{BarcodeSearch_SavedInventory.data}} but it gives me...
Invalid value. Try passing in an array of objects, or the result of a SQL Query with something like {{ query1.data }}

I also tried updating {{ NameSearch_SavedInventory.data }} to be...

select * from dbo.SavedInventory WHERE ItemName LIKE {{'%' + textInput2.value + '%'}} OR ItemNum LIKE {{'%' + textInput1.value + '%'}}

But then neither field updates the table

6

Is it possible to have 2, or is it 1 table per query?

7

Ahh if you want to return the combined results of both searches instead of all rows that meet both criteria it's a bit trickier since any empty query will return all of the results. Does the above query work once you've entered a value into both fields?

I'm curious what you see if you try something like:

SELECT
  *
FROM
  dbo.SavedInventory
WHERE
  {{ !textInput1.value && !textInput2.value }}
  OR (
    {{!!textInput2.value}}
    AND ItemName LIKE {{'%' + textInput2.value + '%'}}
  )
  OR (
    {{!!textInput1.value}}
    AND ItemNum LIKE {{'%' + textInput1.value + '%'}}
  )

That should check to see if both inputs are empty, return the full table if so, and otherwise only pull rows for inputs that actually have a value.

8

Hello,
I copy/pasted your code, but received

An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.

9

Playing with your code, this works as expected... except it doesn't update the table no matter the result...

SELECT * FROM  dbo.SavedInventory WHERE (ISNULL({{textInput1.value}}, '') = '' AND ISNULL({{textInput2.value}}, '') = '')
  OR (ISNULL({{textInput1.value}}, '') = '' AND ItemName LIKE {{'%' + textInput2.value + '%'}})
  OR (ISNULL({{textInput2.value}}, '') = '' AND ItemNum LIKE {{'%' + textInput1.value + '%'}})
10

Ah! Are you using MSSQL? Sorry I hadn't noticed earlier if so :sweat_smile:

Can you try one of the following?

SELECT * FROM dbo.SavedInventory WHERE ({{textInput1.value ? 0 : 1}} = 1 AND {{textInput2.value ? 0 : 1}} = 1)
 OR ({{textInput1.value ? 1 : 0}} = 1 AND ItemName LIKE {{'%' + textInput1.value + '%'}})
 OR ({{textInput2.value ? 1 : 0}} = 1 AND ItemNum LIKE {{'%' + textInput2.value + '%'}})

or

SELECT * FROM dbo.SavedInventory WHERE ({{textInput1.value || null}} IS NULL AND {{textInput2.value || null}} IS NULL)
 OR ({{textInput1.value || null}} IS NOT NULL AND ItemName LIKE {{'%' + textInput1.value + '%'}})
 OR ({{textInput2.value || null}} IS NOT NULL AND ItemNum LIKE {{'%' + textInput2.value + '%'}})

The first reference to each text input should return true if the text input is empty which ISNULL({{textInput1.value}}, '') = '' works well for, but the second reference needs to return true if the text input is *non-empty *since we only want to add values from those filters if the user has actually entered something.