Hello,
I am trying to set up a page that queries a database based on a few items that I am picking in a retool app. I have it working where I can select from a list of organizations, and it will query the database based on that selection by the organization ID. I am trying to make it possible to select a date range as well and have it either query or filter the results based on that date range as well.
In the table component I was able to get this to work by using the "filter" section to filter based on the date picker values, but I am also trying to just get a count of all of the results based on the date picker range as well.
I can't figure out if there's a way to use the table component to reference elsewhere and return the filtered results. For instance, if I do {{table1.data.length}}, that returns the count of everything in the table rather than the filtered results based on date range. Alternatively, if I do a specific database query using the date range components as part of the actual query, I can't get it to handle a scenario where I don't have one or both dates picked.
For example, if I use this query -
SELECT COUNT(id)
FROM tickets
WHERE organization_id = {{listbox1.selectedItem.id}}
AND created_at BETWEEN {{dateRange1.value.start}} AND {{dateRange1.value.end}};
This functions correctly if the dates are both provided, but just fails if one or both of them are not entered. I would like it to just exclude whichever one is not provided if that is the case, for instance if the "end" date is not provided, just get everything up through the current date. If neither are provided, just search everything regardless of the date.
I'm not sure I'm approaching this the right way in either way of handling it, but does anyone have any suggestions on how you could handle this scenario?
