app help - null fields

[#1]

Hi,

We would like an app to populate an answer in a field if its blank and meets criteria. We have tried adding two expressions to the script but neither works:

"e_mail",if(e_mail = "-", 'No email', 'Has email'),

or

  "tency_end_dt", if(tency_end_dt = null, 'Current Tenancy', 'Previous Tenancy'),

Any suggestions/advice?

Much appreciated.

Chris

[sunny_talwar]

If those are null, try this:

If(IsNull(e_mail), 'No email', e_mail) as e_mail or

If(Len(Trim(e_mail)) = 0, 'No email', e_mail) as e_mail

[marcus_sommer]

Try it with: if(len(trim(YourField))=0, 'empty or null or only spaces', 'values available')

- Marcus

[#4]

Hi,

I have tried both the above replacing "e_mail" in the script ; however, comes up with error "file not found <null>'

With both 'email' and tenancy end date'  which were trying to add this to there is some values populated (date or an email) but many blanks ("-")

To clarify we are replacing the script below in bold with your suggested expressions?

Load
age,
AgeBand,
Area,
"category_id",
"Client No",
"Contact_method",
DateofBirth,
"Disability_summary",
"e_mail",if(e_mail = "-", 'No email', 'Has email'),
"Economic Status",
Estate,
Ethnicity,
Gender,
"Internet_access",
Latitude,
Longitude,
LongLat,
"Marital Status",
Nationality,
Neighbourhood,
"Preferred Language",
"Primary Client",
"Property_ID",
"PVP_Alert",
Religion,
"Rent Account Name",
"Sexual Orientation",
"Tenancy No",
"Tenant Type",
"tency_end_dt", if(tency_end_dt = null, 'Current Tenancy', 'Previous Tenancy'),
"tency_st_dt",
UPRN,
"Vulnerability level";

Chris

[marcus_sommer · Accepted solution]

Now more specific, try:

...

if(len(trim((e_mail))=0, 'No email', 'Has email') as e_mail_CHECK,

if(len(trim(tency_end_dt))=0, 'Current Tenancy', 'Previous Tenancy') as Tenancy_CHECK,

...

- Marcus

[PrashantSangle]

Hi,

Try in two statement like

if(e_mail = "-" or len(trim(e_mail))=0, 'No email') as NO_Email,

if(len(trim(e_mail))>0, e_mail) as Has_Email,

Regards

[daveamz]

Hi Chris,

For an extra validation for email address field, you can use something like: If(Index(e-mail, '@'), 'Has email', 'No email')

If no '@' is found in the e_mail string, 0 is return, thus FALSE

Regards,

David

[#8]

Hi all,

Thanks for your input. Tried If(Index(e_mail, '@'), 'Has email', 'No email'), and its worked.

However, with tenancy end date it doesn't work. Can anyone suggest a possible solution? In the date if the tenancy is still live/active then there is '-'. If the tenancy has ended a date exists in the data.

Chris

[daveamz]

Check Marcus's response. It should work fine.

[#10]

HI David, Marcus,

Yes the expression works - thank you:

if(len(trim(tency_end_dt))=0, 'Current Tenancy', 'Previous Tenancy') as Tenancy_CHECK,

I will mark as correct but for our understanding could you just explain the logic of how this works....

Chris