Character Length Errors

[reginawhelan]

Hi, I've built a flow that validates & formats the data appropriately to upload into a system.

There are 81 columns that all have unique character limits, for example:

Field 1 must be 9 characters

Field 2 allows for up to 50 characters (including blank spaces)

etc.

I would like to have a validation that errors out any fields that exceed the character limit but in just one column (I don't want 81 error columns). I would like to have one column that identifies the specific cell/s that exceeded the limit. For example:

Column 1/Row 29 - exceeded character limit 

Column 1/Row 29 & Column 12/Row 29 - exceeded character limit

I've tried the following:

Created multiple formulas (one function), as follow:

if Length([Field 1]) > 50 then "Yes" else "No" Endif

Then another formula function following with:

IF [Field 1] = "Yes" THEN "Field 1" or if([Field 1] = "Yes" and [Field 2] = "Yes") then "Field 1 & Field 2" ELSE "No" ENDIF

The second formula has Malformed IF Statement.

Is there an easier way to identify these errors? Without using so  many if/and statements?

[bpatel · Accepted solution]

hi @reginawhelan ,

what about trying something like this - taking your 81 columns, then transpose and have a second table or file with all the corresponding character limits and do the same to join and then have one simple formula

Hope this helps and is what you are looking for

[reginawhelan]

Thank you. I'm still having some difficulties and hoping you can help.

I am getting an "invalid type in operator" error message.

I used the exact same formula you used in your example and tried changing the Type to V_WString & String but still getting the error. 

[reginawhelan]

Hi again, please disregard my last message. I removed all the Select functions and it now works. Thanks again!

[reginawhelan]

Hi there, I found your solution very helpful and have replaced a few formulas within my flow with it. Do you know if I could read multiple values? For example:

The gender could be Male or Female. I would want to identify anything that isn't Male or Female. 

I've tried numerous methods to read both values but not having any success.

The flow right now only looks at the first value.

Thanks 

[bpatel]

hi @reginawhelan,

do you have an example of the what the data looks like? 

[reginawhelan]

Hi, I want to ensure the customer is either Male of Female. It's ok if the field is blank as the customer may not have a spouse or children.

The errors I want to identify are red in the following chart:

Customer ID	Gender	Spouse Gender	Child 1 Gender	Child 2 Gender	Child 3 Gender
Customer 1	Male	Female
Customer 2	Male	Female	Malw
Customer 3	Male	Female	Female	Female
Customer 4	Male	Female	Female	Male	Female
Customer 5	Male	Female	Female
Customer 6	Female	Female	Female
Customer 7	Female	Male
Customer 8	Female	Male
Customer 9	N/A	Male

[bpatel]

hi @reginawhelan ,

i hope this is helpful and what you are looking for. you could do multiple different things but here are two suggestions i came up with:

or