Can anyone recreate this spreadsheet functionality?

Bubblers, I need assistance. I typed out a novel trying to explain the puzzle, but then I realized I could build what I want in a spreadsheet in 10 seconds, so here it is:

Can anyone figure out how to re-create this functionality in Bubble? I’ve labeled everything in the spreadsheet. When an input’s value is changed, it creates this domino effect of value changes - every field update is dependent on the same, already updated field of the Thing before it except for the first Thing, which is dependent on the input value. See formulas in Row 2.

I have a repeating group that is set up in this way. I’ve played around with scheduling API workflow on a list, but I didn’t stick with it long enough because I could see that the delay alone won’t work for the user experience. The number of Things involved will be in the hundreds per user and it basically needs to behave (nearly) as instant as it does in this spreadsheet. Of course, I’m expecting some kind of delay no matter what. I just haven’t been able to successfully re-create the correct value change. Is this possible?

If I can clarify anything, please ask. A bubbler I’m working with and I have been stuck on this for a while. I will send an edible arrangement to anyone who can re-create exactly what this spreadsheet is doing.

Gaby

1 Like

In a repeating group, you can reference any other cell by adding/subtracting an offset to the current index. Once you have this new index, you can reference the cell in question with “item#:”.

Hey @Scott, yeah, I’m definitely doing that, but there’s a timing issue when I want to change multiple cells from a single trigger. When I enter a new value in the primary input (A2 in the sheet), all Field 1 values should be changed. Those values, however, are dependent on the cell before it.

If Cell 2 is part of Cell 3’s value and Cell 3 is part of Cell 4’s value, I can’t update Cells 3 and 4 at the same time. 3 needs to be updated first before 4 can be updated. This is where I felt like scheduling an API workflow on the list might have answered, but it’s not looking good.

Hey Gaby! Just to throw an idea out there… what if you didn’t store the F1 and F2 values (and only stored the changeable, green values). This would mean you would only store A2 and each User’s F3 and F4. Then when needing to display a User’s F1 and F2 fields (not stored in the database), those would be displayed dynamically through an equation? For example, if you were to display these values in a text element on a User’s profile page:

Conditional statement: If Current Page User’s Order is “1”

User 1:
F1 = A2 (stored database value) + Current Page User’s F3 + F4
F2 = Current Page User’s F3 + F4

If Current User’s Order is greater than “1”

User 2:
F1 = A2 (stored database value) + SearchforUsers (constraint, Order = Current Page User’s Order – 1): firstitem’s F3 + F4, + Current Page User’s F3 + F4

F2 = Current Page User’s F3 + F4

User 3:
F1 = A2 (stored database value) + SearchforUsers (constraint, Order = Current Page User’s Order – 1): firstitem’s F3 + F4, + Current Page User’s F3 + F4

F2 = Current Page User’s F3 + F4

User 4:
F1 = A2 (stored database value) + SearchforUsers (constraint, Order = Current Page User’s Order – 1): firstitem F3 + F4, + Current Page User’s F3 + F4

F2 = Current Page User’s F3 + F4

Then if you needed to show the F1 and F2 values in a repeating group, you could have it sorted by Order, and then the dynamic expressions would be similar:

If Current cell’s User’s Order is “1”

User 1:
(Through conditional formatting)
F1 = A2 (stored database value) + Current cell’s User’s F3 + F4
F2 = Current cell’s User’s F3 + F4

If Current cell’s User’s Order is greater than “1”

User 2:
F1 = A2 (stored database value) + SearchforUsers (constraint, Order = Current cell’s User’s Order – 1): firstitem F2 + F3

F2 = Current cell’s User F3 + F4

(the process continues for User 3 and User 4)

This would make sure that the F1 and F2 values are always accurate, and would adjust if A2 is changed, or if any individual User adjusts their F3 and F4 fields. But I’m not sure if this would work for you if you have to store F1 and F2.

Faye - this is very close to the structure I had for a while, but the more I moved through it, the more I needed to save values.

Also, I just ran your User 4 formula through what’s currently in the spreadsheet to sample Orders > 1: it doesn’t add up :frowning: They all result in the same number. Notice that for Orders > 1 , the formula for field 1 is previous User’s field 1 + current User’s field 2. Field 2 = current user’s fields 3 + 4.

The Thing I’m actually dealing with is also not the same as the Page thing. The Page is type Business. The RG (to keep things in English for the forum) is a list of Items. The database structure is:

Business

  • List of Items

