Hi All, I wrote the following expression to t...

[Malaka_Jayaward]

Hi All, I wrote the following expression to the LongText type Virtual Column “Image Dates”, in order to get a CONCATENATED sequence of dates called in from a set of Date type columns, each date to be separated by a “,” only if those date columns are filled in by the user, and if a date column is not filled in, then there won’t be anything in “Image Dates” column. My issue is, the “Image Dates” virtual column still returns a value of (,) at the very beginning even before the user punches in any dates in the date columns.

This is the expression

=CONCATENATE(IF(ISNOTBLANK([USS Date]=TRUE),[USS Date],""),IF(ISNOTBLANK([USS Date]=TRUE),",",""),IF(ISNOTBLANK([CT Scan Date]=TRUE),[CT Scan Date],""),IF(ISNOTBLANK([CT Scan Date]=TRUE),",",""),IF(ISNOTBLANK([MRI/MRCP Date]=TRUE),[MRI/MRCP Date],""),IF(ISNOTBLANK([MRI/MRCP Date]=TRUE),",",""),IF(ISNOTBLANK([ERCP Date]=TRUE),[ERCP Date],""),IF(ISNOTBLANK([ERCP Date]=TRUE),",",""),IF(ISNOTBLANK([EUS Date]=TRUE),[EUS Date],""),IF(ISNOTBLANK([EUS Date]=TRUE),",",""),IF(ISNOTBLANK([PET Scan Date]=TRUE),[PET Scan Date],""),IF(ISNOTBLANK([PET Scan Date]=TRUE),",",""),IF(ISNOTBLANK([Biopsy/Image Guided Biopsy Date]=TRUE),[Biopsy/Image Guided Biopsy Date],""),IF(ISNOTBLANK([Biopsy/Image Guided Biopsy Date]=TRUE),",",""))

Can someone please help me to figure out the mistake I’ve made here?

[Steven_Coile]

I’m gonna make several suggestions in several posts, so bear with me…

First, lemme reformat your expression for clarity:

=CONCATENATE( IF(ISNOTBLANK([USS Date]=TRUE),[USS Date],""), IF(ISNOTBLANK([USS Date]=TRUE),",",""), IF(ISNOTBLANK([CT Scan Date]=TRUE),[CT Scan Date],""), IF(ISNOTBLANK([CT Scan Date]=TRUE),",",""), IF(ISNOTBLANK([MRI/MRCP Date]=TRUE),[MRI/MRCP Date],""), IF(ISNOTBLANK([MRI/MRCP Date]=TRUE),",",""), IF(ISNOTBLANK([ERCP Date]=TRUE),[ERCP Date],""), IF(ISNOTBLANK([ERCP Date]=TRUE),",",""), IF(ISNOTBLANK([EUS Date]=TRUE),[EUS Date],""), IF(ISNOTBLANK([EUS Date]=TRUE),",",""), IF(ISNOTBLANK([PET Scan Date]=TRUE),[PET Scan Date],""), IF(ISNOTBLANK([PET Scan Date]=TRUE),",",""), IF(ISNOTBLANK([Biopsy/Image Guided Biopsy Date]=TRUE),[Biopsy/Image Guided Biopsy Date],""), IF(ISNOTBLANK([Biopsy/Image Guided Biopsy Date]=TRUE),",","") )

[Steven_Coile]

The ISNOTBLANK() function returns a Yes/No value, either TRUE or FALSE. You don’t need to then compare its result to TRUE or FALSE. So we can simplify your expression by removing the redundant comparisons:

=CONCATENATE( IF(ISNOTBLANK([USS Date]),[USS Date],""), IF(ISNOTBLANK([USS Date]),",",""), IF(ISNOTBLANK([CT Scan Date]),[CT Scan Date],""), IF(ISNOTBLANK([CT Scan Date]),",",""), IF(ISNOTBLANK([MRI/MRCP Date]),[MRI/MRCP Date],""), IF(ISNOTBLANK([MRI/MRCP Date]),",",""), IF(ISNOTBLANK([ERCP Date]),[ERCP Date],""), IF(ISNOTBLANK([ERCP Date]),",",""), IF(ISNOTBLANK([EUS Date]),[EUS Date],""), IF(ISNOTBLANK([EUS Date]),",",""), IF(ISNOTBLANK([PET Scan Date]),[PET Scan Date],""), IF(ISNOTBLANK([PET Scan Date]),",",""), IF(ISNOTBLANK([Biopsy/Image Guided Biopsy Date]),[Biopsy/Image Guided Biopsy Date],""), IF(ISNOTBLANK([Biopsy/Image Guided Biopsy Date]),",","") )

