Date / Data Filter help needed

Hi.

I’m trying to filter data by dates dynamically. It displays how it should in preview, but when loading in browser there is an error which doesn’t allow the page to load even.

  1. In the database query I created a condition that it should match dates between the two GET values:

  1. The two GET values are connected to two variables

  1. In the variables I have only static values for now:

  1. It works perfectly in the preview:

  1. When loading the page in the browser, it gives this error message on the browser:

{"status":"500","message":"Undefined binding(s) detected when compiling SELECT. Undefined column(s): [sales.DateDay] query: select \"DateDay\" as \"x\", count(\"DateDay\") as \"y\" from \"sales\" where \"sales\".\"DateDay\" between ? and ? group by \"DateDay\"","stack":"Error: Undefined binding(s) detected when compiling SELECT. Undefined column(s): [sales.DateDay] query: select \"DateDay\" as \"x\", count(\"DateDay\") as \"y\" from \"sales\" where \"sales\".\"DateDay\" between ? and ? group by \"DateDay\"\n at QueryCompiler_PG.toSQL (/opt/node_app/node_modules/knex/lib/query/compiler.js:98:13)\n at Builder.toSQL (/opt/node_app/node_modules/knex/lib/query/builder.js:97:44)\n at /opt/node_app/node_modules/knex/lib/runner.js:31:36\n at /opt/node_app/node_modules/knex/lib/runner.js:277:24\n at async App._exec (/opt/node_app/lib/core/app.js:449:30)\n at async App._exec (/opt/node_app/lib/core/app.js:420:17)\n at async App.exec (/opt/node_app/lib/core/app.js:389:9)\n at async App.define (/opt/node_app/lib/core/app.js:379:9)"}

  1. When using static values in the conditions all works perfectly, so the query is right for sure. So I guess I’m making a mistake with the binding?

Does anyone have an idea what I’m doing wrong? I tried to use set value and other stuff in the server connect but nothing works. I keep getting this error.

The query generated by the builder seems wrong.
It should be “sales.DateDay” in the select columns list I think.
Maybe try to create another query step with just a few columns selected and no aggregate functions or conditions. Compare that with the one you have above if it works.

Not sure why it would work in preview but not in browser, because preview itself is pretty much the browser itself.

Thank you for your reply.
But why does it work when in the conditions I type the dates? I think it’s a problem in the binding of the variables.

Oh. I missed your point number 6.
Maybe try printing the get values using a set value to be sure that values are being received in server action?

One or both expressions used for the parameters returned undefined, at least that is what the error is telling.

If you are values from user input it is best to also validate it and perhaps have a default value like $_GET.date_start.default('2021-01-01').

1 Like

Hi! I tried this too, it gave the same error…

Hi, so at the moment it’s not from a user input, I just filled in the values already, it’s static. Somehow it doesn’t bind the value to the server connect query. Don’t exactly know what to do else

I tried all without using grouping and aliases etc. The problem is that the the $_GET values are not binding when the page loads.

How does everyone work with filtering queries if this doesn’t work?

My gut feeling is that the dates you specify may be seen as strings so the comparison with the date field fails

Try defining the two dates using a Set value step with something like "2021-09-05’.toDate() to ensure they are dates and not strings then use the “Set Value” variables as your between conditions.
Remember $_GETs are passed as strings

Hi Brian,

Thank you for the suggestion, unfortunately I couldn’t make that work.

I made the “set values” between with .toDate() but it gives an error about that the formatter doesn’t exist.


I tried all of the other of the date formatting options in the list but all of them return the initial error.

I also can’t understand why it works perfectly in the preview…