Inline listview with unique records (with equal values in only selected columns)

[Kasper_Egelund]

I’m stock…

In an event management app I have three tables. Event, Activities, Contacts.

Each event has activities refs. And each Activities has contacts refs.

Every contact [contact] can work in different position during [role] an activity.

I’m trying to create a virtual column in my event table that can show a distinct list that show each combination of [contact] and [role] only once (and still keeping the reference to the contact).

My contacts table has columns with id, a reference column to my address book, role, and working hours/plan - ref to activity.

ex

id - contact - role - start - end - activity(ref)

xx - kasper - guard - 8pm - 10pm - play1

xx - kasper - cleaning - 4am - 6am - play1

xx - henrik - guard - 7pm - 11pm - play2

ss - kasper - cleaning - 4am - 7am - play2

xx - kasper - guard - 7pm - 10pm - play3

xx - kasper - cleaning - 3am - 6am - play3

Activity table

ID - Activity - event(ref)

xx - play1 - event1

xx - play2 - event1

xx - play3 - event1

My Event table

ID - Title - VC activities - VC activity contacts

xx - event1 - (play1, play2,play3) - (kasper-guard,kasper-cleaning,henrik-guard)

I have no problems with creating VC activities. I can also create a virtual column that shows all the contacts (kasper-guard,kasper-cleaning,henrik-guard,kasper-cleaning,kasper-guard,kasper-cleaning). But I only wan’t to show a distinct list based on the name and the role column not taking the time or other columns into account.

I’ve tried by creating a slice in the contacts table using

IFS(
(
[_THISROW] = MAXROW(
“contacts”,
“_RowNumber”,AND(
[_THISROW].[ac_activity] = [ac_activity],
[_THISROW].[ac_contact] = [ac_contact],
[_THISROW].[ac_contact_role] = [ac_contact_role]
)
)
),
True
)

But this only gives me the entire list of the event.

I’ve tried to implement a filter, unique,select within the virtual column in my event table but I can’t figure out to make it work.

[Syamsudin]

Hi @Kasper_Egelund

I’m trying to give you some inspiration, is this the kind of app you want?

Contact

Contacts detail

Activity & event

Detail Activity & event

Form sampel

This is just an inspiration, I hope you like it

[Suvrutt_Gurjar]

Welcome to the community @Kasper_Egelund

Your exact requirement could be clearer. Could you give an example of what exactly you are expecting the VC to show in the Events table?

I mean how the VC should look for the app user?

Edit: On rereading your post, it sounds that you would want the contacts in the following format

@Kasper_Egelund

My Event table

ID - Title - VC activities - VC activity contacts

xx - event1 - (play1, play2,play3) - (kasper-guard,kasper-cleaning,henrik-guard)

You also mentioned the following

@Kasper_Egelund

I have no problems with creating VC activities. I can also create a virtual column that shows all the contacts (kasper-guard,kasper-cleaning,henrik-guard,kasper-cleaning,kasper-guard,kasper-cleaning).

May we know any specific reason UNIQUE() function is not working on the above list?

This would help the community to suggest the best possible solution.

[Kasper_Egelund]

This is how it looks like right now:

I’ve grouped the list of contacts by role

“BB_Moderator - Facilitator” and “BB_Tekniker“ are the Roles,

What I would like it to look like is so that if the name - role has multiple record they only show up one.

The inline list/view is from a VC based on a slice

the slice is created with an expression

“IFS(
(
[_THISROW] = MAXROW(
“activity_contact”,
“_RowNumber”,
AND(
[_THISROW].[ac_activity] = [ac_activity],
[_THISROW].[ac_contact] = [ac_contact],
[_THISROW].[ac_contact_role] = [ac_contact_role],
[_THISROW].[ac_status] = ‘ec633ead124’
)
)
),
True
)”

Hope this makes a little more sense?

[Kasper_Egelund]

I’ve tried to create another VC

the expression goes like this:

