Valid if on date text

[Peter_Kalu]

Hello all,

I have a special situation where date columns on Appsheet are not parsed properly on SQL database. So, I'm forced to change the column to TEXT in Appsheet. However, I need to enforce validation on the column to only accept this format dd/mm/yyyy. How can I do that with an expression?

[Marc_Dillon]

I think you should figure out why the date columns are "not parsed properly", so that you can set the data type correctly. What type of SQL server are you using? I currently use both MySQL and Postgres, and use date columns just fine. What exactly are the errors? Have you contacted support?

[Peter_Kalu]

We're using Apigee as an intermediary to avoid exposing IP addresses; so that means the Appsheet payload has to be evaluated before reaching MySQL backend.

Appsheet sends datetime fields as such: YYYY-MM-DDThh:mm:ss.000Z. That format is not recognized with our logic, so we're still trying to figure it out. Text field solution is a workaround for now, just need an enforceable valid-if expression.

[Marc_Dillon]

Hmm, ok, I don't know anything about Apigee, but can see how that might be out of your control then. I'd certainly still suggest contacting support about this.

Creating a valid_if for a date format will be a pain. I'm not going to do that for you right now, because that's too much like work (and you might find it already created if you search this forum enough). But I'll copy a similar valid_if from another project of mine so you can see what sort of things you'd have to do. This valid_if enforces the rules for a code like A1-2B-3, where the first segment is a single letter followed by 1 or 2 digits, second segment is a 1 or 2 digit number followed by a single letter, and third segment is a single number. (or something like that).

AND(

COUNT( SPLIT( [_THIS] , "-" ) ) = 3 ,

IN(
  LEFT( [_THIS] , 1 ) ,
  LIST( 'A','B','C','D','E','F','G','H','I','J','K','L','M',
        'N','O','P','Q','R','S','T','U','V','W','X','Y','Z' )
) ,



LEN( INDEX( SPLIT( [_THIS] , "-" ) , 1 ) ) > 1 ,
LEN( INDEX( SPLIT( [_THIS] , "-" ) , 1 ) ) < 4 ,

IN(
  MID( INDEX( SPLIT( [_THIS] , "-" ) , 1 ) , 2 , 1 ) ,
  LIST( 1,2,3,4,5,6,7,8,9,0 )
) ,

IF(  
  LEN( INDEX( SPLIT( [_THIS] , "-" ) , 1 ) ) = 3 ,
  IN(
    MID( INDEX( SPLIT( [_THIS] , "-" ) , 1 ) , 3 , 1 ) ,
    LIST( 1,2,3,4,5,6,7,8,9,0 )
  ) ,
  TRUE
) ,



IN(
  LEFT( INDEX( SPLIT( [_THIS] , "-" ) , 2 ) , 1 ) ,
  LIST( 1,2,3,4,5,6,7,8,9,0 )
) ,

IF(  
  LEN( INDEX( SPLIT( [_THIS] , "-" ) , 2 ) ) = 3 ,
  AND(
    IN(
      MID( INDEX( SPLIT( [_THIS] , "-" ) , 2 ) , 2 , 1 ) ,
      LIST( 1,2,3,4,5,6,7,8,9,0 )
    ) ,
    IN(
      MID( INDEX( SPLIT( [_THIS] , "-" ) , 2 ) , 3 , 1 ) ,
      LIST( 'A','B','C','D','E','F','G','H','I','J','K','L','M',
        'N','O','P','Q','R','S','T','U','V','W','X','Y','Z' )
    ) 
  ) ,
  IN(
    MID( INDEX( SPLIT( [_THIS] , "-" ) , 2 ) , 2 , 1 ) ,
    LIST( 'A','B','C','D','E','F','G','H','I','J','K','L','M',
        'N','O','P','Q','R','S','T','U','V','W','X','Y','Z' )
    ) ,
) ,



IN(
  INDEX( SPLIT( [_THIS] , "-" ) , 3 ) ,
  LIST( 1,2,3,4,5,6,7,8,9,0 )
)


)

Another point is that you probably need to enforce the YYYY-MM-DD format, to match with what mysql expects, not dd/mm/yyyy, no? 

Another suggestion that I feel may be easier, is to split it into 2 columns; an input column, and the actual date column. Both can be Date type in Appsheet, but the input column can be text/varchar in the database, and the other column can be a sql generated column that splits up and converts that iso datetime string into what you actually need.

[Peter_Kalu]

Thanks for the pointers and sample expression, Marc. I changed the mysql column text, but for consistency and sorting purposes I want to enforce dd/mm/yyyy format.

[Peter_Kalu · Accepted solution]

Since the default date format for Appsheet is mm/dd/yyyy, I went with this valid_if expression instead

AND(
COUNT( SPLIT( TRIM([_THIS]), "/"))=3,
ISNOTBLANK(DATE(TRIM([_THIS])))
)