Setting URL placeholder values with formulas in data variables

Hi there, wanted to check if it’s possible to set URL placeholder values dynamically with formulas in data variables?

For eg. for a Schedule data resource, I want to add URL placeholders to filter the records returned in the API call response. Those URL placeholders are supposed to set the “from” and “to” datetime values depending on the current date and time.

This is to allow the app to filter classes based on the date the user selects (for eg. classes for today, classes for tomorrow, and so on).

When I add static values, the API response is valid. But when I use formulas, it doesn’t.

The exact formulas I’m using are:

TIMESTAMP(NOW()) for the “from” value
TIMESTAMP(DATETIME(ADD_DURATION(NOW(),1, “days”)))

I’m using TIMESTAMP because Backendless (the database service I’m using) datetime data is in UNIX format.

Any insights into making customising API calls to pull records that have a datetime field that happens between the current time and tomorrow, tomorrow and the following day, the following day and 2 days later, and so on are greatly appreciated!

What I’m trying to achieve is a schedule filter similar to that on ClassPass or MindBody.

Thanks!

Hmmmm. I don’t know much about this topic but some things I would try out:

  • Set the static timestamp value via formula, does that work?
  • If static timestamp via formula works, I would check that the format you get from the TIMESTAMP formula matches what you need exactly
  • If the static timestamp via formula doesn’t work, something is wrong with how it is passed. Should it be in an object? Would a delay node in front help (if in logic)?

Hey @Mevi, I suspect it’s a bug, but not sure if I’m just missing something. Some screenshots to illustrate what I’m saying.

This is the data configuration. I have two URL placeholders/parameters for the “from” datetime and “to” datetime, which are timestamps:

If I hard code the static values, it works. The WHERE clause is basically to return any records with classStart that is bigger than “from” datetime and smaller than “to” datetime.

When I use the formula TIMESTAMP(NOW()), it’s fine, as you can see in the two screenshots below.


I’m trying to get the timestamp for the “to” datetime parameter based on the current date. So I used TIMESTAMP(DATETIME(ADD_DURATION(NOW(), 1, "days"))).

The validator says it’d return a number and the example value looks good.

In theory, this should work. But somehow, it doesn’t for some reason. It just returns an empty string.

So I tried TIMESTAMP(NOW()) + 86400 and any large number, really, but it still didn’t work. Not sure what I’m missing.


If there’s a way to print/return the actual value of the formula, perhaps troubleshooting might be easier.

Thanks so much for your help!

If anyone has any idea or experience with this, please shed some light. I’m using Backendless as the database solution.

Thanks in advance :slight_smile:

Hi!

I had a look and I’m still not much wiser, but I’d try the following things:

  • Remove the DATETIME from the latter formula, as I think it’s not necessary
  • Try having a static value in the from parameter and the formula with the added duration in the to formula

In addition, if you could share me your app id or perhaps the app id of a tester app where you have a similar set up I could check it out. I’ll make a repro based on that and forward this as a bug, unless it gets solved with these ideas.

Hey @Mevi, I actually found the problem. I thought the TIMESTAMP function for AppGyver’s formula returns the UNIX timestamp in seconds, when in actual fact, it’s in milliseconds, which is exactly the same as Backendless’s.

Because of that misunderstanding, I added “000” behind the URL parameters of to and from in the API call URL. That caused the problem.

It looked something like this:
https://api.backendless.com/{api-key}/data/schedules?where=classStart%20>%20{from}000%20and%20classStart%20<%20{to}000&loadRelations=className%2Cinstructor%2Cattendees

It’s working perfectly now, and I’m going to share the formulas I’m using in case other people might want to know too.

For current date: TIMESTAMP(NOW())

For end of today (I’m using SET_TIMEZONE to set to Singapore’s timing because that’s where I’m based in and most of our users are): TIMESTAMP(ADD_DURATION(SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(SET_TIMEZONE(NOW(), ("Asia/Singapore")), 0, "hour"), 0, "minute"), 0, "second"), 0, "millisecond"), 1, "days"))

Doing the above so that I’m returning the value of 00:00:00 of the next day, so only the classes that start from the current time and end of today will show up in this list.

For subsequent days, I’m intending to use this formula, which I tested briefly and it works too:
TIMESTAMP(ADD_DURATION(SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(SET_TIMEZONE(NOW(), ("Asia/Singapore")), 0, "hour"), 0, "minute"), 0, "second"), 0, "millisecond"), 1, "days")) + [no. of days]

Where [no. of days] would be in increments of 86400000 (no. of milliseconds in a day).

Thanks @Mevi for your help :slight_smile:

1 Like