Solved: Comparing text with the apostrophe ( ' )

Swoopy · 06-10-2021 08:31 PM

Since this expression is invalid on AppSheet.

[column] = " '"

(Note that any spaces are not allowed in front of the ’ )

I need to prevent user from saving something like,

Height=5 '

( 5’ is preferred)

So I need to include it in Valid If expression.

How to correct it ?

Swoopy

I use LEFT(" “&”’ ", 2) as work around.

My valid expression is

NOT(OR(
   CONTAINS([_THIS], "' "),
   CONTAINS([_THIS], LEFT(" "&"' ", 2))
))

This prevents users from saving texts like
it ’ s
it’ s
it 's
instead of it’s.

View solution in original post

Suvrutt_Gurjar

Could you please elaborate, what is the valid entry and what is an invalid entry that you wish to prevent the user from data entry and column type etc.?

Swoopy

Samples for valid entry
1’
10’
100’

Samples for invalid entry
1 ’
10 ’
100 ’

Thanks.

Suvrutt_Gurjar

Thanks. Column type- text?

Swoopy

Yes Sir.

Suvrutt_Gurjar

Could you please try the below

AND(

IFS(
LEN( LEFT([_THIS], LEN([_this])-1))=1, NUMBER(LEFT([_THIS], LEN([_this])-1))>0,
LEN(LEFT([_THIS], LEN([_this])-1))=2, NUMBER(LEFT([_THIS], LEN([_this])-1))>=10,
LEN( LEFT([_THIS], LEN([_this])-1))=3, NUMBER(LEFT([_THIS], LEN([_this])-1))>=100),

RIGHT([_this],1)="’ "
)

Is supposed to work for heights till 999’

Swoopy

Actually, I need the following text to be invalid.

The object 's Length=3 ', Width=4 ’ and Height=5 '

The valid text should be,
The object’s Length=3’, Width=4’ and Height=5’

Please advise because your suggested expression is not working with it.

I’d tried to use CONTAINS([_THIS], " '") to catch the invalid input but it errors because of the " '" .

Suvrutt_Gurjar

Thank you for revised requirements

In general repeating the shared expression suitable for 1 parameter could be extended for 3.

Could you update what is your setup for this entire column? Is it concatenated text that the user will enter? How the user enters the three parameters?

Swoopy

It’s for note taking columns. So users are freely typing their notes. However I need to make it not looking weird.

There is no any parameters input like you asked.

Suvrutt_Gurjar

I am sorry in that case. I will be unable to offer any suggestion for such a free flowing text validation. I believe unless some pattern is there, one may not be able to validate a free following text exactly.

One option could be the users enter the three parameters in three fields and the app constructs a valid string sentence.

My request will be if possible to mention requirements in greater detail so it could ensure evaluation / better solution.

Maybe someone else will have a solution to your requirement.

However, I believe the approach suggested for your earlier requirement of just one field will work for anyone looking for such a requirement.

Swoopy

Thanks for your kind and efforts.

Consider the following expression is working fine in Excel & Google Sheet but not in AppSheet validation.

FIND(" '", “abc 'd”)

If AppSheet just recognized the " '" , the valid expression would be so short like

CONTAINS([_THIS], " '")

Suvrutt_Gurjar

I fully agree. It is just matter of CONTAINS() working as desired. I am sure there will be some approach to get it working and someone will definitely have an easier solution the way you mentioned. I will post if I come across any such possibility.

Suvrutt_Gurjar

Could you please try in valid_if the following

NOT(IN(" ’ ",SPLIT([_this]) ))

I believe it will work for the two valid , invalid cases you have shared.

Edit: Modified the expression.

Swoopy

The validation is to avoid 3 cases, for example

It  '  s

It'  s

It  's

I don’t think the " ’ " alone would be working as you said. Anyhow, I’ve got my app working now.

Thanks to all of you guys.

Suvrutt_Gurjar

Yes, saw your solution post after posting. Great to know you got it solved.

Aleksi

FIND(" “&LEFT(”’ ",1),[_THIS])=0 should do the job.

Swoopy

I use LEFT(" “&”’ ", 2) as work around.

My valid expression is

NOT(OR(
   CONTAINS([_THIS], "' "),
   CONTAINS([_THIS], LEFT(" "&"' ", 2))
))

This prevents users from saving texts like
it ’ s
it’ s
it 's
instead of it’s.

Suvrutt_Gurjar

@Aleksi : simple, effective as usual.

Swoopy

Before I saw this, someone on heaven suggested me to use

LEFT(" “&”’ ", 2)