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

Solved Solved
0 15 1,242
1 ACCEPTED SOLUTION

This is working!!!

View solution in original post

15 REPLIES 15

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

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

Yep.

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.

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.

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?

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

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.

This is working!!!

Top Labels in this Space