Looping, Iteration?

[Erik_Meyer]

I have a variable length field of data that needs to be validated.

Is there any way to iterate over data? For example, is there some sort of for loop, do…until, etc? If no, as I suspect, how could you handle scenario below?

Example Psuedo Code, step 3 is either a or b, not both:

1. Data entered by user, triggers workflow action
2. Workflow action copies Data to a hidden or virtual column so it can be worked upon
   3a) Action iterates through each character, word, whatever and checks on each individually, updating the hidden column as necessary if data needed to be changed
   3b) Action iterates through data and finds non conformant data, rejects the update
3. Action copies modified data from hidden column back to original cell, or rejects update as determined in step3.

TIA for any assistance you may be able to provide.

erik

[MultiTech]

There’s no looping like that, something like a FOR() statement???

But you can do bulk actions, and you can create an action that would take your list of records and run your validation action over them.

First create an individual action for each of your validation steps.
Next, put those actions in a Group action in the order you wish them to process.
Finally, create the “execute an action on a set of rows” action that calls your grouped action.

I guess in a way, the “Data: execute an action on a set of rows” is kinda like a FOR() statement…

FOR each of the rows, do THIS action.

help.appsheet.com

Actions: The Essentials

Learn how to use actions to modify data and navigation in your app.

[Erik_Meyer]

MultiTech_Visions:

But you can do bulk actions, and you can create an action that would take your list of records and run your validation action over them.

Well, if I have a text field, I don’t know how long the string it contains is when building the action. Say a user enters 5 characters - the action will need to evaluate 5 times, once for each character. Next time I might need to check on 25 characters. How would I setup an action that can apply a test to any given number of characters/words without some sort of looping construct?

[Bahbus]

I cannot think of a way that would allow you to do this. I would suggest to look for a way to design the data differently.

[MultiTech]

I agree with @Bahbus, you won’t be able to do something like that.

Perhaps if you gave us some clues as to what you’re trying to do, I’m sure someone here will have some ideas.

[Steve]

MultiTech_Visions:

Perhaps if you gave us some clues as to what you’re trying to do, I’m sure someone here will have some ideas.

Data Validation - MAC Address Questions

Hello, I have an app where my installers will need to enter the MAC address for a device they are installing. I am looking for assistance in validating that the data entered is a valid MAC address. The MAC field they are using is setup as a text type. MAC address must contain exactly 12 characters, where each character is equal to 0-9 or a-f. Additional characters may be entered as separators, eg aa.00.bb… or 00:de:9a…. What I would like to have happen is this: User enters data System lo…

[Koichi_Tsuji]

Not sure if this helps, but did you consider to use Contains() expression?

help.appsheet.com

CONTAINS()

Does text contain fragment?

[Erik_Meyer]

Thanks @Steve. These two posts are related, as you surmised. Thanks @MultiTech_Visions as well for a solution (other thread) that pointed me in the right direction.

Follow up question: Is there a way to use an IF() construct in such a way that multiple actions are assigned to the true and false events? Example:

IF( some expression,
true do something AND do something AND do something,
false do other thing AND another thing
)

Are the expressions used for true and false limited to a single action?

[MultiTech]

Think of it more like “the space in the formula” needs either True or False - meaning, the place you enter the conditions in the formula - however you get there is up to you.

