I have an array in a record:
“Bob Smith - 30%, Susan Bee - 50%, Alan Watts - 20%”
and I want to change this array from percentages to money payouts, in accordance to a currency field, for example total income of $1500. How can I calculate within an array so that the end result would be something like this:
“Bob Smith - $450, Susan Bee - $750, Alan Watts - $300”
Airtable is not setup to do this with a single formula field.
It is hard to advise without seeing more of the structure of your base. However, I’m guessing that you have a linked record field that is linked to three people and the percentages are in the linked records, but the currency amount is in the parent record.
If you have a one-to-many relationship between the total income record and the people records, you need to pass the total income back to the people records using a lookup or rollup. Then have the individual people records calculate their own payouts. Then have a lookup/rollup of the payouts in the total income record.
On the other hand, if you have a many-to-many relationship between the total income table and the people table, you will need to create a join/junction table, and then use the process above.