No doubt this is database 101, but it’s driving me nuts!
Example table here.
On the WriterSplits_Results table, I would like Airtable to check the fields “Writers”, “Composers”, and “Arrangers” and return text based on the results.
In the “WriteSplitResults” field I’m using this formula to return results if there is data in the “@Writers” “@Composers” “@Arrangers” roll-up fields:
IF( Writers, "Writers: " & ARRAYJOIN(Writers) ) & IF( Composers, '\n' & "Composers: " & ARRAYJOIN(Composers) ) & IF( Arrangers, '\n' & "Arrangers: " & ARRAYJOIN(Arrangers) )
This results in duplicated information though, ie. returning:
“Writers: John Smith 100% (Sony)
Composers: John Smith 100% (Sony)
Arrangers: John Smith 100% (Sony)”
“Writer/Composer/Arranger: John Smith 100% (Sony)”
“Writer/Arranger: Bob Brown 100% (Universal)
Composer: John Smith 50% (Sony), Bob Brown 50% (Universal)”
You can see from the four songs entered that there are variables in how the Writer/ComposerArranger can be split between individuals.
I’m trying to work out how to get Airtable to check the “Writers” “Composers”, “Arrangers” fields and return test according to the results. I set up tests (WC_Test, WA_Test, CA_Test) but this seems clunky and I still can’t work out how to tell Airtable to calculate off those results. I’ve tried using nested IF, AND, OR formulas but I’m struggling to make sense of it.
Is it possible to have one formula field to calculate the variables and return the various results?
Any help greatly appreciated! Thanks,