Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 15, 2021 03:41 PM
I have two fields - a rollup field with 3 integer values and a currency field with a single value.
I want to divide the currency field by each value in the array to, hopefully, get a new array that includes the output of each calculation.
This seems like an obvious problem to do on an array but I cannot find the formula to perform the task.
Can someone point me in the right direction? I’m brute forcing this and it sucks.
Jul 15, 2021 03:49 PM
Welcome to the Airtable community!
Airtable does not have array handling functions to do this. Instead, you need to “roll-down” the currency field to the linked records (with a rollup field), then have the linked records do the division, and finally roll up the quotient.
Depending on the math you are doing, and if you only need to see a final number, you could have the rollup sum the integers and then divide the sum by the currency field.
Jul 15, 2021 04:07 PM
This is a confusing explanation, but I will give it a shot.
Jul 16, 2021 12:42 AM
What @kuovonne is saying (as I understand it): do the math on the individual record level (field “number”, field “currency”, field “currency/number”) and get the results in the other table via rollup field on the “currency/number” field, using SUM(values)
formula in that field.
Jul 16, 2021 10:56 AM
This is way more complicated than it seems like it needs to be, so let me give some visuals to explain why the answer confuses me.
I’ve got four interlinked tables related to this question:
One is the rate-skills table. Each individual can have multiple rates because they have multiple skills (company specific data is hidden but the left field is the primary field which is text and the right hidden field is a linked field which links to the rates table)
Second is the rates table. This is just the rates in order - a list of entries of prices which link to the skills table above
Third is the staff table - this is the most complex table and here staff are assigned skills and I have a lookup for rates. (Note, the w9 and role are also links to other tables but they don’t touch the problem)
Then I have the responses table, which includes each person’s answer for expected pay and a rollup of their rates (which is being pulled from the lookup of their rates in the staff table (by linking each response to their entry in the staff table) which in turn is pulling from two nested lookups).
My understanding of your suggestion is that it involves X steps:
Point being after an hour of trying to understand the answer I did the following instead
Tedious, but it took me about 15 minutes and didn’t result in a lot links that I would need to clean up after this report was delivered.
If I’m totally misunderstanding your answer that’s fine, but I would say that is because the answer assumed a level of knowledge about how to do fancy stuff with rollups that, if I had it, would probably result in me not asking the question in the fireplace