Conditionnal Filtering


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
"Volumes"."Description" AS "VolumeDescription",
"Locations"."Description" AS "LocationDescription",
"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

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

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: ""
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

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