Constraining results of a Data Type for counting where Data Type is connected to Data Type that has a child Data Type

I have a Data Type (named Score) where I’m trying to get a count of unique elements of Scorer’s counted. However, I want to first add a constraint before I get to that count.

Here’s the database structure:

Data Types
Score (Fields: Score, Scorer)
Scorer (Fields: ScorerName, Project)
Project (Fields: ProjectName)

Scorer has a many to one relationship to Project
Score has a many to one relationship to Scorer

If I had a field named “Project” connected to my Project Data Type constraining is easy. However, as I want to keep this correctly structured as would normally be done, I’m instead trying to:

Constrain to a Project but I get there via the relationship of Scorer to project. When I’m trying to add the constraint; however, I’ll have multiple Scorers for a project. This is kicking my ass.

I start with Search for Scores :each item’s Scorer:unique elements:count

I’m fairly certain this is correct. However, it is within adding a constraint within Search for Score that I’m stuck. What should be my Type and what should I enter in after clicking to add a constraint. I can’t simply say Project = (the Project Name I want to limit to) because my Score table doesn’t have Project Name, it has Scorers where in the Scorer table is a Project Name (that is tied to the Project table).

Seems like your Score should have a Project field, no?

What does this mean? A count of a certain Scorer’s what?

No. The project is connected to Scorer. The Scorer table is connected to Project in a field Named ProjectName in the Scorer table. This is best practice for database structure (outside of bubble world).

I almost refuse to not structure it a way different from what I’m doing because that literally is the great value of relational databases. Otherwise we could just all create one giant table with all the fields we want and have a giant mess.

You need to use an advanced filter in this situation, @bryan.holmes.

Add the :filtered operator after Search for Scores, and when you add a constraint to the filter, scroll to the bottom of the dropdown and select Advanced. At that point, you should be able to construct the project-related constraint. FYI… advanced filters happen on the client (browser) side, so the search is going to return all of the things in the Score data type to the browser, and then the filter will be applied.

1 Like

Note that Search for Scores :each item’s Scorer:unique elements:count will not be an array of numbers but a single (scalar) number that is the count of ALL unique Scorer’s that are attached to any Score. (That is, it will be the SUM of what you are assuming you will get.)

So this approach is already a non-starter (as there is no way in vanilla Bubble to get the listwise count that you actually want).

You’re trying to do something that my Count Each Field/Sort action in Floppy can do though (try it out and if it doesn’t do what you need, I’ll add an option that makes that so). For a discussion of this issue, see this video:

… posted originally in the Floppy thread here:

THAT DID IT! Thank you so much. I would have never figured that out. Hopefully this helps someone in the future too.

2 Likes

Did I misunderstand your issue? (Serious question: I don’t see how the expression in question would yield anything like what it sounds like you wanted to get.) Perhaps you did just want the sum? :man_shrugging:

It did. To be honest, I didn’t follow your answer.

There are no numbers to sum in the field I’m counting so I’m fairly certain it’s working as expected, that is, that it is counting my unique things (DISTINCT records in SQL speak).

I hope this helps you.

1 Like

This topic was automatically closed after 70 days. New replies are no longer allowed.