Help needed Writing an Expression

  • 87Views
  • Last Post 28 February 2020
  • Topic Is Solved
0
votes
Benjamin Hills posted this 25 February 2020

Hey Skyvia! 

 

I'm currently performing a data import to zoho.  The conncetions are set and established and I have data importing.  I want to add in an expression to a few of my csv mapped columns but am not sure on how to write my expression. 

Example: 

I have column 'foo_data' in my csv that maps to column 'foo_name' - can I write an experssion so 'foo_data' will update 'foo_name' if 'foo_data' is not blank. 

Essentially - if 'foo_data' is not null update the column with 'foo_data' else do nothing. 

 

Thanks!

import zoho-crm expression
Order By: Standard | Newest | Votes
0
votes
Dmitriy Muzurov posted this 27 February 2020

Hello Benjamin,

Thank you for submitting a ticket with us.

This expression can be written with a conditional operator "?" and "ISNULL" function.

Try this one:

ISNULL([foo_data]) ? "" : [foo_data]

Make sure to replace foo_data with actual column name.

We are looking forward to hearing from you, should you have any questions.

0
votes
Benjamin Hills posted this 27 February 2020

I ended up with an error after trying out that expression for one of my columns.  The csv has a column 'DATE_CANC' so I wrote the expression as so: 

 

  • ISNULL([DATE_CANC]) ? "" : [DATE_CANC] . 


  • This expression is mapped to the zoho column "Date Canceled" 

 

Error received: 

 

"ErrorDescription";"Id";"Date Canceled"

 

"An error occurred during computation of the expression.   Column name: 'Date Canceled'.";"4012548000001819028";""

 

 

In this specific instance I wouldn't of expected any data change on the zoho side - right now the record 4012548000001819028 has a 'Date Canceled' and the csv has no data to change for that specific column.  In this instance does that just display as an error on the run? 

Appreciate the help!

 

 

 

0
votes
Dmitriy Muzurov posted this 28 February 2020

Dear Benjamin,

Thank you for getting back to us.

This error is related to the fact that due to data type limitations, it is not possible to put an empty string into a field with 'date' data type.

We would recommend either trying such expression:

ISNULL([DATE_CANC]) ? (DT_WSTR,20)NULL(DT_DBDATE) : [DATE_CANC]

This expression will put NULL to the mapped column instead of some date when a CSV field DATE_CANC does not have any value.

Another option is to change the data type of "Date Canceled" to DT_WSTR (this is called "Single Line" in Zoho) and use this expression:

ISNULL([DATE_CANC]) ? "" : [DATE_CANC]

However, if a target field "Date Canceled" already has some value, both expressions will overwrite it with NULL/blank value.

Finally, if you need to keep the existing value for some record, so that the expression does not touch it, you can leave all as is and disregard the error messages. The initial expression works for pasting a new date value to the blank field and updating existing value in the field and returns errors if DATE_CANC is NULL. 

If there is already a value in the "Date Canceled" that must not be touched, this is a working variant.

Should you have any questions, do not hesitate to contact us back.

New Ticket
Categories
Popular Tags
Close