I'm embarrassed to say I've completely stumped myself with this. I assumed it would be simple for me, or that I'd be able to use some views around Content elements to work it out, but neither is the case.
In the way that, with Content elements, you can attach them to any level of the production element tree and they will flow downwards, but also be overruled by more specific rules, I'd built a configuration table like this:
title_no
prod_no
prod_season_no
perf_no
[configuration information]
I then expected to produce a view that just had
that I could match to transactions. The idea is that if I have
title_no = 1, config = 'A'
and
perf_no = 5, config = 'B'
And title_no 1 filters down to perf_nos 5-8 I would get
I'm seeing a couple of views that pull together the Content elements using unions, but no plan for choosing which configuration to use, which I guess is done in the application.
Just to make sure I am understanding your question correctly, are you saying that the perf_no level configuration would override the title_no configuration, or that it would be together? Because, my understanding of how things like Content elements is that 5 would actually give you, Configuration: "B,A", and not just "B".
Yes, it should override, I only want the most specific rule.
One option I've been trying to avoid because I think it's messy and fundamentally performance unfriendly is to use COALESCE, i.e.
COALESCE(perf.configuration, prod_season.configuration, prod.configuration, title.configuration)
But maybe that's my answer? There's some ugliness there in that I have many columns of configuration data, and some might be null by design, but wouldn't want individual columns to come from different configuration. I suppose what I could do is run the COALESE on the id of the configuration row, and then rejoin the table based on that...
Yeah, I think that might be your best way out. At least, it is hard to say more without looking more specifically at the table(s) you are writing.
But, going the Content route where the table would be (inv_no,configuration_type,configuration_value), then, since, as I said above, Tessitura does not have a mechanism for prioritizing the configuration but simply returns all valid for a given perf_no (so "adding down" rather than "filtering down"), there is no mechanism to "copy" there, as it were, to get the specific configuration settings for that performance.
And I doubt that the performance using that form of a COALESCE would be THAT poor. But again, hard to say from where I am sitting.