Merging two Enumlist (Ref) and remove duplicates

AppSheet AppSheet Q&A
1

Hi All,

I am trying to merge 2 lists and remove its duplicates. I tried the following:

UNIQUE(
	[Teaching_Tips_1]+	
	LOOKUP(
		MAXROW(
			"Class_Observations"
		,
			"Modified On"
		,
			[_THISROW].[Student] = [Student]
		)
	,
		"Class_Observations"
	,
		"Key"
	,
		"Add_Objective_1_Teaching_Tips"
	)
)

and received this error:
Arithmetic expression '([Teaching_Tips_1]+ANY(SELECT(Class_Observations[Add_Objective_1_Teaching_Tips],([Key] = ANY(SELECT(Class_Observations[Key],AND(([Key].[Student] = [Student]), ([Modified On] = MAX(SELECT(Class_Observations[Modified On],([Key].[Student] = [Student])))))))))))' has inputs of an invalid type 'Unknown'

So i tried this:

UNIQUE(
	[Teaching_Tips_1]+	
	List(LOOKUP(
		MAXROW(
			"Class_Observations"
		,
			"Modified On"
		,
			[_THISROW].[Student] = [Student]
		)
	,
		"Class_Observations"
	,
		"Key"
	,
		"Add_Objective_1_Teaching_Tips"
	))
)

No error but the duplicates aren’t stripped away.

Note that this code is used in an action:

2X_b_b9dc5741011ad04db7789454e7da40159b12f039.png
2X_b_b9dc5741011ad04db7789454e7da40159b12f039.png690Ă—367 19.1 KB

Any help is appreciated. Thank you.

Alex

2

You need to use lists with the addition (+)… meaning LIST()+LIST().

3

Hi Aleksi,

I did try that and I still end up with duplicates.

UNIQUE(
	List([Teaching_Tips_1])+	
	List(LOOKUP(
		MAXROW(
			"Class_Observations"
		,
			"Modified On"
		,
			[_THISROW].[Student] = [Student]
		)
	,
		"Class_Observations"
	,
		"Key"
	,
		"Add_Objective_1_Teaching_Tips"
	))
)

2X_b_b7a9f2ed29a21bd679955992c020d6aeca64f158.png
2X_b_b7a9f2ed29a21bd679955992c020d6aeca64f158.png247Ă—353 15.7 KB

4

-LIST("") at the end. UNIQUE sometimes seems to fail in these circumstances.

5

Thx Dave. Doesn’t seem to work either in both case below.

UNIQUE(
	LIST([Teaching_Tips_1])+	
	LIST(LOOKUP(
		MAXROW(
			"Class_Observations"
		,
			"Modified On"
		,
			[_THISROW].[Student] = [Student]
		)
	,
		"Class_Observations"
	,
		"Key"
	,
		"Add_Objective_1_Teaching_Tips"
	))-LIST(" ")
)

UNIQUE(
	LIST([Teaching_Tips_1])+	
	LIST(LOOKUP(
		MAXROW(
			"Class_Observations"
		,
			"Modified On"
		,
			[_THISROW].[Student] = [Student]
		)
	,
		"Class_Observations"
	,
		"Key"
	,
		"Add_Objective_1_Teaching_Tips"
	))
)-LIST(" ")

I don’t think it matters but both lists contains keys of the table Teaching Tips that are generated with UNIQUEID().

6

No space in between the quotes. Just double quotes right next to each other.

7

Same. In both cases. Which case you wanted me to try?

8

– LIST() usually works for me. I’ve not tried the UNIQUE() function yet.

9

@Brandt_Litton @Bahbus
Is that what you were thinking about?
UNIQUE(LIST("a, b, c, d, e")+LIST("d, e")-LIST())

The current test data is similar but it keeps returning LIST(“a, b, c, d, e, d, e”)

10

Yes. But, like I said, I’ve only done that same thing without the UNIQUE() part of the expression. So for me it’s usually LIST(…)+LIST(…)+LIST(…)-LIST() to get rid of duplicates.

11

this sounds terrible but maybe I can do LIST(A)+LIST(B)-INTERSECT(LIST(A), LIST(B))

or can I wrap the LIST(A) and LIST(B) in a select and use the argument to remove the duplicate?

12

Does work:
LIST("a", "b", "c", "d", "e")+LIST("d", "e", "f")-LIST("") = {a, b, c, d, e, f}

13

Doesn’t work for me. Maybe the issue comes from this mismatch type.

2X_1_123d5f4f697bc100eccdf085a6fec7867555d0df.png
2X_1_123d5f4f697bc100eccdf085a6fec7867555d0df.png1508Ă—1503 138 KB

’
I have this error when I use INTERSECT(). I don’t get why since they are both pointing toward 2 fields that are EnumList and Ref of the same table.

14

Wait. Is [Teaching_Tips_1] an EnumList? That’s the reason it’s not working. LIST([EnumList Column]) I’m pretty sure takes the content of the EnumList {“test1”, “test2”} (2 items) and converts it into a new list of {“test1, test2”} (1 item). Then it’s {“test1, test2”} + {“test2”} = {“test1, test2” , “test2”} (2 items). That’s why UNIQUE() and the -LIST(“”) aren’t working. As far as the expression is concerned, they’re different.

So try these two options:

UNIQUE(
	[Teaching_Tips_1]+	
	LIST(LOOKUP(
		MAXROW(
			"Class_Observations"
		,
			"Modified On"
		,
			[_THISROW].[Student] = [Student]
		)
	,
		"Class_Observations"
	,
		"Key"
	,
		"Add_Objective_1_Teaching_Tips"
	))
)

If that doesn’t work, try:

UNIQUE(
	SPLIT([Teaching_Tips_1], ",")+	
	LIST(LOOKUP(
		MAXROW(
			"Class_Observations"
		,
			"Modified On"
		,
			[_THISROW].[Student] = [Student]
		)
	,
		"Class_Observations"
	,
		"Key"
	,
		"Add_Objective_1_Teaching_Tips"
	))
)
3 Likes
15

@Bahbus Thanks so much, I added the SPLIT for both columns and it works great

16

Just a question, why an EnumList of Ref isn’t a regular LIST?

17

Mostly because its a mishmash of two seperate column types. But this problem would occur even if the column was of the List type (I think). Either way, the list is “stored” as a string of text separated by comma (or a different delimiter if you define it). It would be equivalent to LIST(“A”, “B”, LIST(“A”, “B”)), but the editor can recognize that as a no-no.

1 Like
18

@Bahbus I think I am having the same issue with enumlist of Ref again.

  • On table A, I have a field [Scope Card] that is an enumlist Ref of table Gyms
  • On table B, I have a field [Gym] that is a Ref of a the table Gyms
  • The key of table Gyms is a UNIQUEID() String

I am trying to use the current filter on a Slice of table B:

IN(> [Gym]> ,> LOOKUP(CONTEXT(“Device”), “User_Profiles”, “Device”, “Card Scope”)> )

It always return false even with the correct data. So I tried:

IN(> [Gym]> ,> SPLIT(LOOKUP(CONTEXT(“Device”), “User_Profiles”, “Device”, “Card Scope”), “,”)> )

Still doesn’t work.

I did notice the filter working when I change the order of the values in the field Card Scope.

Any idea? Thx!

19

Try:

IN(> [_THISROW].[Gym]> ,> SPLIT(LOOKUP(CONTEXT(“Device”), “User_Profiles”, “Device”, “Card Scope”), “,”)> )

1 Like
20

Doesn’t work either. Thanks though.