Comparing TEXT values

Hi,

Very simple task from the looks of it.

I have a line item where user (or formula, I don't think it matters) enters text. I want this text be unique, and if it isn't - I want user to know about it. How do I make a check?

screenshot.65.png

If it were cells formatted as list members - I can think of a way to compare those, but many functions do not work with text fields.

Any ideas are appreciated.

Accepted answers

cianna.bramley

Hey!

I've included 2 screenshots here of different ways you could do this - whether you'd prefer a written text error or more a more visual "red box" error. (You can see the formula for each in the top of the screenshot - and the second also uses conditional formatting to highlight the "red" cells, alternatively you can use a boolean when checking if it's unique, instead of 1's and 0's!)

One thing to note with this solution is that it won't bring back an error for the first occurence of the text (unlike in your screenshot), only the repeated text inputs. I'm not sure if you'd need it for the first occurrence too?

Hope this helps!

Cianna

Screenshot_74_37825.png

Screenshot_75_37825.png

LipChean_Soh

Hi,

Please refer to the following:

Screen Shot 2018-10-25 at 4.41.04 PM.png

I've also created 2 lists, i.e.

1. Transaction A - Corresponds to your line 1, line 2, line 3, etc

2. Agg Text - This is more of a dummy list, with only 1 member, called 1 Mbr

I've created 2 modules, i.e.

1.Unique Text Transaction: This module uses list "Transaction A" and "Agg Text"

2. Combine All Text: This module uses list "Agg Text"

For the "Unique Text Transaction" module, you have the following line item:

1. Text Input (format: Text) - This is where you have your text to be tested for uniqueness,

2. Text Input Length (format: Number) - To calculate the length of "Text Input", formula = LENGTH(Text Input)

3. Replace Text (format: Text) - This looks for 'Text Input' within the 'Combine All Text' below, and replace with blank, formula = SUBSTITUTE(Combine All Text.Value, Text Input, "")

4. Replaced Length (format: number) - This calculates the length of 'Replace Text', formula = LENGTH(Replace Text)

5. Replaced Count (format: number) - This calculates the instances of 'Text Input' replaced, formula = (Combine All Text.Original Length - Replaced Length) / Text Input Length. Note that unique instance will only be replaced ONCE.

6. Error message (format: text) - This shows the relevant error message or not, formula = IF Replaced Count > 1 THEN "Not Unique!" ELSE ""

For the "Combine All Text" module, you have the following line items:

1. Value (format: Text) - This is where you combine all text input from "Unique Text Transaction", formula = TEXTLIST(Unique Text Transaction.Text Input, ",", Transaction A, ALL)

2. Original Length (format: number) - This calculates the length of Value, formula = LENGTH(Value)

Obviously you can optimize the formulas a bit, but i just want to expand everything for easier understanding.

Thanks,

LipChean

All comments

jjwikselaar

Not a very simple solution maybe... but you could create an action that creates a list from all the 'Text inputs' and then to the check based on the list.

The disadvantage is that you need to run an action / process before the check is applied.

Can't think of an easier way to do this.... if something better comes in my mind I let you know 🙂

YS_*12825

Thank you for your input. From user interface perspective running a process to verify if this same user entered non-unique text seems a bit too heavy. I'd like a more elegant solution, if possible.