How to detect a row is a newly inserted row

[Steven_Aung]

Current solution is

• add a virtual column name TempKey which is set identical to a table’s key named _Key. Set the column as a reference column which will point back to the current table
• add another virtual column named isNew in which we check the TempKey reference. E.g ISBLANK([TempKey].[_Key]). If the column is true, the current row is brand new.

Is there better way to detect the newly-inserted row? What’s your trick?
Thanks in advance for sharing.

Note - we use the information to make some columns editable at the insertion state or at re-editing state.

[Steve]

To use in a form to determine if the row is new:

IN([_ROWNUMBER], table-name[_ROWNUMBER])

Or:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Is this a new row?/Does this row already exist? Is this a new row? NOT(IN([_ROWNUMBER], My Table[_ROWNUMBER])) or: ISBLANK( FILTER( "My Table", ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER]) ) ) Does this row already exist? IN([_ROWNUMBER], My Table[_ROWNUMBER]) or: ISNOTBLANK( FILTER( "My Table", ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER]) ) ) In the above, replace My Table with the name of the table about which you’re inquiring. When modifying rows, such as in forms or wi…

[Steven_Aung]

I thought about using filter or checking row number list but could they be slower?

Honestly, I don’t know the performance impact of using a virtual column.

[Koichi_Tsuji]

Depending on what sort of expression you kick into Virtual Column, but IN() expression is one of heavy expression.
If you use with small amount of data set, no obvious impact (slownesss) you may feel, but once the data set grows, it will slow down the app.

After IN expression, Select expression is generally expensive as well.

[Steven_Aung]

The virtual column is simple duplication of the key. Say _ComputedKey is the key. Then the virtual column will have the formula [_ComputedKey] and then reference back to the same table.

[Koichi_Tsuji]

Sounds like a self-ref type of arrangement, it should not be a heavy operation then.

[Aline_Mav]

Thank you Steve! The LOOKUP() worked for my recent ticket 🙂

[Aleksi]

@Steven_Aung Why would you need to have a virtual column for this purpose at all? If you use the IN() expression with the Editable If, it has nothing to do with performance because it’s recalculated only when you edit the record.

[Steven_Aung]

I see. The reason was that the editable calculation is shared by multiple columns.

[Aleksi]

It doesn’t matter if one or more columns are involved. You just need to create a suitable formula for your Editable option.

[Steven_Aung · Accepted solution]

Steve:

IN([_ROWNUMBER], table-name[_ROWNUMBER])

Aleksi:

If you use the IN() expression with the Editable If, it has nothing to do with performance because it’s recalculated only when you edit the record.

Good to know that.

[MatthewDavid122]

@Steve @Aleksi 

I'm assuming this works with the use of recursion / looping to create records within a form that calls a group action?  I have a scheduler that generates 1 to N "Schedules".  The form "saved event" calls a looping group action (capped at 5).  My concern is when users edit these generated records, it will (a) be taxing on the system to see if the record exists (n times) and (b) it will not recognize this as an edit and duplicate records.

Thoughts on this approach?  any better ideas to place this?  Otherwise I'll just break up the function into different slices/views.  Edit Mode / Schedule mode.  

Thanks!

Matt

[Steve]

Your approach is fine, it's how I do it, and there's really no other way to do it.