Merging two Enumlist (Ref) and remove duplicates

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:
2020-03-02_23-27-17

Any help is appreciated. Thank you.

Alex

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

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"
	))
)

2020-03-03_14-54-01

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

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().

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

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

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

@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”)

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.

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?

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

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



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.

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

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

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

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

@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!

Try:

1 Like

Doesn’t work either. Thanks though.