Enumeration error in RowNumber

[Austin]

I have a workflow that is taking an action on a set of rows that add a row to another table. When I ran it I got this error:
“Errors”: “Error: Perform DataAction ‘Untitled Action 4’ failed because Row having key ‘185209754’ in table ‘AlteryxReference-CompletedImports’ in field ‘_RowNumber’ is required to have a value. Error: ‘Add Row’ Data action ‘Untitled Action 4’ failed with exception Collection was modified; enumeration operation may not execute. .”,

I read this as “error with field _rowNumber”. Am I reading that correctly? cause we have 0 control over rownumber as a field so it throwing an error must be a bug?

[Steve]

Is _RowNumber in fact your key column?

Is 185209754 likely a _RowNumber column value the app is trying to use? 185,209,754 is a hell of a lot of rows! You may actually be running up against a limit of some sort.

[Austin]

No, there is a Key Column for the table.

[Steve]

Possible the random number value for Key duplicates an existing value?

Either way, looks like there’s a bug in the error message in citing the wrong column. That, at least, should be reported to support@appsheet.com.

[Victor_Keil]

Hi,

First post on the forum, so I am not sure whether to create a new thread or add to this one, but here goes.

I am getting the same error message as @Austin_Lambeth when running an action with a workflow which runs on ADDS_ONLY on a certain table. The action adds a new row to another table using data from the ‘ADDED’ row.

I do not use _RowNumber as a key on any table directly. I do, however, use it when generating the initial value for the key column in some tables, but only ever in conjunction with UNIQUEID(). I am happy to provide any further info which I have inevitably left out.

Thank you in advance!

[Steve]

Would prefer you start a new topic to detail your problem. It’s a lot easier to consider problems separately than trying to read back through an existing topic and trying to imagine how it aligns with your experience.

[Victor_Keil]

Will do.

[sreekar_pranav]

Instead of random use unique I’d()

[Austin]

The Key is a RandBetween(1,999999999) and we have like 2000 rows. Another person on our team has had issues where Rand and UniqueID does not seem to enumerate when adding multiple rows through workflows/actions. I had never experienced it but I generally take my multirow adds to the database side instead of letting Appsheet handle them.

[Steve]

Austin_Lambeth:

Another person on our team has had issues where Rand and UniqueID does not seem to enumerate when adding multiple rows through workflows/actions.

I’ve seen that, too. I suspect the random number generator seeds from the current time each time it’s used, so multiple uses within a very short period of time (e.g., within the same second) will produce the same result.

@MultiTech_Visions, do you have experience with this?

[Austin]

I dont think it is a within the same second as I have singular actions that can be done in as rapid a succession as you can click a mouse and never have this issue. My thoughts are some level of parallelism is happening and the number does not change.

[Austin]

This is the action that the workflow triggers. It executes the second action on all rows of the PDI Import table because we are copying every row out of a view in our database where status = pending then a follow up action from the workflow is setting all the statuses to Paid which removes them from this database view.

(Ignore my action names as everything is in testing right now )

[MultiTech]

That would be a problem, most definitely.

My rule for IDs is as follows:

• Always a text column
• Always hidden
• Always UNIQUEID()

---

I’ve only recently begun experimenting with “sensical” IDs, where I’ll take some value entered by a user and clean it up to be an ID.

Here's the formula I've been testing out; it works in most instances.

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(

TRIM([ENTER_YOUR_COLUMN_HERE]),

" , ", “"),
", ", "”),
" “, “"),
“-”, "”),
left(”’ “, 1), “”),
left(’” ', 1), “”),
“/”, “”),
“.”, “_”),
“,”, “”),
“”, “”),
“:”, “”),
“;”, “”),
“[”, “”),
“]”, “”),
“(”, “”),
“)”, “”),
“{”, “”),
“}”, “”),
“!”, “”),
“@”, “”),
“#”, “”),
“$”, “”),
“%”, “”),
“^”, “”),
“&”, “”),
“*”, “”),
“?”, “”),
“|”, “”),
“<”, “”),
“>”, “”)

A slimmed down version (without each item on it’s own line) would be:

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(
TRIM([ENTER_YOUR_COLUMN_HERE]),
" , ", “"), ", ", "”), " “, “"), “-”, "”), left(”’ “, 1), “”), left(’” ', 1), “”), “/”, “”), “.”, “_”), “,”, “”), “”, “”), “:”, “”), “;”, “”), “[”, “”), “]”, “”), “(”, “”), “)”, “”), “{”, “”), “}”, “”), “!”, “”), “@”, “”), “#”, “”), “$”, “”), “%”, “”), “^”, “”), “&”, “”), “*”, “”), “?”, “”), “|”, “”), “<”, “”), “>”, “”)

---

But you have problems with overlap sometimes, so you either need to add something (like UNIQUEID() to the end) or just “handle” having the possibility of duplicates. (Possibly put a validation on the text the ID is based off to not allow duplicates, etc. etc.).

This is exactly why I don’t meddle with IDs - all these “possible” eventualities.

If you stick with UNIQUEID() on a text that’s hidden, you can trust the system to do things right.

[Steve]

MultiTech_Visions:

Here’s the formula I’ve been testing out; it works in most instances.

[Steve]

MultiTech_Visions:

That would be a problem, most definitely.

[Austin]

The table that the error is being thrown in is AlteryxReference-CompletedImports not PDI Import. The PDI Import table is Read Only and has no Key since I only need to make a CSV out of it.

[MultiTech]

@Austin_Lambeth Gotcha.

I’ve never seen this particular behavior (as per my rules for IDs above), but I have experience formulas just not functioning sometimes.

I would throw an email to support@appsheet.com about this; they might be able to step through actions or something to debug.

[Austin]

I sent an email about rownumber throwing the error when that’s probably not the error, I’ll add the rest onto that when they respond. Not hopeful so time to find a workaround in the database.

[MultiTech]

I bet if you included a column for an actual ID, and in your action that creates the rows in that table include that column with a unique ID formula… I bet that will solve your problem.

[Bahbus]

I gotta agree with the always UNIQUEID() for the actual Key. Especially with the UUID option. I’ve only made a few exceptions, and those are hand-crafted, read-only tables (like my weekday 1-7=mon-sun formatted reference table).

[Austin]

I prefer RandBetween() because I generally try to do a MAX(Key)+Rand since appsheet can’t simply let my database handle the auto int and I prefer to have sequential Keys. This app is a 1 user app so I haven’t had to worry about duplicate keys. I exclusively use Rand or a MAX+Rand and have not ran into any issues before.

[Eddy_Xu]

give the following a try:
set the initial value of _RowNumber in table “AlteryxReference-CompletedImports” to uniqueId()

[Austin]

_RowNumber is the generated column from appsheet on every table. It is not editable.

[Eddy_Xu]

I used this method to bypass the error, and hence to recommend an attempt.

Totally understand _RowNumber is not editable, but the initial value field of the column could be entered with a formula

[Scott_L]

Eddy’s tip worked for me. (Thanks for posting it)