CreateGo wrote:
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?
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
