List in a list from two separate tables

[reggieneo]

what is the correct expression to do this?
the result should be a single value from Table 1 column [Check Number] that is not existing in Table 2 Column Check Number].
Basically Comparing two lists and extracting a single value that is not in the other.

Thanks

[Suvrutt_Gurjar]

reggieneo:

should be a single value from Table 1 column [Check Number] that is not existing in Table 2 Column Check Number].

You may wish to elaborate on the above a bit.

In general, you could try following based on the description given

(Table 2 [Check Number])- (INTERSECT(Table 2[Check Number], Table 1 [Check Number]))

help.appsheet.com

INTERSECT()

Items common to two lists

[Marc_Dillon]

Or perhaps:

ANY( Table1[Check Number] - Table2[Check Number] )

[Suvrutt_Gurjar]

Wow ! Simple and compact one Marc.

[reggieneo · Accepted solution]

Gents,
I have come up with this:

(Order Details[Check Number] - LIST())

(Month Finance[Check Number]- LIST())

always grateful

[Suvrutt_Gurjar]

Great to know you got the solution.

Is it possible to update how you are constructing the second part of the expression that is LIST()

[reggieneo]

i got it in this link: List Subtraction | AppSheet Help Center

[Suvrutt_Gurjar]

Thank you.

I mean how you have constructed the second list LIST() in the expression, manually or through an expression.

[reggieneo]

sorry, not sure if i got your question correctly:
you mean this? (in red)

[Suvrutt_Gurjar]

Okay, got it now. Thank you very much @reggieneo. I am sorry the picture you shared in the previous post was not visible earlier on my pc and I posted without refreshing my browser to see the contents.

Nice approach you used to solve your requirement.

[Marc_Dillon]

Suvrutt_Gurjar:

Okay, got it now.

I certainly don’t!

@reggieneo, is that 2 different expressions or 1? How does that meet your listed requirements?

[reggieneo]

Marc,
it is 1 expression and yes , met my requirement.

(Order Details[Check Number] - LIST()) - (Month Finance[Check Number]- LIST())

these are the unique items in my table 1 and table 2 list:

the expression gives me “88888” which I need to put in table 2 list. indicating that i have a new check number .

[Marc_Dillon]

reggieneo:

(Order Details[Check Number] - LIST()) - (Month Finance[Check Number]- LIST())

Ahh, that makes more sense, Your previous display of the expression did not include the subtraction in the middle.

Although this expression will still output a List of values, not a single value. It just so happens in your shown example, there is only 1 value in that List of values. Is this how it will always be?

In either case, I’d suggest wrapping the entire thing in ANY() as I posted above in order to pull a single value from the List.

[reggieneo]

yes, it is setup that when there is a new check number in table 1 it will add to table 2. will always be 1 new,
i will still use your suggested expression in the future. first time as I seen that way before.

thanks much