[chaley]

Originally Posted by Sandwich

I may be overthinking this, I admit, but can anyone point me in the right direction here?

You are very close to what I would do.

I would make three custom columns:

• The year as you described.
• A "universe specifier", a text custom column or perhaps a column with a fixed set of values, for example "Dune" or "Star Wars".
• A column built from other columns that chooses how to format the year number based on the universe specifier.

An example template might be:

program:		 
	universe = field('#text');
	year = raw_field('#myint');
	first_non_empty(
		if universe == 'Star Wars' then
			if year < 0 then
				strcat(floor(multiply(year, -1)), ' BBY')
			else
				strcat(year, ' ABY')
			fi
		fi,
		if universe == 'Dune' then
			if year < 0 then
				strcat(floor(multiply(year, -1)), ' BG')
			else
				strcat(year, ' AG')
			fi
		fi
	)

Ignore the lookup names in the first two lines. Those are columns I already had.

This screen capture shows the result.


Alternatively, and just for fun, it can be made more readable using the new stored template feature. I first define a stored template called 'universe_year' that formats the year:

program:
		arguments(year, lt, gt);
		if year < 0 then
			strcat(floor(multiply(year, -1)), ' ', lt)
		else
			strcat(year, ' ', gt)
		fi

I next change the original template to use the stored template, resulting in:

program:		 
	universe = field('#text');
	year = raw_field('#myint');
	first_non_empty(
		if universe == 'Star Wars' then
			universe_year(year, 'BBY', 'ABY')
		fi,
		if universe == 'Dune' then
			universe_year(year, 'BG', 'AG')
		fi)