SELECT() changes item separator in different view

I created a virtual column, which works perfectly, except in a detail view the item separator changes to default. See video below

Hi @Dan_Oliphant,

Just wish to mention at the start that I could not watch the video properly for some reason, as I am watching it on a small tab device currently. So following may not exactly apply. It sounds that a list that shows as “Apple / Banana/ Cherry” in form view looks like “Apple , Banana, Cherry” in detail view.

My testing showed that the the list type columns revert to default comma seperator in detail view.

So, as a workaround, if you are using the said field only for display purpose in both the form and detail views, then you may wrap your SELECT() with a SUBSTITUTE() and please do make the VC a TEXT type instead of LIST column type.

SUBSTITUTE(YOUR SELECT STATEMENT GOES HERE ,",","/")

1 Like

Thanks. Quick follow up question. Can virtual columns be included in a workflow email in the same format <<[virtual_column]>>?

1 Like

Yep.

1 Like

This works great. Thanks everyone. Is there a way to order the initials. I have a field crew chief. Its either NULL or crew chief. I would like it to be ordered by crew chief then NULL.

1 Like

Here is the expression. SUBSTITUTE(SELECT(crew_dispatch[crew_ini], ([job_uid] = [_THISROW].[unique_id])),",","/")

ORDERBY(SUBSTITUTE(SELECT(crew_dispatch[crew_ini], ([job_uid] = [_THISROW].[unique_id])),",","/"), crew_dispatch[chief]) gives back an error

Could you please confirm, if you wish to just display the crew chief list alphabetically as compiled by the SELECT() expression in this thread earlier and whether it is key field or not?

You may try the following expression , if the field is not a key field. This sorts the list reverse alphabetically ( Z to A) and any nulls are at the end. It could be sorted alphabetically A to Z) but then nulls display in the beginning. We could circumvent this but then I believe expression may become lengthier.

SUBSTITUTE(SORT(SELECT(crew_dispatch[crew_ini], ([job_uid] = [_THISROW].[unique_id])), TRUE) ,",","/")

Please post back if you are looking for something else.

It is not a key field. And alphabetical would be fine. I will try this and let you know.

I was hoping to sort another field other than crew initials. it would be the field in crew_dispatch, column is called chief.

Could you please share more details?

In general SORT() can be applied to sort any list , manually composed or constructed through SELECT() statement.

2 Likes

Here is a video, i do better showing rather than explaining in words

I’m getting no audio in the video. Is that me, or is it silent?

1 Like

the microphone must have been off. I will recreate it.

1 Like

Could you please try the following expression
SUBSTITUTE(
(
SORT(
SELECT(crew_dispatch[crew_ini], AND([job_uid] = [_THISROW].[unique_id], [Chief]=“Crew Chief”)),
TRUE) +
SORT(

SELECT(crew_dispatch[crew_ini], AND([job_uid] = [_THISROW].[unique_id], ISBLANK([Chief])))
, TRUE)
)
,",","/")

Based on your video and earlier description in post , assumption is [Chief] enum column has two possible values , NULL or Blank and “Crew Chief”. The column name and associated condition is highlighted in the expression above. Those may need minor changes based on enum values of the column if those are different than assumed in the expression.

Also the expression could not be tested. It might have a parenthesis error or some other syntactical error but have tried best to ensure it need not have.

The approach is first make list (through SELECT()) and sort that list of members who are crew chief and then make another list (through SELECT()) and sort that list of members who are not crew chief.

Then add those two lists and apply “/” list seperator through SUBSTITUTE(). In the final SUBSTITUTE() expression theresult becomes TEXT type from LIST type. So the column needs to be TEXT type.

2 Likes

This is working!!!

2 Likes