How to get list of values from another column that has another value found in its row.

[Jpaulsen]

Hello. 

I was wondering if someone can point me in the right direction here. These formula's are a little different the excel's. So I really frustrated on make a these formulas work. 

First formula is Select(equipment[unit], ( [_thisrow].[type of filter] = [Part Number].[type of filter]), True) it works but it pulls all the units that don't have the "part number" in its same row of information. I like to only get the units that use this part number. 

Second is. I lost on complete my formal in my valid if that if the value is not there that a I can input a new value. 

IFS(
[Type of Filter] = "Engine Oil Filter",
UNIQUE(equipment[Engine Oil Filter]),

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

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

)

Third with the second formula, how would I able to include a list and/or select?

Thank you so much in advance. 

[Steve]

---

@Jpaulsen wrote:

First formula is Select(equipment[unit], ( [_thisrow].[type of filter] = [Part Number].[type of filter]), True) it works but it pulls all the units that don't have the "part number" in its same row of information. I like to only get the units that use this part number. 

---

I don't understand what you're trying to do. Please explain your goal in plain language with no technical terms.

---

@Jpaulsen wrote:

Second is. I lost on complete my formal in my valid if that if the value is not there that a I can input a new value. 

---

Use Suggested values rather than Valid If.

---

@Jpaulsen wrote:

Third with the second formula, how would I able to include a list and/or select?

---

You already include lists: equipment[Water Separator Filter] is a list.

Construct a list from table-column references - AppSheet Help

[Jpaulsen]

Hello Steve, 

Thank you for your reply back. I have a master equipment list that breaks down what type of filter and their part number. In the master sheet the "type of filter" Eg Engine oil filter, Fuel filter. Hydraulic filter. Ect. Column list go on. I made it that way because of the many different type of filter are need for the that one equipment. Under each "type of filter" will have their one part number. Now some equipment could use that same filter. 

The main equipment info page works great. I've be struggling on another section call filter info. On this page I have it set kind of set up to do the following, bare with me on this. 

From Top to Bottom of the page. 

Description:               Value Box:

Type of Filter:             Engine Oil filter - This value with change on the type filter I want to lookup.

Part Number:             zz-2342 - this value with change also, as each part number different.

Unit(s):                         22,23,24,24 - this is where I like the value formula to show me what units use this "Type of Filter" once the part number is added. 

I've try multiple ways that I can think of but it seems simple in my head, but not when I try to make a formula. Because "Type of Filter" box (Dropdown list of different filter) in the filter info page it's a column in the main equipment date. When I use [_thisrow} in a select formula it looks for a column name "Type of Filter" in the sheet.  If type only [_thisrow] it will show me the selected filter from the dropdown list. 

So when I combine is a select value relationship Select(ledcor[unit], ( [_thisrow].[type of filter] = [Part Number].[type of filter]), false) it shows me all units even the ones that don't use that filter's part number. 

Now if I use: Select(ledcor[unit], [part number] = [_thisrow]) I get nothing. but if I use Select(ledcor[unit], [part number] = [Engine Oil Filter])  it give me the proper equipment that uses this filter and it's part number. But when I pick a different type of filter like Fuel Filter is shows me nothing as this formal is only looking for "Engine oil Filter" so I'm have issue using [_thisrow] to as if by itself in its on spot tells me what type of filter is listed at the top of the page. 

I've included screen shot of the main equipment data screenshot of area on my Appsheet.