Calculating grand totals in repeating group

[namitdeb739]

I have a repeating group which has subtotals in each individual cell. These subtotals are a text-box, and are calculated on the spot (because the data that these subtotals add up can be changed on other pages). So far, the subtotals in each cell work perfectly fine, and are behaving as they should. My issue comes when I need to create a grand total (sum of all subtotals) OUTSIDE the repeating group.

I don’t think I can use :sum in this case, because the numbers won’t add up. Each subtotal does a search for data relevant to its own cell, so simply using a :sum doesn’t really work.

Another idea I had is in the dynamic text of the grand total text box, I reference the repeating group’s first item, and calculate the sum of that, then write dynamic text to add it to the repeating group’s second item’s sum total, and then the third, etc. But the repeating group could potentially have n amount of terms and I don’t know how to do an nth term sum in bubble (if possible).

If needed I can post screenshots/link to the app, but it’s quite big and the subtotals themselves reference a lot of other data which might confuse the situation (they work fine, I’m just trying to focus on grand total). A good analogy would be this Google sheet screenshot:

In this case the list of businesses and sub-totals would be in the repeating group, and the grand total at the bottom would be outside the repeating group and get the sum of the individual subtotals within the repeating group. This is just an analogy, and I’d be happy to provide more details if needed. Does anyone know how to get the grand total?

[SerPounce]

Instead of doing the subtotal calculations in text boxes, do the following:

1. Add an expression element that does the calculation for each subtotal.
2. Reference the newly added expressions in the text boxes.
3. Add an additional expression that is the sum of the subtotal expressions.

[robert]

You could use the plugin Repeating Group Tools (BDK). That will do it.

[namitdeb739]

Thanks! I’ve managed to create expressions for the subtotals and put them in text boxes, but I’m not being able to do step 3. Since my expression is outside the repeating group, how would I reference the sum of the subtotal expressions?

[SerPounce]

Shoot! Sorry, both you and @robert are correct. I totally misled you and the RG expressions cannot be accessed from outside the RG.

I believe you have 2 choices to solve:

1. As per robert’s suggestion, use the bdk RG plugin.
2. This is how I handle (but this only works if you are not doing any data entry within the RG. If you are doing anything in the RG then you can only do option 1):
   (1) Create a new custom state that represents business in your analogy above.
   (2) On page load set that custom state to all the businesses in question.
   (3) Your RG expression would reference the custom state and have the formula for the single row filtered for the business.
   (4) Your page expression would have the same formula as the RG row, but not filtered for the row and sum for all the businesses.

[namitdeb739]

Thanks for these suggestions @SerPounce @Robert!

The bdk plugin seems pretty cool, but I saw it was $16. To be honest, this is for a school project so I want to keep it free if possible (though if there’s no other solution then I’ll use the plugin if needed).

I’ve taken a look at both @SerPounce ideas, and to be honest I’m not too familiar with custom states. The issue I faced was that each subtotal is essentially calculated as such on the spot:

Subtotal= (Quantity * Price + Dividend)/ExchangeRate

This works fine if I filter the formula for specific rows, but an unfiltered formula wouldn’t give me a grand total like I needed. Again, I’m pretty new to custom states as a whole so maybe there’s a way of working this out using those. I’ll provide some screenshots for more context:

This screenshot is the layout of the page: the RG where the subtotals are shown is inside a repeating group of its own (to divide the businesses into different portfolios). I’ve circled the subtotals and the grand total, which lies in a group of its own outside of the subtotal RG.

This is the dynamic data (in a text box) describing a subtotal.

Here’s the details of the “Do A Search For” in the previous screenshot, which allows the subtotal to only provide details for one business (the constraints are the same regardless of searching for Transactions or for Market Prices).

You might notice that when the subtotal Searches for Market Prices, it only takes the first item. This is because with the given search constraints it’s only possible for one Market Price to exist (that’s done separately on a different page).

From what I can see, simply removing the filters so that the grand total takes the formula for all businesses won’t work. With the given criteria, individual Market prices belong to individual businesses, and you can’t really take a sum of all of them. Unless there was a way of doing it where it takes an Nth term sum like:

Grandtotal = subtotal1 + subtotal 2 + — + subtotalN

Is there a way of doing that through custom states? Please let me know

[SerPounce]

What is the criteria for how businesses get included in the RG? i.e. what is the data source for the RG? I might have an idea for a hack workaround, but need to understand better how the RG gets constructed.

[SerPounce]

Here is the solution using custom states and the hack I was thinking of. There is a fair amount of custom state jiggery pokery going on and I use a custom event that is triggered for the count of rows in the RG to add each transaction to the Grand Total custom state.

Let me know if you have any questions.

Bubble Editor - Rgcalculation

[namitdeb739]

I took a look at the custom states you made, and it looks really good, except for the issue being the datatype of the RG.

In my case the datatype is a search for Businesses, with the constraint being the Business’s Portfolio=Current Cell’s Portfolio (this is to divide the businesses into different portfolios, essentially to categorise them).

The text boxes that calculate the subtotals don’t simply take “current cell’s transaction” as Transactions is a completely different thing to Businesses. Each Business has multiple Transactions tied to it, hence why in the screenshot I attached in my previous reply I need to Search for Transactions where the Transaction’s Company Name = Current cell’s Business’s Company name.

The RG calculation you provided looks really great but I think in the AddTransaction workflow, it can’t refer to the current workflow transaction because the datatype of the RG is businesses. Is it possible to alter it slightly keeping this in mind?

[namitdeb739]

I think to better understand the app it might be better for me to share a version with you. The page In question with the subtotals and grand totals is ‘value_report’. Here’s a copy I made:

shareportfoliotracking.bubbleapps.io/version-test/

[namitdeb739]

Hey, @SerPounce, just wanted to let you know that I did a bit of tinkering around with your custom states to suit the datatypes for my RG and I managed to get it to work.

Thanks so much!

[SerPounce]

Great to hear it worked out!

[bezi0210]

@SerPounce @namitdeb739
Hi guys , im pretty new to Bubble and im trying to understand what youve discussed, because i think i’ve exactly the same issue
I started creating a calculator today.
It works like this:
user select product type in a dropdown menu
based on this selection, a repeating group shows in each row (text box) the name of a product (my repeating group shows unique elements of products from type products with the constraint type = dropdown type value)
in each row of the repeating group i have two more dropdowns with product options (brand and size) that were created with data from table products where type = dropdown type and product = repeating group row value (distinct product).
Then, i have an input for quantity in each row and finally a text box that calculates the price (from table) * quantity.
I also wanted to see a grand total after the repeating group, but i think the only way is with the plugin people are talking about: BDK.
Am I doing something else wrong?
By the way, my real idea is to create a kind of dynamic survey, where only one row is shown at one time, the next just beng presented after the user fills the previous row. Any idea about it?
Thanks in advance, guys!

[prajakta.markale]

Thanks, it worked for me!!!