Conditional dropdown with PIN NUMBER

[desmond_lee]

Hi Guys

Need some help. Is there a why to droplist a list of assign Part Number base on the PIN NUMBER. See bloew:

The below assign PN has Col A unique PIN NUMBER assign to each and every vendor. Table name QAssign

The other table (table name SQuote) is where the Vendor fill in a form key in the PIN Number to pick in dropdown form base on the PIN NUMBER so as other vendor cannot be seen by him 

Filter the drop down by PIN NUMBER

I try expression but no luck

[WillowMobileSys]

When you use in an expression something like "Table Name[Column]", this returns a list of all values in that column for that table - its a LIST of values.  Thus, you can't perform a comparison against it like this - "[Column] = Table Name[Column]".

However, you can use the IN() function to check if the value appears in the list.  So the expression I believe you want is this:

SELECT( QASSIGN DATABASE[QASSIGN ID] , IN([PIN NUMBER},  SQUOTE DATABASE[PIN NUMBER]))

[desmond_lee]

It doesn't work. What I did.... 

So I pick A2 in the below table and key in the PIN NUMBER 600493_d5ef77fea and the expected dropdown list from the QASSIGN ID is SAQ_TEMP-150071610-2023.2.12 & SAQ_114G0-032285-01-2023.2.12. 

I key in the expression SELECT(QASSIGN DATABASE[QASSIGN ID],IN([PIN NUMBER],SQUOTE DATABASE[PIN NUMBER])) in the below valid_if 

The result from the drop dow form is blank

If everything is ok the form should drop down only thise two QAssign ID SAQ_TEMP-150071610-2023.2.12 & SAQ_114G0-032285-01-2023.2.12.one selected to be fill in the below table

[WillowMobileSys]

Sorry,  I forgot an important part.  For any fields from the Form you need to explicitly reference the Form row.  This is done using the keyword [_THISROW],  So the expression needs to change like this:

SELECT( QASSIGN DATABASE[QASSIGN ID] , IN([_THISROW].[PIN NUMBER},  SQUOTE DATABASE[PIN NUMBER]))

[desmond_lee]

SELECT( QASSIGN DATABASE[QASSIGN ID] , IN([_THISROW].[PIN NUMBER}, SQUOTE DATABASE[PIN NUMBER]))

There is a curly bracket. Is this a typo error? And I put it like this by following the help article even though not exact e.g. and edit like SELECT( QASSIGN DATABASE[QASSIGN ID] , IN([_THISROW].[PIN NUMBER],{SQUOTE DATABASE[PIN NUMBER]}))

The result still the same i.e. no dropdown at the form

[WillowMobileSys]

Sorry, yes the curly brace was a typo.  It should have been a square closing bracket. You do NOT want to use curly braces as you have shown.  It makes the result a LIST of LIST which will not match.  

So the correct expression is (carefully reviewed):

SELECT( QASSIGN DATABASE[QASSIGN ID] , 
        IN([_THISROW].[PIN NUMBER],  SQUOTE DATABASE[PIN NUMBER])
)

[desmond_lee]

Hi Thank very much unfortunate it work partially. It drops everything ID instead only match PIN NUMBER. Ler's see below :

1. Enter the expression as per your recommendation its shows OK. See below:

2. Let's take the 2nd ROW,A2 that is PIN NUMBER 600493_d5ef77fea as the testsing. The expected drop down result are QASSIGN ID - SAQ_TEMP-150071610-2023.2.16.14.2.43 and SAQ_114G0-032285-01-2023.2.12.14.3.41 because Row 2 and Row 3 have the same PIN NUMBER. Refer to table below.

See below when PIN NUMBER 600493_d5ef77fea is entered all the 5 QASSIGN IDs shows up

[desmond_lee]

If above QASSIGN ID is correct the data should be properly populate in another table as show below that is in another tab,SQuote Database, and in another file name, S_Quotation Database V1

[desmond_lee]

Will do this another way do this security issue. This question conside closed and I will show the new way if successful

[WillowMobileSys]

I think it's just a matter of adjusting the expression to your needs.  It's not clear to me what you intentions are.  Previously, I simply provided an expression that was somewhat logically equivalent but syntactically correct based on your original expression.  

You will have better luck if you describe, in words, what you need the expression to do in addition to providing the images.  It seems like you want to filter rows into the dropdown but I am not clear how both the QAssign Database and SQuote Database tables play a part in that filtering.  Be sure to clearly define which table your sample table images below to.

[desmond_lee]

Hi Willow or any experts

Thanks for responding let's give it another shut. The expected result is to filter the row in the Table QAssign Database which the PIN NUMBER in Col A of Table QAssign Database match to the PIN NUMBER that the user key in the Quote Form PIN NUMBER below :

The user will key in a PIN NUMBER at the Quote Form, then the expression will DO  a match betwen the keyin PIN NUMBER to the list in Column A PIN NUMBER of Table QAssign Database (File name, Quotation Database V1) and filter the result as drop down only matchin list at the Quote Form in QASSIGN ID. The user will select of these QASSIGN IDs and fill the rest of the info, hit the save button and the data will be saved to the target Table SQuote Database (File SQuote Database V1)

To cut it short Fill In Quote Form - SOURCE : QAssign Database (compare Key) - Select one of the match QASSIGN ID at Quote Form - Save Form Date  - TARGET : SQuote Database