Return results by testing three fields


#1

Hi All,

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)

Instead of:
Writer/Composer/Arranger: John Smith 100% (Sony)

or
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,


#2

@JoSpencer

I came up with the following which

  • checks special case ( compares Arrayjoins ) when Writers = Composers = Arrangers
  • if it is true then it prepares your special string
  • otherwise it uses your formula to process the results

IF(ARRAYJOIN(Writers) = ARRAYJOIN(Composers), IF(ARRAYJOIN(Writers) = ARRAYJOIN(Arrangers), "Writer/Composer/Arranger: " & ARRAYJOIN(Writers), “” ), IF( Writers, "Writer: " & ARRAYJOIN(Writers) ) & IF( Composers, ‘\n’ & "Composer: " & ARRAYJOIN(Composers) ) & IF( Arrangers, ‘\n’ & "Arranger: " & ARRAYJOIN(Arrangers) ) )

Formula part which I inserted in the beginning needs to be modified for error checking i.e. if Writers data is there then only check ARRAYJOIN(Writers) etc.

If this works for you then if statement needs to be modified for each special condition e.g. when writers = composers, composers = arrangers , writers = arrangers etc

Let me know if it works for you !

Thanks