Confused by joining tables to summarize data

Need help Database
1

Being a developer can sometime have its drawbacks with Bubble and their database as I feel you need to think differently sometimes. Here’s what in my head I want to achieve in Bubble:

I have 2 tables, Projects & Proposals where a project has many proposals. All I am trying to do is get a sum of the proposal amount where the project stage is “published”. So in SQL I would do this:

SELECT sum(amount)
FROM Proposals
INNER LEFT JOIN Projects on Proposals.ProjectsID = Projects.ProjectsID
WHERE Projects.stage = ‘PUBLISHED’
AND Proposals.Is_Latest = Yes

I have tried all different approaches but just cannot get this to work. Any suggestions with this?

2

You need to do a search for Proposals, who’s project is in:

Then do a search for Projects who’s Stage - published

then use: each item’s amount: sum

3

Thanks Adam, that makes sense. Not sure how many workflow units thats going to cost me :slight_smile:

4

You can keep a running count every time a proposal is published IF that helps with WUs.