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

(Malaka Jayawardene) #1

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) #2

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) #3

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) #4

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) #5

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.

(Steven Coile) #6

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


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:


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

(Bellave Jayaram) #7

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

(Malaka Jayawardene) #8

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