Sumproduct with two different lists

Questions
1

Good morning!

I need to cross-reference data from two different lists stored in the database.

In one list I have the price, in another list I have the quantity that will be multiplied by the price.

The objective is that the user selects a product, the quantity and the system makes a quote in several different stores that have the same product with different prices, obviously. That is, it is a price comparison function.

To organize this, I created a repeating group of the stores (where the prices are) and an internal repeating group, inside the store, where the products are => in this cell I calculate the unit value of the product X the quantity (available in the list of purchases)

It happens that the bubble does not allow getting values ​​within the internal repeating group and when I calculate the form through sumproduct for each product and each quantity, unfortunately the bubble cannot agglutinate the products by quantity, although there is information in the database of both lists (or I couldn’t get it to work). What the bubble does is add and multiply according to the precedence in the list, i.e. cell 1 of list 1 times cell 1 of list 2.

If the list has only one quantity or they happen to be in the same sequence, the result is correct, however, if they are in a different sequence, the result is wrong.

My pains and possible solutions:

1 how to make sumproduct identify the product in both lists and only then do each item?

2 how to add inputs within the internal repeating group to display the summed result in the stores’ repeating group?

Note: I’ve already tried using the Orchestra plug-in, list repeating group and math.js, none of them can concatenate the lists and the problem persists. In the case of the orchestra, it only works through the click of buttons, which makes the UI terrible.

Help please

2

I’m just taking some guesses because I might be doing something like this in the future…

When you are doing this price comparison, is this making a new data type like a Quote or anything? I feel like if you press a compare button, it should make a “Quote item”, with fields like Quantity and the actual Product. Then it could have one more field called “Item total”, after changing the Quantity input it would trigger a calculation of the Item total. Then you can sum all the Item totals together? And all the quote items would fall under a specific Quote, and you could have a quote per store? Just thinking out loud I haven’t done any of this in practice

3

It sounds like you might be hitting the limits of your database structure. I had similar problems configuring my e-commerce checkout. I solved it by adding a new data type to accommodate the unique quantities. So, the calculation itself becomes a data type that is connected to the user.

And then I used backend workflow to clear all entries of the new data type that are older than 24 hours. Not ideal, and maybe not the solve for what you’re doing, but hopefully it’s helpful.

4

I’m trying my best to avoid creating new items in the database, but it’s the only solution I can see at the moment.

5

I hear you, it’s definitely not the ideal choice. And there have to be other ways to solve with a custom state or something, but I definitely tried for a few days and resorted to the DB out of frustration.

Maybe someone with more skills will weigh in…

6

I don’t fully understand the request so it’s hard to figure out which piece is the struggle, although I do see you gave a pretty thorough explanation…

Take a look at this and see if it gives you any ideas: Saturday Showcase Show-off

7

The idea is that this green number presents the result of the sum of the product on the side, which is its own mix, aggregated with the price that the market defined (which is another list), aggregated with the quantity that the current user defined (which is another list of products and quantities).

The best way would be to simply sum the results within the internal repeater group, where there are two products, but I can’t access the result.

image
image

image
image411Ă—831 94.7 KB

In the example we have

RG 1 = Repeating group 1 (commercial establishments that have the product the user wants to quote)
RG 2 = Repeating group 2 (unit price of the product in each establishment x the quantity of products that the current user has defined)

L1 = list 1 => products
L2 = list 2 => product price in each supermarket
L3 = list 3 => amount of products the user wants

I want to know the result of the sum of the following multiplications that take place inside each cell (product = value x current user defined quantity)

8

Not really a solution, but more of a comment: This is a very annoying issue with Bubble that really catches folks off-guard when they run into it.

The main problem is that vanilla Bubble provides very limited capabilities for iteration and Repeating Groups are not actually one of them. The fact that you can do what I would call “display math” in a Repeating Group doesn’t make them useful as iterators, as @agcmateus is discovering.

The fact is, RG’s are a display element and, in a code-based application, we would of course first do our computations and then display them in a repeater.

