Repeating group with merge of two tables (think left outer join)

Need help Database
1

I am loving Bubble but having difficulty organizing my data.

I have a calendar table (date, year, month, quarter, weekday, etc) and a sales table with a linked “calendar” field.

I am trying to return a repeating group where all records of the calendar table are returned and only matched records of the sales table are returned.

I am probably overthinking this and making this harder than it is.

(updated screenshot)

Untitled
Untitled1794×390 42.9 KB

2

One idea (if I’m understanding correctly)…

Set the Repeating Group (RG) to be type “Calendar” and do a search for the calendar items that you want included.

Then for each row within the RG, set text to display “search Sales Table for: Calendar Date = Sales Date’s Food :sum” and that should display the correct number for Food. Do the same for Beverage and you’re all set.

A few added items:

  • Add a condition to the search to make sure there’s at least 1 item that matches (or else Bubble will ignore the Calendar Date = Sales Date filter).
  • If you want “0” to return “null” then add a condition for that at the end of the statement.

Hope this helps.

Best,
Scott

3

Thanks @sridharan.s for the quick reply. I was able to get it working using your suggestions.

I created a page on the forum app for any others who might find the solution useful.

4

Seems quite a complex way to extract parts of the date, when you can use :extract for that ?

A simpler way (without the need for a calendar table) would be to set the RG to be date, and list the :unique dates.

In fact in Bubble there is no need to do this sort of SQL like join. You can achieve this just on the database.

So on your calendar thing, you could have a list of sales things for that day. (thing = object / row concept).

Or on your sales thing you could link back to a date thing (rather than a date, but see comment above).

This means that for each calendar thing, you can simply say "this calendar’s sales’s bev_sales:sum. (i.e. sum up all the bev_sales things that are listed on this date).

So you are doing your join in Bubble “apostrophe language” rather than via searches within searches which will be slower and more complex. Make the datebase do the work, as we don’t have the ability to set up indexes.

Searching your whole calendar table, and then searching each sales thing to see if there is anything for that date, and then searching if all again twice to sum up the two columns … seem like a long way round because in Bubble you can implement your relationships between things (objects/rows/entries etc) directly. It is object relational (sort of) rather than relational.

Again, this all assumes you have some sort of “accounting date” for each sale rather than it being the creation date.

3 Likes
5

Revisiting this project again and having difficulty setting up a conditional statement to replace the null values in the joined table with something user friendly such as “no data”.

The available options in the Conditional panel don’t seem to provide for checking for a null value.

In addition, I am not able to sum on the field values that are not in the primary table of the repeating group.

Any help would be appreciated.

6

Any solution to this? I am looking to do basically the same thing.