In words, I’m trying to calculate the :average value of C for a given Thing A’s list of Thing B’s, where each Number C is a particular “Item #n” from a list.
The expression I’d like to write is would be Thing A's list of Thing Bs' list of Number C's:Item#2:average, and I can express all of that right up the end where I want to put :average, but the only options are:
I know I can work around this with yet another database write to my Thing B table (to write each Number C into a separate field), but I’d like to avoid that. Am I thinking about the above expression incorrectly, or should this be possible in Bubble?
The expression you have currently… The list of Thing B’s List of Number Cs is that full list of Number Cs in your screenshot. Item #2 is simply the 2nd in that list, so you’ve drilled down to a single Number C, which is why you cannot average.
What you want to do is filter Thing B’s list of Number Cs to extract the Number C’s you need and average that, but since these are number values (and not Thing C), not sure how you’d filter it.
You could probably do it by running an API workflow on the list of Thing Bs to pull out each one of their Number Cs item #2, but that might not be the most efficient for generating a calculation.
Just typing out my thoughts. Wanted to at least share why your expression isn’t working the way you want.
Thanks. That’s interesting, and helpful. This would be really tedious but in theory since I know that each Thing B will always have exactly and only 12 Number C’s, I could write an expression to add, for example, Item #2 + #14 + #26 + #38, and so on to some absurd number, then divide by the number of Thing B’s to get the average.
Really wish there was a cleaner way to do this. This isn’t really my root problem, of course, just another byproduct of slow database write speed (the reason I’m stuffing these 12 Number C’s in a list in the first place).
I’m not sure that I understand the structure, but it seems that the Numbers in your case are fields in Thing B and not a separate (third) type (table). Are you saying that you want to average the second number of Thing B, i. e. the second field?
If this is the case, you firstly construct a list of these fields in this way:
Thing A’s Thing Bs’ Number C2:average
(firstly, for each thing A you get a list of Things B, from those you take column C2 which gives you a list of numbers, which are then averaged)
It might be that your formula is OK but your data structure is not
Right, the C’s are not another data type, just numbers. They would ideally be another data type, or at least stored as part of a key-value pair in the list field that’s in Thing B. But when I originally had them as a separate data type it took insanely long to write the twelve records to the database (and do other downstream calcs). So that’s why I’m here.
All right. But are these numbers all in one field in Thing B? Or in separate fields, named C1, C2 or similar? If they are in one field, how to you distinguish them? I wouldn’t count on their order. If all things B have exactly three of these numbers, then I suggest you create three fields in Thing B type and the solution is as I suggested before, with three expressions for three different Cs, and you get three averages:
Thing A’s Thing Bs’ Number C1:average
Thing A’s Thing Bs’ Number C2:average
Thing A’s Thing Bs’ Number C3:average
There are 12 C’s for each Thing B, and yes I am relying on them being written and stored in a particular order, which I know is risky but seems to be holding true. Creating twelve new fields in Thing B table won’t work for me generally because I need to do sorts/calcs across those numbers as well, but I may end up doing both the list and separate fields (or reintroduce the third data type) for this new feature.
OK, I understand. In classical relational model you would have Thing C with a value field (number) and ‘type’ (1, 2, 3 … 12), but this ‘purity’ might not be suitable for you, as you mentioned.
Yeah, that was my original setup. Just too slow. As in: one of my user-facing workflows took about 90 seconds and after switching to this number list it is now instantaneous.
