Select Values that are in the same row but different column after dropdown value is selected.

[Jpaulsen]

Morning, 

Was wondering if someone could help me out here. I'm trying to wrap my head around a formula I am trying to get to work but seem to have some problems. I got two drop down list at the top of my form. Once the first on is select, the second one appears with a select values based on the first drop down. Now what I am trying to do is, once both drop downs are complete, I want to gather a list of values from another column that are associated to the second dropdown because it's in the same roll.

I have tried using _thisrow but seems to not work. I have to let appsheet sheet know that _thisrow = "value"  I'm trying to IFS, SELECT AND NOT(ISBLANK( and(

First Drop Down [Type of Filter], Second Drop Down [Part Number]

IFS(SELECT(Equipment[Unit],NOT(ISBLANK([part number], and(

[Type of Filter] = "Engine oil Filter",
UNIQUE(Eqipment[Engine oil filter]),

[Type of Filter] = "Fuel Filter",
UNIQUE(Eqipment[Fuel Filter]),

[Type of Filter] = "Water Separator Filter",
UNIQUE(Equipmet[Water Separator Filter])

)))))

Thank you in advance, 

Jason

[Aurelien]

Hi @Jpaulsen 

---

@Jpaulsen wrote:

Once the first on is select, the second one appears with a select values based on the first drop down.

---

You probably want to have a look to this document:

Dependent drop-downs - AppSheet Help

---

@Jpaulsen wrote:

Now what I am trying to do is, once both drop downs are complete, I want to gather a list of values from another column that are associated to the second dropdown because it's in the same roll.

---

---

@Jpaulsen wrote:

First Drop Down [Type of Filter], Second Drop Down [Part Number]

---

When reading at the expression you provided (and thank you for having tried), I have the feeling that the Equipment data structure could be improved.

Usually, in the next field you would provide a FILTER or SELECT expression in the Valid_If field.

Usually, I would suggest something like this:

FILTER("Equipment",
  AND(
    [Type of Filter]=[_THISROW].[Type of Filter],
    [Part Number]=[_THISROW].[Part Number]
  )
)

Because you said you wish to catch values from the same table but another column, I'm afraid there is any confusion. Can you please share:

- the data structure of the table to which the form is attached to

- the Equipment table structure

---

[Jpaulsen]

Good Day Aurelien,

Sorry for the mass confusion, The values that I want to catch is from another table, and only want the catch values the are affiliated to the part number once it's determined in that it's under the "Type of Filter" Column. That drop down is looking for exact column. "Engine Oil Filter", Fuel Filter", Water Separator Filter", and so on.  Then the second drop down display all the filter numbers in that the column.  I tried the formula about, it works, but gives me all the values in the unit column. Where I need the ones that use the part number. I also add some screen shot photos. I hope this help. I have be strungling with this for a while now. 

Thank you in advanace.

[Aurelien]

Many thanks for the screenshot.

I think there is a little misunderstanding on my side (because of barrier language, not your fault), so here are my suggestions.

What about that ?

IFS(
  AND(
    ISNOTBLANK([Type of Filter]),
    ISNOTBLANK([Part Number)
  ),

SWITCH([Type of Filter],

  "Engine oil Filter",
  SELECT(Equipment[Engine oil filter],
    [_THISROW].[Part Number]=[Engine oil filter]
  ),

  "Fuel Filter",
  SELECT(Equipment[Fuel Filter],
    [_THISROW].[Part Number]=[Fuel Filter]
  ),

 "Water Separator Filter",
  SELECT(Equipment[Water Separator Filter],
    [_THISROW].[Part Number]=[Water Separator Filter]
  )
)

)

or

IFS(
  AND(
    ISNOTBLANK([Type of Filter]),
    ISNOTBLANK([Part Number)
  ),

SWITCH([Type of Filter],

  "Engine oil Filter",
  FILTER("Equipment",
    [_THISROW].[Part Number]=[Engine oil filter]
  ),

  "Fuel Filter",
    FILTER("Equipment",
    [_THISROW].[Part Number]=[Fuel Filter]
  ),

 "Water Separator Filter",
    FILTER("Equipment",
    [_THISROW].[Part Number]=[Water Separator Filter]
  )
)

)

(I think you are looking for this second option)

For reference:

SWITCH() - AppSheet Help

FILTER() - AppSheet Help

[Jpaulsen]

Hello Aurelien, 

I tried both of these formulas but I keep getting an error "Number of opened and closed parentheses does not match." I can't seem to fix it. Any suggestions?

Thank you again for the helping me out.

[Aurelien · Accepted solution]

IFS(
  AND(
    ISNOTBLANK([Type of Filter]),
    ISNOTBLANK([Part Number])
  ),

  SWITCH([Type of Filter],

    "Engine oil Filter",
    FILTER("Equipment",
      [_THISROW].[Part Number]=[Engine oil filter]
    ),
  
    "Fuel Filter",
      FILTER("Equipment",
      [_THISROW].[Part Number]=[Fuel Filter]
    ),

   "Water Separator Filter",
    FILTER("Equipment",
     [_THISROW].[Part Number]=[Water Separator Filter]
    )
  )
)

Hi @Jpaulsen 

I forgot one square bracket.

Here is the correct expression.

I suggest you install the Chrome's extension "AppSheet Toolbox". It's immensely useful.

EDIT: the SWITCH part is not exactly correct, as the "default-case" part is missing. I added the correct solution in a post later.

[Jpaulsen]

Thank you Aurelien, 

OMG, I added false at the end and changed the select column. Just what I was looking for. I just want to say thank you so much, it's going to be a long code once I am done adding the other columns to it but it's what I have been pulling my hair out for a month. Thank you again. 

[Aurelien]

Hi @Jpaulsen 

I'm glad to help, and glad you found it by yourself eventually. Sorry for answering that late, I was out of office for the weekend.

Just in case someone else reads this thread: here is the final correct expression.

I previously forgot the default case.

IFS(
  AND(
    ISNOTBLANK([Type of Filter]),
    ISNOTBLANK([Part Number])
  ),

  SWITCH([Type of Filter],

    "Engine oil Filter",
    FILTER("Equipment",
      [_THISROW].[Part Number]=[Engine oil filter]
    ),
  
    "Fuel Filter",
      FILTER("Equipment",
      [_THISROW].[Part Number]=[Fuel Filter]
    ),

   "Water Separator Filter",
    FILTER("Equipment",
     [_THISROW].[Part Number]=[Water Separator Filter]
    ),

    FILTER("Equipment",
     [_THISROW].[Part Number]=[columForDefaultResult]
    )
  )
)