Can't aggregate on date from a backend API

atcgymnastics · 2025-02-06T20:38:50.609Z

I have create a backend API that combines fields from 3 different fee tables and 3 credit/payment tables. It returns a pseudo JSON list. Everything works fine until I try to aggregate the data in a repeating group. It won’t aggregate any of the date or amount fields with anything except “Exact”. It says they are not numbers or dates. I used the Bubble API Connector to examine the data and it is putting quotation marks around the key/value year. I am using the :Format as JSON Safe for each.

I created a new API just to test the dates to try to narrow down what is going on. Here is the text format:

image358×348 6.64 KB

This is what the JSON looks like:

image748×570 9.84 KB

This is the error I am getting:

image351×394 9.83 KB

I have tried extracting to the date to UNIX, formatting the date in various date formats, and everything else I can think of. What am I doing wrong? I assume it is on the text formatting end causing the JSON to be formatted as text, not on the Group By end just interpreting it wrong. (There are quotation marks around the dates, signaling it is string, not date data.) Any thoughts?

Jici · 2025-02-06T20:47:13.044Z

i don’t see the amount, so not sure about the structure. However, when you set the API connector, did you set the type of “date” to “date” and not text?

In JSON, there’s no date type of data. So date are always encoded as string/text and Bubble will set this to text by default. You need to change this to date when you initialize the API

atcgymnastics · 2025-02-06T20:52:43.406Z

It all of a sudden started working and I don’t know why. All I did was change back from this test API to the original API that contained the real data and it works. I didn’t change anything other than the API provider in the data source in the repeating group. I was working on it for 4 hours before that.

atcgymnastics · 2025-02-07T14:08:26.558Z

I played with it some more to try to replicate the error. I was working with several instances of Bubble open in several different windows. I discovered that changes made in one window weren’t always updating in other windows unless I explicitly refreshed that window. So, although I was making changes to the API, the editor in another window didn’t recognize those changes all the time.

I also think it didn’t like me naming the key “Date”. When I named it “TransDate” it worked.

atcgymnastics · 2025-02-07T14:13:06.261Z

Jici, To answer your question, displayed was simply a test API I made to test the issue. In the real API I had 7 fields, but I was trying to group by date and it wasn’t working, so I simplified it to just that one field for test purposes. Once I refreshed the editor, it worked.