Nested Lists in Extractions

Hey, this is something I've dug into recently, so I have a few answers.  Lists that depend on lists that depend on lists that depend on lists is 100% a thing you can do in Tessitura.  Unfortunately, there's really nothing stopping you from accidentally creating lists that have looping dependencies (List A uses List B, List B uses List C, List C uses List A).  I have an idea about that (shameless self promotion)!

List criteria in Lists should detect and forbid list dependency loops

https://community.tessituranetwork.com/ideas/i/ideas/list-criteria-in-lists-should-detect-and-forbid-list-dependency-loops

So that can likely cause you problems if it happens, and the deeper your nesting the higher the chances.

The second thing is that if you are trying to manage the number of Lists in your system, say by deleting lists over a certain age, you will have to worry about deleting a List that another List depends upon, and there's no real tool for deducing that (although Last Used Date might help).  Also, during the v16 migration a lot of old Lists are likely to become broken by changes to Memberships, and with a lot of List dependencies, figuring out which Lists will be affected downstream is going to be difficult.  More shameless self promotion:

Better Management for Old Lists and Extractions/Segmentations (and Output Sets?)

https://community.tessituranetwork.com/ideas/i/ideas/better-management-for-old-lists-and-extractions-segmentations-and-output-sets

Now something that might actually be useful for you: before we transitioned to using Contact Permissions, we had a series of contact suppression lists for various communication types, each of which relied on a set of lists which in turn relied on a set of lists (so three levels deep), which would then be included, typically, as a suppression row in an Extraction.  So you have three layers of dependencies with maybe six to ten lists in total, all "Dynamic", and therefore regenerated prior to each count and extraction run.  Because many of these lists would be shared between different communication types there was a modest percent chance that they would be part of two separate Extractions' suppressions being run simultaneously, and I found cases where Extraction output was incorrect because between the regeneration of a list and it's actual application to the suppression another Extraction had started regenerating it, and as a result when the list was referenced it was only partially full.  This was really an extreme case, but I suppose a large consortium that shared certain lists might stumble upon it as well.

Beyond that, I'm pretty sure you're not going to hit a limit ever, and if you did it would be around 200 (the usual recursion limit in MS SQL).  Even our overwrought system only seems to have produced about 5 or 6 levels of reference.

Lastly, because I'm on a roll, beware of Dynamic Lists and Analytics:

Analytics Should Honor Dynamic Lists

https://community.tessituranetwork.com/ideas/i/ideas/analytics-should-honor-dynamic-lists

Thank you Gawain Lavers for the info, this is extremely helpful! I've upvoted both ideas listed as I think both would be helpful, specifically some type of archiving system for lists and extractions. We were able to dig into this further and according to documentation, SQL allows recursion up to 32 levels deep. As you mentioned, I don't foresee getting past 5 levels deep, so I think we are fine.

Thank you again!

Gawain Lavers said:

Unfortunately, there's really nothing stopping you from accidentally creating lists that have looping dependencies (List A uses List B, List B uses List C, List C uses List A).  I have an idea about that (shameless self promotion)!

We use naming conventions and list categories to indicate which lists are intended for use as criteria, and degrees deep, so theoretically there should only ever be a one-way feed of information. But, the potential for loops is certainly worth noting - thanks for highlighting this.

The inability to identify list dependencies is a definite problem! Once or twice I queried T_List.Criteria, for a specific List ID, but it's not a tenable solution and doesn't account for the use of lists in extractions.

On that topic, I previously upvoted this suggestion (which I see you also weighed in on)
https://community.tessituranetwork.com/ideas/i/ideas/what-references-a-list?_ga=2.32185021.2053618148.1689181023-2086228388.1678117458

That's right, I had a feeling there was something funny about that number!  I was getting the 200 number with recursing CTEs, which is how I was running down our list dependency chains, so I guess they have a different limit.

I paid for this query in blood, use it wisely...

with formatted_value_lists as (
	select REPLACE(REPLACE(RTRIM(LTRIM(le.value1)), '(', ''), ')', '') as str_list, le.*
	from T_LIST_EXTRACTION as le
	where le.keyword_no = 59 --list
), initial_dependent_list as (
	select
		x.list_id as initial_list_id,
		x.extraction_flag as initial_extraction_flag,
		CONVERT(int, sl.Element) as dependency_list_no,
		x.*
	from formatted_value_lists as x
		cross apply dbo.FT_SPLIT_LIST(x.str_list, ',') as sl
), dependent_recursion as (
	select
		1 as dep_level, f.initial_list_id, f.initial_extraction_flag,
		f.unique_id as criteria_id,
		f.list_id as current_list_id, f.extraction_flag,
		f.dependency_list_no,
		case when f.initial_list_id = f.dependency_list_no then 'Y' else 'N' end as loop_detected
	from initial_dependent_list as f
	union all
	select
		dep_level + 1, y.initial_list_id, y.initial_extraction_flag,
		x.unique_id as criteria_id,
		x.list_id as current_list_id, x.extraction_flag, --i don't think I need these really?
		x.dependency_list_no,
		case when y.initial_list_id = x.dependency_list_no then 'Y' else 'N' end as loop_detected
	from initial_dependent_list as x
		inner join dependent_recursion as y on y.dependency_list_no = x.list_id
			and x.extraction_flag = 'L'
			and y.loop_detected <> 'Y'
)

select
	dep_level,
	initial_list_id,
	initial_extraction_flag,
	criteria_id,
	current_list_id,
	extraction_flag,
	dependency_list_no,
	loop_detected
from
	dependent_recursion
;