“UNIQUE(
SPLIT(
CONCATENATE([VC Related activity_contacts][VC ac unique TEST]),
“,”
)
)”

the “[VC ac unique TEST]” is a concatenation of name and role.

the result:

The count number “11” is correct… but I don’t see the result?

[Steve]

Change the App formula expression to:

CONCATENATE(">>>", [VC Related activity_contacts][VC ac unique TEST], "<<<")

Change the column type to Text.

[Steve]

@Kasper_Egelund

IFS(
(
[_THISROW] = MAXROW(
“contacts”,
“_RowNumber”,AND(
[_THISROW].[ac_activity] = [ac_activity],
[_THISROW].[ac_contact] = [ac_contact],
[_THISROW].[ac_contact_role] = [ac_contact_role]
)
)
),
True
)

Can be more simply expressed as just this:

(
  [_THISROW] = MAXROW(
    “contacts”,
    “_RowNumber”,
    AND(
      [_THISROW].[ac_activity] = [ac_activity],
      [_THISROW].[ac_contact] = [ac_contact],
      [_THISROW].[ac_contact_role] = [ac_contact_role]
    )
  )
)

Your use of IFS() is unnecessary.

[Steve]

Just to make sure here:

The value in the activity(ref) column should be the key column value of the row of the activity table (presumably the ID column), NOT the name of the activity (the Activity column).

[Kasper_Egelund]

@Kasper_Egelund

UNIQUE(
SPLIT(
CONCATENATE([VC Related activity_contacts][VC ac unique TEST]),
“,”
)
)

Hi Steve,

Thanks for the reply.
If changing it to text I’ll loose the ref and only see it in plain text.

While 022eea27 is the ref of the contacts name and 003f2fdde8 is the role.

[Kasper_Egelund]

Yes. That’s correct.

[Kasper_Egelund]

Thanks. That’s easier to read

[Kasper_Egelund]

@Kasper_Egelund

UNIQUE(
SPLIT(
CONCATENATE([VC Related activity_contacts][VC ac unique TEST]),
“,”
)
)

Maybe this can clarify:

I’m trying to do what would be similar to (and I know this is not possible as such…)

Maxrow([VC Related activity_contacts],[VC Related activity_contacts][ac_id], IN([VC Related activity_contacts][VC ac unique TEST], List([_THISROW].[VC TEST 2])))

To get a list of each of the latest of ids ([ac_id]) where the column value [VC ac unique TEST] matched a value in [VC TEST 2].

The expression is used in a VC in the same table as where [VC Related activity_contacts] and [VC TEST 2] are located to ref values in the table where [ac_id] is located.

[Kasper_Egelund]

@Kasper_Egelund

IN([VC Related activity_contacts][VC ac unique TEST], List([_THISROW].[VC TEST 2])

Thanks for taking the time to come with this inspiration to the app. I appreciate it. Used it to try other ways to find a solution to my problem

[Steve]

@Kasper_Egelund

While 022eea27 is the ref of the contacts name and 003f2fdde8 is the role.

The list in your screenshot, that I requested as Text enclosed in >>>, <<<, is a list of key column values in the ac_unique slice? Please provide a screenshot of the slice data that shows the key column and at least one (but preferably more) of the values in this list.

[Kasper_Egelund]

Finally!!! Solved it. It was my expression to define my slice. I was so convinced I had to do the filtering/uniqueness on the event level in my VC in my event table and not in the activity_contact table.

So my expression ended up like this and is now working as expected:

“(
[_THISROW] = MAXROW(
“activity_contact”,
“_RowNumber”,
AND(

  [_THISROW].[ac_contact] = [ac_contact],
  [_THISROW].[ac_contact_role] = [ac_contact_role],
  [_THISROW].[ac_status] = 'ec633ead124',
  [_THISROW].[VC ac-Event] = [VC ac-Event]
)

)
)

“

Thanks for the feedback and ideas that ended up with pushing me in the right direction.