PARENT/ CHILDREN Function on a drop-down menu

Loletan

What If?

GRID #1

1st Column ----------------------2nd Column ----------------------3rd Column (indented rows from PARENT)

Primary column----------------Used as a PARENT field-------Used as CHILD group (rows)

How can I make a function on another grid?

GRID #2

2nd Column ----------------------3rd Column

Is linked to Parent field ------NEED TO WRITE A FUNCTION

DESIRED FUNCTION (2nd grid/Column 3): automatically generate a drop-down menu of indented rows (the CHILD group).

Is this possible?

What is the Function?

Loletan

Image3.jpg

To further explain:

Please look at the attached image (GRID1) as reference.

1.   Parent is South Africa

2.   South Africa Children Group: Free State, Gauteng, KwaZulu-Natal, Limpopo, Mpumalanga, North West Province, Northern Cape, Western Cape.

3.   Mpumalanga Children Group: Kruger National Park, Pligrim’s Rest, Sabi Sand Reserve, Genesis of Life, Panorama Route, Shangana Cultural Village, Sudwala Caves.

So, on grid number 2

I linked the COUNTRY column to GRID2/COLUMN2 and selected South Africa in the field= GRID2/COLUMN2/ROW1.

1.   Once the Country is selected the desired field data for: GRID2/COLUMN3/ROW1 = A dependent drop-down menu of South Africa CHILDREN (all Region fields allocated to South Africa).

2.   Let’s take it a step further. If the user selects the child “Mpumalanga” in field GRID2/COLUMN3/ROW1.  How can get GRID2/COLUMN4/ROW1 to automate another dependent drop-down menu of Mpumalanga Children?

Genevieve P.

Hi @Loletan

It's currently not possible to auto-populate and create child rows beneath a Parent on one sheet based on the same Parent in a second sheet. You could use a formula to gather all the Child Row values into one cell using a JOIN formula, but it would not be able to parse them down into multiple rows beneath the current Parent, does that make sense?

An alternative would be to set up a helper template sheet with every possible Parent and Child relationship identified, then when a category is needed, Copy the Parent row over to your main sheet. This will automatically bring all the predefined Child rows with it (see: Copy Rows to Another Sheet).

Please let our Product team know of your request by filling in this form, here!

Thanks,

Genevieve

Loletan

@Genevieve P. Thank you for your comment and I appreciate your help.

I am looking for a way to link categories/sub-categories column fields to run reports. For example, entering zip code 90210 will define a property as being in Beverly Hills. So, in this case the defining parent/child fields are city, state,and zip code. I have several contact list for different industry types (restaurants, clubs, transportation services, etc) each with a defining address fields. Let's say I would like to run a report to list all industry contacts (from several sheets) in the region of Los Angeles.

So I am looking for a way to link a defined location AREA to all the sheets.

Genevieve P.

Hi @Loletan

Thank you for clarifying this is for a Report! Reports look at rows individually without assessing the Parent/Child relationship, so in this instance, you'll want to bring data from the Parent rows into each Child row in order to use that as a filter.

Since you have multiple levels, I have added in 2 "helper" columns into your sheet for the Report. The first one bring down the Parent Country into its first Region level.

Ex: "South Africa" on the same row as "Free State"

The second helper column takes that value and adds it to the Parent Region as well, if the current row is a Destination row. (Note: I've colour coded the rows to identify the Grandparent, Parent, and Child rows):

My 1st Helper column formula is like so:

=PARENT(Country@row)

My 2nd Helper column formula is:

=IF(PARENT([1st Helper]@row) = "", [1st Helper]@row, PARENT([1st Helper]@row) + " / " + PARENT(Regions@row))

Now in my Report, I can filter by if the Report Helper column contains the word "South Africa" and pull in both the Region and Destination rows. Or, if I only wanted to see the Regions (and no Destinations) then I would pull a Report based on the 1st Helper column.

Is this closer to what you're looking to do? The alternative would be to drag-fill the Parent Row information down into its child rows, without using formulas.

Cheers,

Genevieve

Loletan

@Genevieve P. Can you share the example sheet?

Genevieve P.

Hi @Loletan

No problem, here's a published link to view the sheet: https://app.smartsheet.com/b/publish?EQBCT=d771d107203d489d9cb835e97f51cf8d

Note I have it currently set up both with the two helper columns and with the suggested drag-fill of the details down into the child rows. You would only need one or the other for the Report.

Let me know if you'd like to see an example Report, too.

Cheers,

Genevieve

Loletan

I don't understand any of this but thank you :)

Genevieve P.

No problem!

The first helper formula simply takes the Parent details from the Country column and brings that to each Child Row:

=PARENT(Country@row)

This means that the text "South Africa" appears in the "1st Helper" column so it's on the same line as "Free State" do you see that?

Then a Report can search for that value, because it exists on the same line!

I'll break the second formula down for you, as that one is a bit more complex:

=IF(PARENT([1st Helper]@row) = "", [1st Helper]@row, PARENT([1st Helper]@row) + " / " + PARENT(Regions@row))

This says, if, in the Parent row, the 1st Helper column is blank (meaning that this is a row with a REGION but no DESTINATION), then simply return the same thing as in the 1st Helper column (which will return the COUNTRY into the REGION row).

Otherwise, if the 1st Helper column is not blank, then that means the current row also has a Region that we need to bring in, along with the Country.

So, we take the PARENT of the 1st Helper (the Country) and we add it to the PARENT of the REGION column, to have a cell that contains both the Country and Region on the same row or line as the DESTINATION.

Does that make a bit more sense?

Here's why we would do this... now you can create a Filter (or a Report) that uses the Report Helper column as the criteria:

I just have to look to see if the Report Helper contains the text "South" and it brings back all rows associated with South Africa (both Regions and Destinations).

If you have more questions, it would be helpful to know a bit more what your end-goal is. Another screen capture of your sheet with dummy data showing the desired result would be very helpful!

Cheers,

Genevieve