Get all child and grand child task by Ref_row

[amufaamo]

Usually task and project has several hierarchy. Appsheet can take child task by Ref_row, but is it possible to take all child and grand child task?

For example, I have sheet like below

For example, United States includes California, and California includes Los Angeles. 

It means United States includes California and Los Angeles, so I want to take both California and Los Angeles by list.

Within is Enumlist, which can choose places

I made virtual column "include" like below. App formula is 

select(Sheet1[Place], in([_thisrow].[Place], [within]))

I could get "California" in "United States". But I also want to take grand child "Los Angeles". Is it possible ?

[1minManager]

Firstly, if i where doing this i'd organse the data tables as:

• Country
• Region
• City

So I think to do this with 1 table you might actually need 3 virtual columns.  The first, to get the first level down you already have.  Though really you should be selecting the ID column, such as

Select(Sheet1[UniqueID], IN([_ThisRow].[Place], [within]))

Next your going to need another table to look for items which are 'within' the records of the first table.  For example lets call this [Includes2]

Select(Sheet1[UniqueID],IN([Within],
	INTERSECT(
		[Includes][Place],
		Select(Sheet1[Within],TRUE)
	)
))	

Then to combine them together using  [Includes]+[Includes2]

This should more or less work.  But as I said above doing it this way is a bad design.

Simon@1minManager.com

[amufaamo]

Thank you so much. Actually, I want to make project management tool. Big project has high hierarchy and small project has few hierarchy. Number of hierarchy is not determined. Can this formula work even any hierarchy? 

So sorry my example was country. 

[1minManager · Accepted solution]

Yes.  But may I suggestion you talk with an Appsheet Dev like myself or one of the others so that you get your data structure right first time?