Item (User in the sheet example)

  • Field 1
  • Field 2
  • Field 3
  • Field 4

I wanted to label it User in the sheet to be clear that this is a table of 1 data type. This is part of a financial app that will be saving a bunch of things no matter what. The values are pieces of financial data and the inputs are for adjusting the existing data as new information comes in (sales, expenses, projections, etc.). So, that’s why I have the black and green in the sheet to demonstrate what users will be able to modify and what will be “auto-generated” - but to your point, the auto-generated pieces don’t necessarily need to be saved. The green inputs do because they’ll need to display the last saved info when users log back in.

I sincerely appreciate the time you took to help figure this out. Classic Faye :wink:

1 Like

Preview: https://examples.bubbleapps.io/version-test/dependent_cells
Editor: https://bubble.io/page?type=page&name=dependent_cells&id=examples&tab=tabs-1
The solution ended up being much simpler than maintaining several groups. I would like to note that I had deleted an instance that wasn’t storing the next item in the linked list (originally the 0th item).

3 Likes

Hey @Scott,

This is looking good, but there are some key pieces that are still missing. First, what you do have working is when input is changed, all row values are updated and they calculate correctly off the inputs (current row’s modifier + previous row’s value).

However, the modifier must be made up of 2 additional changeable fields in the same row. So when either of those two fields are changed, the modifier would update automatically, which would in turn update that row’s value and all rows after … so all of that is triggered by 1 of the 2 changeable fields.

So close. I still have hope!

The “modifier” field I had placed is essentially the sum of the two fields I had omitted, so I believe that part can be added. For modifying all the rows after, just pass the row where the two fields were changed into the workflow and it should process it starting from there to the end. I’ll give it a shot in a bit.

Let me know if you get it. I understand that breaking down the modifier into 2 parts is straight-forward, so I guess the thing I want to highlight is that any time the modifier changes, its row (and following rows) should as well. Right now, if I change a modifier, the row value stays the same.

You really might almost have it!

I’ve updated the example to meet your specs. An observation I’ve seen is that some calculations are not immediate (since it’s scheduled); thus, the values may be delayed or incorrect. The issue is pronounced if an input (or series of inputs) is changed in rapid succession. While it works, it’s not something I would put into production at its current state, unless you restrict the user until all processing has completed.
Preview: https://examples.bubbleapps.io/version-test/dependent_cells
Editor: https://bubble.io/page?type=page&name=dependent_cells&id=examples&tab=tabs-11

6 Likes

Incredible. This looks like it’s doing exactly what my spreadsheet is doing. I’m going to apply this into the app and see how it does.

I fully agree with you re: quick edits and timing. I may have to put up a loading barrier or something, but you’re right. Will need to test extensively before releasing to users who will expect immediate cell feedback.

Scott, thank you!!! I’m super impressed with how quickly you put that together. I was really hoping someone with a fresh brain would look at this and say “oh, you just need to do this, silly.” Looks like you did it. Will let you know how it turns out. Many many thanks and I owe you one. :beers:

1 Like

@Scott ps if you wouldn’t mind leaving that page up for a day or two…?

I don’t mind at all. I’m too lazy to delete it anyway.

1 Like

No prob at all, Gaby! You’re totally right - my idea was not updating correctly once I tested it out! (Plus there were errors in what exactly I was adding up). I just recreated Scott’s solution - always seems so simple after the fact, every time! :sweat_smile:

Great solution @Scott! (I would not have thought of doing it that way!). Thank you for sharing the example. :slight_smile: :clap:

@Scott You are some logic wizard. It 100% works in the app. Thank you again. You can cash in on that edible arrangement whenever :wink:

I’ve seen that back and forth custom event thing before and feel like I’ve done it with showing loading screens, but it never crossed my mind for this with lists. Super cool.

You’re right about the spreadsheet being a good representation of the problem, as I admit that I didn’t read far down enough to notice Edible Arrangement offer. I had fun solving this one, as it pulled from a data structures class I took some time ago.

Scott,

You ARE a wizard rock star!

It’s my app that you solved a big problem for.

If you want something other than the Edible Arrangement, maybe something geekier, let us know! Okay?

George

1 Like

Feel free to make a donation of any amount to my colleagues at http://ocnep.org/. They have been doing great work there ever since they started it about two years ago.

2 Likes

Just sent them $50. Thanks again! @Scott @george

4 Likes

This is awesome! I have no idea about the functionality of the request or solution but the help-factor is sky high.

(Y)

3 Likes