RG’s cause folks to conflate computation and display. (And while I respect other plugin devs who have attempted to solve this issue the “backward” way – by plucking values out of the dom and sending them to Bubble – that’s just… well… backward.)

In these sorts of situations, what you should do is do the computation and then display it. But Bubble makes this difficult (nigh-impossible in vanilla) due to the lack of a :forEach or :map type expression operator for lists, no built-in way to iterate over a client-side workflow(s), and (sometimes equally vexing) lack of support for custom objects in-the-page.

My free List Shifter plugin provides a couple of workarounds for these problems. It has support for iterating over a list via a workflow (which – let me be clear – is stupid slow for simple things like mathematical operations) via its ITERATE Actions and also via an Action called PROCESS List (which provides a funky, but no-code approach to creating iterative functions that operate more-or-less at native JavaScript speed). See this reply and video link in the main List Shifter thread for simple examples of how to use both of these features:

Unfortunately, PROCESS List is greatly underutilized since Bubble doesn’t let us plugin devs create super-great interfaces and it seems people get overwhelmed with its apparent complexity, but it’s actually really simple to use.

I’m currently working on (and am almost code-complete) on a new commercial plug-in that’s sort a more friendly approach to the sorts of things that List Shifter does (with more robust error checking and other niceties and a whole bunch of unique functionality). It introduces a new (and more flexible) approach to iterating over workflows, as well as an action that lets you simply write a little JavaScript and execute it as a .map(), .forEach(), etc. over some List.

Based on this particular thread (and others like it), I’ll probably add an Action or actions for doing “simple math” on a List and sending it to various plugin outputs. Still, Element plugins are not the ideal interface for stuff like this (but necessary at the moment), and one does wish that we could return values to the workflow for client-side Action plugins (as we can do in server-side action plugins), or access to an Expressions API for extending the expression editor. :man_shrugging:

2 Likes
9

I appreciate the reply and realize that I will not find a suitable solution. I have a Java script code that searches for an input and adds the results, the problem is that I don’t have the necessary knowledge to get the data from the repeating group, because I’m still starting my studies in js.

I believe that a plugin that looks for values ​​in an input (albeit hidden) and displays the result outside the repeater group would be extremely useful, as it would have application in many cases and we wouldn’t need to make a sumproduct with so much difficulty.

10

I guess while I’m griping about this subject (which I’ve done repeatedly), it boggles my mind that Bubble (by which I really mean the Bubble expression editor) is still half-baked as an array-oriented language.

I mean, what’s so hard about:

list_1 => (this_item * list_2's :item# this_index)

Where the fat arrow indicates a map function that passes (item, index, and the full_array [in this case the array of price’s]) to the argument, which (obviously) in this case would return a List (array) of the product of each item in list_1 with the corresponding item in list_2.

Bubble already does map functions of this type anytime you reference some_thing's field, it’s just there’s no syntax for simple functions.

Also, don’t get me started about the results of :count on field of some list of Things. So much stupid.

11

Don’t misunderstand me, you can do this quite easily with List Shifter’s PROCESS List action (go watch the video) without needing code.

You could also do it (in what I think is a more laborious way) with things like Toolbox’s List Item Expression element.

It’s just super painful to explain this to people or to provide a specific examples, but here’s an already existing example page that shows how to do this with List Shifter:

You’ll find a button there to open the editor for that page, as well.

Edit: Also, here’s a painfully detailed explanation of that example, as well: List Shifter: Reverse, Rotate, Swap and ITERATE (Loop) Over Bubble Lists | Now at v1.4: Adds Numeric Option, GET INDEX Action - #438 by keith

12

Guys, the problem was following the list. What did I do to solve the problem?

I created a field called code in the product, to refer to the precedence in which it was created, then I copied this field called code for the value and for the amount and when searching the data in sort by I pull the given code for the three lists. As a result, the lists will always be queued in the same order and that’s it. The bubble will always be able to bring the correct result to sumProduct