Saturday Showcase Show-off

I find it fun to test the limits of Bubble from time to time and explore different strategies for accomplishing various tasks in Bubble.

Disclaimer 1: This isn’t meant to be an example of “what to do” in Bubble, but rather “what can be done” in Bubble.

Goal: Generate a specific JSON text to be used to generate a chart (something like below).
{"datasets": [{"series": "Series 1","data": [{"month": 1,"amount": 130},{"month": 2,"amount": 140}]},{"series": "Series 2","data": [{"month": 1,"amount": 150},{"month": 2,"amount": 160}]}]}

I wanted to generate this format using a single Bubble designer box, with no dependencies on any other workflow, input or plugin. I also wasn’t attempting to find the best solution, but simply a solution.

Let’s begin.

Screenshot 2022-08-20 at 8.42.43 PM

Here is our single design input box. The white text in the image is static text, it is JSON formatting that is wrapping the content generated by the blue text which is dynamic. We are going to explore the complexities of the dynamic content next.

First we need to get a set of categories, one of our custom Data Types. Each category represents a “series” in the JSON above. So we “Do a Search for ParCategories”:

We need to do more with the categories than to simply print out the name. We need to get data related to the category so we are going to use the “Format as text” to append more data and manipulate the content. Format as text is intended to split data out into a string separated by a delimiter. We can use this to create advanced data queries and the power of Bubble dynamic content starts to shine.

The white text again is static text to provide some necessary formatting for the proper JSON text. This ParCategory's Name comes from the previous step, Name being a Field on the ParCategory DataType. However, the remaining content we need still need to find.

Our actual series data is going to come from Inventory Counts so the next piece is to “Do a Search” for InventoryCounts. We want to restrict the counts to their respective categories so we are going to Constrain the search to each category. Part of the Bubble magic is that by using the “Format as text” we are creating a sort of “loop” over the categories. So we can select the specific data we need per category by restricting the InventoryCount search using the constraint shown below:

The next piece is we want to get the Inventory Counts per category for each day in the last 30 days. So we create a grouping as shown below:

We group by the Category since we want the Inventory Counts for each category to be the series. Then we group by InventoryCount Create Date since we want the chart to show the counts for each day. When a date field is the last field in a group Bubble provides this very convenient option to create a series that starts on a date and ends on another date (a range of dates). So we start 30 days in the past by using +days -30 to the current date.

Further, we want to sum the Inventory OnHand field to get the total count for each category for each day. This is done at the bottom of the grouping by adding an aggregation as shown below:

Screenshot 2022-08-20 at 9.31.48 PM

This creates a list of dates (30 to be specific) and for each date it gives us a category and a sum of the inventory on that date. So, if we had 2 categories we end up with 60 records, 3 categories 90 records, and so on. We still need to manipulate the content so we use “Format content as text” once again.

Here again we have some static content (in white) which is just to provide the necessary JSON format. We format each date field to remove the time stamp and for the actual inventory counts we need the aforementioned grouping’s sum.

However, there is one issue left with the actual InventoryCount grouping’s sum. When there is no data it does not give us a 0 but an empty entry, which doesn’t do well for graphing. So we need to once again take advantage of another “Format as text” so that we can manipulate the content even further.

Bubble does something nice for us here. It detects that we were formatting text from a logic (Boolean) expression, and gives us the option to format differently depending on if the result is true or false (yes or no). In this case when the content is empty I want to show “0” and when the content is not empty I want to show the content This Grouping's sum of OnHand

The end result of all of those transformations is a string that looks like this:

 {"datasets": [{"series": "Category 1", "data":[ 
{"date": "7/21/22", "inventory": 5},
{"date": "7/22/22", "inventory": 15},
{"date": "7/23/22", "inventory": 25},
{"date": "7/24/22", "inventory": 35},
...]},
{"series": "Category 2", "data":[ 
{"date": "7/21/22", "inventory": 80},
{"date": "7/22/22", "inventory": 70},
{"date": "7/23/22", "inventory": 60},
{"date": "7/24/22", "inventory": 50},
...]},
{"series": "Category 3", "data":[ 
{"date": "7/21/22", "inventory": 10},
{"date": "7/22/22", "inventory": 20},
{"date": "7/23/22", "inventory": 30},
{"date": "7/24/22", "inventory": 40},
...]}]}

I wouldn’t recommend this strategy for performance and technical reasons. However it is nevertheless possible in Bubble to create complex logic and data manipulations without using workflows or temporary states.

3 Likes

Hi @bubble.trouble :wave:
I was not aware of this.
Thanks :+1:
I hope you do more "Saturday showcase Show-off"

1 Like