[Steven_Coile]

You can combine the logic for including the scan date value and the corresponding comma into a single line for each scan date column:

=CONCATENATE( IF(ISNOTBLANK([USS Date]),([USS Date]&","),""), IF(ISNOTBLANK([CT Scan Date]),([CT Scan Date]&","),""), IF(ISNOTBLANK([MRI/MRCP Date]),([MRI/MRCP Date]&","),""), IF(ISNOTBLANK([ERCP Date]),([ERCP Date]&","),""), IF(ISNOTBLANK([EUS Date]),([EUS Date]&","),""), IF(ISNOTBLANK([PET Scan Date]),([PET Scan Date]&","),""), IF(ISNOTBLANK([Biopsy/Image Guided Biopsy Date]),([Biopsy/Image Guided Biopsy Date]&","),"") )

The &"," concatenates a comma after the scan date when (and only if) the (non-blank) scan date is included.

[Steven_Coile]

Using the magic of lists and list operations, we can eliminate all of the IF() statements:

=CONCATENATE( LIST( [USS Date]), [CT Scan Date]), [MRI/MRCP Date]), [ERCP Date]), [EUS Date]), [PET Scan Date]), [Biopsy/Image Guided Biopsy Date] ) - LIST("") )

The first LIST() constructs a list of all the scan dates, including any blank ones, then - LIST("") removes all the blank entries from the first list, leaving only the non-blank entries. We then CONCATENATE() the final list; AppSheet automatically puts commas between each concatenated list item.

[Behram_BASHIR]

Hi Steve,

Whats up with the parentheses after each column name? The number of close brackets is a lot higher than open brackets.

=CONCATENATE( LIST( [USS Date]), [CT Scan Date]), [MRI/MRCP Date]), [ERCP Date]), [EUS Date]), [PET Scan Date]), [Biopsy/Image Guided Biopsy Date] ) - LIST("") )

---

Update:

I tried: =CONCATENATE( LIST( [USS Date]), LIST([CT Scan Date]), LIST([MRI/MRCP Date]), LIST([ERCP Date]), LIST([EUS Date]), LIST([PET Scan Date]), LIST([Biopsy/Image Guided Biopsy Date] ) - LIST("") )

It works. But I get unnecessary commas, any ideas, @Steve?

[Marc_Dillon]

Maybe this?

CONCATENATE( 
  LIST( 
    [USS Date] , [CT Scan Date] , [MRI/MRCP Date] , [ERCP Date] , [EUS Date] , [PET Scan Date] , [Biopsy/Image Guided Biopsy Date] 
  ) 
  - 
  LIST("") 
)

And you might try getting info from threads that are a bit more recent than this one.

[Steven_Coile]

To answer your original question, though… Your problem was where your parentheses were. For example, you had this:

IF(ISNOTBLANK([USS Date]=TRUE),[USS Date],"")

You have [USS Date]=TRUE as the argument for ISNOTBLANK(). [USS Date]=TRUE compares the value of the USS Date column with TRUE. That comparison will always result in TRUE or FALSE. That result is the given to ISNOTBLANK(). Neither TRUE nor FALSE is blank, so ISNOTBLANK() will always return TRUE.

So a typo is what caused the problem. It will work if corrected to this:

IF(ISNOTBLANK([USS Date])=TRUE,[USS Date],"")

Note the parenthesis has been moved so ISNOTBLANK() is looking at only [USS Date], then its result is being compared to TRUE.

[Bellave_Jayaram]

+Steve Coile you almost wrote a novel there​:slightly_smiling_face: Well done

[Malaka_Jayaward]

+Steve Coile That was impressive! Understood all of that to the last detail. And Lists! Did not know one could do that! Thank you very much!

[Behram_BASHIR]

Worked exactly the way I wanted. Thank you @Marc_Dillion

[Ferret]