IF( true
IF( true = true
IF( and(true = true, false = false)

all equal true

You can have as many conditions inside the condition space, as long as it ultimately results in either true or false.

help.appsheet.com

Yes/No Expressions

Write expressions where the result is either TRUE or FALSE.

[Erik_Meyer]

@MultiTech_Visions I understand about setting multiple conditions using AND, OR, etc. What I am asking about is the “then do” and “else do” parts.

More detailed example:

< conditional >
IF(
OR(
contains([MAC Address Entry], “g”),
contains([MAC Address Entry], “h”),
contains([MAC Address Entry], “i”) ),
…

< then do >
(SUBSTITUTE([MAC Address Entry], “g”, “”))
(SUBSTITUTE([MAC Address Entry], “h”, “”))
(SUBSTITUTE([MAC Address Entry], “i”, “”)),
…

< else do >
SOMETHING ELSE
SOMETHING ELSE

I hope that makes the question a bit clearer. Thanks again for imparting your knowledge on us noobs!

[Erik_Meyer]

I have tried the following in the formula for a field, but it always hits the first one (g) and skips the second (h). So now I’m trying to see if I can stack actions in the < then do > section of the expression.

< only first condition happens >
IF(
contains([MAC Address Entry], “g”),
(SUBSTITUTE([MAC Address Entry], “g”), “”),
([MAC Address Entry])
)

IF(
contains([MAC Address Entry], “h”),
(SUBSTITUTE([MAC Address Entry], “h”), “”),
([MAC Address Entry])
)

[MultiTech · Accepted solution]

Oh yeah, you’re talking about nesting. All day.

If(true=false, 
  "Wait...what?!?!", 
switch([Some_Column], 
  "1", "first option", 
  "2", "second", 
if("nothing"="something", 
  "wow!!!!", 
"the end")))

As long as you keep track of where you’re at in the arguments of whatever expression you’re on, and close all the parenthesis, you can stack things so they’re crazy…

---

But for what you’re wanting to do, since you’ll ALWAYS want these characters to be removed - I wouldn’t bother with the condition, just nest the substitutes.

[Erik_Meyer]

@MultiTech_Visions you rock! It’s gonna be ugly, but at least I know how to do it now.

Another follow up: is there a way to add comments into a long expression like that? # or // or something tells interpreter to ignore line?

[MultiTech]

No, unfortunately. We asked for it awhile ago, but due to how things are parsed (especially with workflows and templates) there’s a limitation to that.

[Erik_Meyer]

Also, when using a literal double quote, open/close parenthesis, etc is there a way or need to escape certain characters?

SUBSTITUTE([MAC Address Entry], “< double quote >”, “”)

should it be like this: SUBSTITUTE([MAC Address Entry], “”", “”)

or like this: SUBSTITUTE([MAC Address Entry], " \ " ", “”) ignoring extra spaces

[MultiTech]

As long as things are contained inside the double quotes, you can enter any string you wish - even single quotes for apostrophes.
So:

“Can’t figure it out” = Can’t figure it out

AppSheet is smart about things like that.

You can’t include double quotes inside your double-quoted-text though, for that you can use single quotes:

'Then she said, “Something wild!!!” ’ = Then she said, “Something wild!!!”

[Erik_Meyer]

OK, it is so close now.

I have found one seeming exception to the smart double quotes: single quote. I have not been able to find a way to test against a single quote

IF(
ISNOTBLANK([MAC Address Entry]),
SUBSTITUTE([MAC Address Entry], " ’ ", “”), “blank”) < extra spaces added for clarity >

This does not work. Insert anything other than a single quote inside the SUBSTITUTE line and it works great. Do you know of a way to check against a single quote, or is this possibly a bug?

Edit: I have tried to following patterns for single quote, all of which failed:

< extra spaces added for clarity >
" ’ "
’ ’ ’
’ " ’ " ’ < interprets but does function as intended >
" \ ’ "

[Steve]

Erik_Meyer:

Do you know of a way to check against a single quote, or is this possibly a bug?

Can’t be done. It’s a known limitation of the parser library. (Last I brought the issue up.)

[Erik_Meyer]

Steve:

Can’t be done. It’s a known limitation of the parser library. (Last I brought the issue up.)

That’s a bummer. Seems to me that, for a data entry/capture application platform, it’s fairly important to be able to test against any possible character input on a keyboard (or read from OCR, Scanner, etc). Do you know if there is a plan to overcome this limitation?

[Bahbus]

I’m sure it’s one of many to-do’s on their ever-growing list, but I imagine will only be part of a larger overall upgrade or overhaul.

[Steve]

Erik_Meyer:

Do you know if there is a plan to overcome this limitation?

I do not know.