Using a calculation in a Substitute formula

[Zion_Brock]

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”

Is this even possible?

[kuovonne]

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.