Merging two Enumlist (Ref) and remove duplicates

teambelair · 03-02-2020 09:24 PM

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:

Any help is appreciated. Thank you.

Alex

Aleksi

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

teambelair

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

Bahbus

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

teambelair

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

Bahbus

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

teambelair

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

Brandt_Litton

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

teambelair

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

Brandt_Litton

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.

teambelair

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?

Bahbus

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

teambelair

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.

Bahbus

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

teambelair

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

teambelair

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

Bahbus

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.

teambelair

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

Bahbus

Try:

Bahbus

Try:

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

teambelair

Thanks. I copied just a part of the filter. There are already other conditions.

I don’t think the issue come from the context(device). I do have the same one for my test.

Is there a way to know exactly the list that gets return from the lookup? Some sort of debug print ability.

Bahbus

Using the test button on test button on that expression should show you.

teambelair

Doesn’t work either. Thanks though.

Bahbus

Well, Context(“Device”) based stuff can kind of be difficult to debug. Because, the browsers UUID changes fairly easily, and when you’re playing around in the editor, you’re on the browser. But I believe, I’ve also seen reports in general of issues using IN() as the only condition of a slice. Don’t know if that ever got fixed.