Ref only showing parts the location does not already have

AppSheet AppSheet Q&A
,
1

I am create a form for someone to request a new part for their location.

I have a list of parts - “Parts” and I have many branch locations that can have a variety of parts.

I want to have a ref to parts in a new form that only shows items that the Facility/location does not have.

This formula shows me all the parts that the facility DOES have:

FILTER(Parts,[Branch location]=[_THISROW].[Facility])

if I put <> it will not work because duplicate parts can be at different facilities. So the <> showed every part.

Parts table:

Loc part

1 x

2 x

2 y

3 y

So if they pick location 1 I would only want to display a choice of Y
if they pick location 3 the would have a chose of x
and if they choose 2 they would not have any parts to pick from.

2

Please try

UNIQUE(Parts[Key column] - FILTER(Parts,[Branch location]=[_THISROW].[Facility]))

3

I get this error: Arithmetic expression ‘(Parts[RECID]-SELECT(Parts[Unique Name],([Branch location] = [_THISROW].[Facility])))’ cannot convert ‘List of Ref to table ‘Parts’ of Name’ to ‘List of Number’

Should I be using a not(in ?

4

I had requested to test

The above suggestion is assuming , you are trying to reference Parts table into some orher table.

Could you mention reason for converting the second subpart of expression to SELECT() from FILTER().

Please note the expression needs to be used in valid_if of the ref column.

5

yes, I tested your code and it gave an Arithmetic expression error because of the - sign.

I am trying to do a ref to the Parts table but, I want to only show Parts that the facility does not have.

(Facility and location have the same list of values)

6

Could you share the screenshot of the recommended expression with the error ?

Also please rwtain the column type details in the screenshot.