Crowdfunding - Monthly payment for every user based on the project he invested

[CreateGo]

Hi dear community 🙂

I try to build a crowdfunding platform for real estate using Airtable.

Users can choose the project to invest in and make a payment. 

I am able to see all users and in what project they invested in

Here are all transactions users made

The struggle is to pay every user based on the amount they invested in each project because each project has a different dividend % and users can invest in 2-3 different projects in different periods. I somehow need to add to their profile what they earn every month. 

Any ideas on how to make it a reality? 🙂 

Thank you 🙂

[TheTimeSavingCo]

If you could create a simplified version of your base with examples of what you want to happen exactly I would be happy to help with this.  I got kind of confused watching the youtube video I'm afraid, sorry

Best of luck either way!

[CreateGo]

Thank you Adam for replaying. I also was concerned I made it too complicate )))

I edited the main post and simplified the system. Can you check, please?

[TheTimeSavingCo · Accepted solution]

Ahh I see, yes, I understand now.  Thank you very much!

If I were you, I would do the following:
1. In "Transactions", create a lookup field from the "Investment In" linked field to retrieve that investment's "Monthly Dividends %"
2. In "Transactions", create a formula field that will multiply the "Amount" by the "Percent" to get the dividend amount
3. Create a new table called "Dividends" or something that has a linked field to "Users" as well as a "Currency" field called "Amount" or something
4. Have an automation that will run once a month with the following actions:
4a. "Find Record" action that will look for all the records in the "Transactions" table
4b. Repeating group action using the results from 4a as a list
4c. "Create Record" action that will, for each record found, create a new record in the "Dividends" table.  It will:
  4c i. Link itself to the correct "User" record
  4c ii. Use the value from the field from step 2 and paste it into the "Amount" field

With this method, once a month, you will end up creating one record per investment each user invested in, along with the amount they earned from that investment.

Note that this method will not work if you have more than 100 records in the "Transactions" table though, as the "Find Records" action can only find 100 records at a time.  There are other ways to accomplish this that you can try to explore, such as having a formula field that will update based on which month it is, and having an automation that will trigger based on that field updating etc.  While this works, it will consume as many automation runs as you have records in "Transactions", which may be a problem depending on which Airtable plan you're on

[CreateGo]

Success! 

After trying multiple times I managed to create it and learned a lot from you, thank you ))

Now I need to figure out how not to make a mess with numbers))
To show every user what he invested in each project, and what projects bring him the best results. Now they see all numbers in one field. And some invest in the same project 2-3 times.

[TheTimeSavingCo]

Ahh, yeah you're going to need another junction table for this where each record represents a single user linked to a single project.  That way you can sum up the multiple investments they've made in the project