Multiple filters using number inputs

App Building
, , ,
1

Hi there,

I am trying to pull info from a database using user input in number input boxes. I want the users to be able to enter any of the fields or any number of the fields and it be filtered by that, for example if a user wants to filter by account number and year and serial, then they can enter all 3 of those options and leave the other options empty. However I'm pretty new to sql and also new to ReTool so I'm struggling to find a way to do this that doesn't use a ton of ANDs in the WHERE clause.

Here is the basic look of the tool

image
image1506×611 10.8 KB

I want to be able to put in any of the options on the left to help filter the table on the right but I'm stuck. I essentially want to recreate the filter at the bottom but using easy to fill out input for the users..

In other tabs of this page I've done queries for 3 inputs using basically the below where clause

WHERE 
 (dbo.Asmt_ExemptionDetail.AcctNmbr = {{ exempt_acctnum.value}} 
  AND dbo.Asmt_ExemptionDetail.AsmtYear = {{ exempt_asmtyr.value }} 
  AND dbo.Asmt_ExemptionDetail.ExemptCode LIKE {{ '%' + exempt_code.value + '%' }})
  
OR 
 (dbo.Asmt_ExemptionDetail.AcctNmbr = {{ exempt_acctnum.value}} 
  AND dbo.Asmt_ExemptionDetail.AsmtYear = {{ exempt_asmtyr.value }} 
  AND {{ exempt_code.value}} = '""')

  OR  
  (dbo.Asmt_ExemptionDetail.AcctNmbr = {{ exempt_acctnum.value}} 
   AND {{ exempt_asmtyr.value }}  IS NULL 
   AND dbo.Asmt_ExemptionDetail.ExemptCode LIKE {{ '%' + exempt_code.value + '%' }})
   
  OR  
  ({{ exempt_acctnum.value}} IS NULL 
   AND dbo.Asmt_ExemptionDetail.AsmtYear = {{ exempt_asmtyr.value }} 
   AND dbo.Asmt_ExemptionDetail.ExemptCode LIKE {{ '%' + exempt_code.value + '%' }})`Preformatted text`
2

Hey @chelseawatson97! :slight_smile:

Happy to help here. Just wrote up this query and it seems to be working for me:

select * from actors 
WHERE ({{!textinput1.value}} OR first_name ilike {{'%' + textinput1.value + '%'}})
AND ({{!textinput2.value}} OR last_name ilike {{'%' + textinput2.value + '%'}})
AND ({{!textinput3.value}} OR actor_id = {{ textinput3.value }})

2396×1554 310 KB

Let me know how this works for you, or if you have any other questions!

1 Like
3

Oh great! I will try that with the !textinput.value options. :slight_smile: Report back soon!

4

Please do!


PS for anyone else reading, the !textInput.value syntax is noted in this doc :slight_smile:

5

Life saver! It worked!

I had to modify it a smidge and use the format of

WHERE ({{!select1.value ? 1 : 0}} = 1 OR value = {{select1.value}})

since we use MS SQL but it worked. The doc page helped with finding that format change :slight_smile:
Thank you!!

7

Yay!!!

So glad to hear it! And thank you for including the MS SQL callout for other users :slight_smile: