Conditionnal Filtering

App Building
1

Hi,

I'm trying to filter an Item collection in my mobile app.
I have a text field looking for serial number and customers.
I also want a date picker to filter further much.
But If the date picker is empty it needs to ignore the filter.

this is my Query
SELECT
"Inventory"."ID",
"Inventory"."BarCode",
"Customers"."Name",
"Volumes"."Description" AS "VolumeDescription",
"Inventory"."ReceivingDate",
"Locations"."Description" AS "LocationDescription",
"Inventory"."DeliveryDate"
FROM
"Inventory" AS "Inventory"
LEFT JOIN "Customers" AS "Customers" ON "Inventory"."CustomerID" = "Customers"."ID"
LEFT JOIN "Volumes" AS "Volumes" ON "Inventory"."VolumeID" = "Volumes"."ID"
LEFT JOIN "Locations" AS "Locations" ON "Inventory"."LocationID" = "Locations"."ID"
WHERE "Inventory"."BarCode" ~ {{ searchInvItemCollection.value }}
OR "Customers"."Name" ~ {{ searchInvItemCollection.value }}
AND "Inventory"."DeliveryDate" IS null
ORDER BY "ID" DESC

I tried adding this but it does not work
AND ( {{ !dateFilterInventory.value }} OR "Inventory"."ReceivingDate" = {{ dateFilterInventory.value }} )

The date is never empty / null in the database. that's why I need it to skip the filtering If the component is empty

1 Like
2

I also tried this
AND COALESCE("Inventory"."ReceivingDate" = {{ dateFilterInventory.value }}, "Inventory"."ReceivingDate" > '2000-01-01')

But it gave me this error
etInventorySearchfilter failed (0.316s):invalid input syntax for type date: ""
GetInventorySearchfilter
from GetInventorySearchfilter response(GetInventorySearchfilter)
in GetInventorySearchfilter.trigger()(GetInventorySearchfilter)
searchInvItemCollection.value: ""dateFilterInventory.value: ""environment: "production"
from GetInventorySearchfilter.query update(GetInventorySearchfilter)
from dateFilterInventory.value update(dateFilterInventory)
from user interaction

3

Fixed It
I created a variable thas is null by default
and added this to my query
AND COALESCE ("Inventory"."ReceivingDate" = {{variableDateFilter.value}}, "Inventory"."ReceivingDate" > '2000-01-01')

1 Like