Why don't we have a way to easily detect ADD or EDIT mode when opening a Form - like this?

[WillowMobileSys]

In email body or template we can use the variable shown below.  There are many use cases on a From view where we need to take different actions based on if the Form is being used to add a new row or if it is being used to Edit an existing row.

It seems extremely odd to me that we have an email variable to be able to report if the mode was ADD or EDIT/UPDATE but we don't have that capability to easily do the same in a Form View.  The only way I know to detect this mode is based on if the row exists in the table or not - i.e. on an ADD it will not yet exist in the table.

It would be so much easier and more reliable if we could ask the app what mode it thinks its in!

Have I been missing something all these years?

[Markus_Malessa]

I agree, and I'm not sure that you're missing something. For me it's as simple as changing the label on the form to 'Add New' vs 'Edit'. On the very rare occasion I even just use two separate forms, but that usually only occurs if there is a difference on the column list that is available on Add vs Edit forms.

[Jonathon]

Agreed, an easier expression here would be nice.. In the meantime, if anyone stumbles on this thread an needs a solution:

I typically add 2 datetime columns, created and updated, with initial values of UTCNOW() and updated resetting on edit. This serves as a basic audit trail, but can also be used to detect row creation from row updates.

IF([created]=[updated],"new row","update row")

I used to use this expression which would work without created/updated columns, but it won't work on the new desktop mode:

IF(IN([_THISROW],Table[KeyColumn]),"Update row","New row")

The above took advantage of the fact that a row doesn't exist in the table until it's saved; the new desktop mode seems to have changed that and the rows now exist in a generic Table[Column] query before they've been saved.

[Steve]

Is this a new row?/Does this row already exist?

[WillowMobileSys]

---

@Jonathon wrote:

the new desktop mode seems to have changed that and the rows now exist in a generic Table[Column] query before they've been saved.

---

Hmmm, really?   That seems like a significant backwards compatibility design flaw.  Wouldn't apps that rely on your second approach, of detecting New from Update, start breaking?  There are probably other implementations that also leverage the fact that rows don't yet exist in the Table on an ADD.

Secondly, I don't see the advantage of adding a row BEFORE it is saved?   And it also must be then that it is added immediately and then kept updated as the user fills out the Form?  Why have the extra overhead?  There are probably use cases that warrant it.  I'm just not seeing it.

[Jonathon]

To clarify, the rows aren't physically added to the database until saved/synced as it's always been.

IN([_THISROW], ThisTable[KeyColumn])

Previously the above expression would return false for new records, and true for existing records. Now, it will always return true.

[SkrOYC]

---

@Jonathon wrote:

the rows aren't physically added to the database until saved/synced as it's always been.

---

It's like it's adding them on the go to the local storage database and when you hit save it just closes the process, while if you hit cancel it removes what it did.

Previously (and with the mobile view) it was the other way around

[SkrOYC]

---

@Jonathon wrote:

I used to use this expression which would work without created/updated columns, but it won't work on the new desktop mode:

IF(IN([_THISROW],Table[KeyColumn]),"Update row","New row")

The above took advantage of the fact that a row doesn't exist in the table until it's saved; the new desktop mode seems to have changed that and the rows now exist in a generic Table[Column] query before they've been saved.

---

Yes, it's a ABSOLUTE pain that IMO NEEDS to be fixed
https://www.googlecloudcommunity.com/gc/Announcements/In-Preview-New-UI-design-for-desktop-users/m-p...

[Steve]

Both the new desktop and database features in preview seem to cast aside existing mechanics in favor of some new vision.

[SkrOYC]

It would be great to understand this new move. If it's something that can put us closer to Real Time Database, awesome! But we have no idea and worst yet we have to treasure hunt this marvelous new quirks 😒

[Jonathon]

Yeah, this should be opt-in similar to the consistent vs legacy blank value comparison modes.

Generally speaking, I prefer the new functionality... but it is a huge pain having to review old applications.

[WillowMobileSys]

---

@Steve wrote:

Both the new desktop and database features in preview seem to cast aside existing mechanics in favor of some new vision.

---

Personally, I think that is fine if they are working towards some new grand capabilities PROVIDED they:

• Recognize impact to existing apps and customers
• Notify all App Creators of imminent changes coming that may break apps and recommendations on how to avoid impact.

This is common industry courtesy whenever there are breaking changes coming soon.  The new Automation may not have migrated as well as hoped when it was first deployed, but at least we were all well aware it was coming!

[Steve]

Yeah, my fear is they don't realize they're deviating from the existing model. As evidenced by the recent Synchronizing... localization fiasco, there are developers who don't know the product, and insufficient oversight.

[victorespina]

I think that breaking backwards compatibility has become a normal practice and that is just terrible for app development.  App dev platforms shoul allways try to be as close as possible to 100% backward compatible.  Its a royal PITA that everytime a major app framework release a new version, all the apps made with the previous version start to fail and force us to rewrite perfectly good and tested code :(.    

Adding new functionality should never come at the expense of breaking existing functionality.

[WillowMobileSys]

Agreed that maintaining backwards compatibility as much as possible is important.  I don't think breaking it has become normal practice.

Never breaking backwards compatibility is not an option.  There are times when, in order for a company to progress forward, they have no choice.  It is in these rare cases, as I mentioned above, the company should alert and give developers ample time to change the affected implementation.

And for the record, I have yet to experience the issue of broken backwards compatibility in AppSheet.

[victorespina]

I don't feel those cases are so "rare".  I used to work a lot with ASP.NET in it first versions and EVERY SINGLE TIME MS released a new version of .NET Framework, I had to do  a lot of fixes and code rewrite because all the breaking changes they introduced.  

Same thing with Angular / Ionic.  It just happened to me last week.  I cloned a 2 years old repository for an app that is still in production in order to add a new feature and, guess what? the code doesn't work anymore due to changes in the Angular framework , so I now have to check what those breaking changes are and make changes in my code to make it work again.

On the other hand, I coded with Visual Foxpro  & SQL Server FOR DECADES and not even ONCE I've had to make changes to legacy SQL Server code to make it work in new versions, and almost the same applies to VFP code. And they introduced major new features and enhancements in every major new version, but making their best efforst to don't break backwards compatibility.

So, it is possible; you just need to make it an important directive for your organization.

[SkrOYC]

---

@Jonathon wrote:

Agreed, an easier expression here would be nice.. In the meantime, if anyone stumbles on this thread an needs a solution:

I typically add 2 datetime columns, created and updated, with initial values of UTCNOW() and updated resetting on edit. This serves as a basic audit trail, but can also be used to detect row creation from row updates.

IF([created]=[updated],"new row","update row")

---

Thanks for the idea btw.
I was trying different methods for a new way to identify new row vs update row and since I also have a [created_at] (Initial with NOW()) and [modified_at] (ChangeTimeStamp) I ended pointing the Initial for the [created_at] to the value of [modified_at], which the first time will make both fields equal. Btw, NOW() takes the datetime of Form opening, while ChangeTimeStamp updates to the last modification, like Form save datetime, and it's fine for my usecase.

Thanks again

[Jonathon]

---

@SkrOYC wrote:

Btw, NOW() takes the datetime of Form opening, while ChangeTimeStamp updates to the last modification, like Form save datetime, and it's fine for my usecase.

---

Unfortunately ChangeTimeStamp doesn't give UTC timestamps, so if those are a requirement then Datetime with initial value of UTCNOW() and reset on edit checked is the way to go!

[SkrOYC]

Yep, I was thinking about that when I saw you using UTCNOW().

I guess you know about this but USERTZOFFSET() might also be useful

USERTZOFFSET() - AppSheet Help

[victorespina]

I just realized this was posted TWO YEARS AGO!!!  How is possible that in 2 years the team behind AppSheets has not found a moment to implement a value for CONTEXT() that can tell if you are adding or editing in a Form view???  Yes, I am aware that there are a couple of workarounds to this (including the dreadly _RowNumber check) but the point is that if it takes more than 2 years to implement such an obvious and simple change, what can we expect for more complex (and needed) changes / enhancements ???

Very dissapointing indeed. 

[WillowMobileSys]

It may very well be that AppSheet has determined internally that such a feature is not needed.  I can certainly attest that I have been able to circumvent the need through expression implementations such as "Reset on Edit" and associated properties.

And I couldn't tell you if it would have been any better!!

[stefanasks]

Due to how AppSheet works, all questions about something already exists or not are obsolete.  We don't even know if a new ID exists or not, there is just a high probability that it doesn't.

[Steve]

True of any distributed system that allows offline use.

[victorespina]

That's why any workaround regarding LOOKUP() is a bad idea... but internally AppSheet DOES NOW if the form is in APPEND mode or in UPDATE mode, so there should be a context value to get that state.

[aquiroga]

The solution using the Key Column doesn't work because AppSheet assign the key value before it save in the database. I tried different methods and this worked for me, I have created a virtual column named "Is_New_Row" :

NOT(IN([_THISROW].[_RowNumber],Table[_RowNumber])

I have a column as UNIQID in the database (MySQL), then, in Data Validity, I used this sentence to avoid get a duplicate error in the database:

IF([_THISROW].[Is_New_Row],NOT(IN([_THISROW].[UniqColumn],Table[UniqColumn])),TRUE)

I hope it can help.

[victorespina]

Ok, this will work but at what cost ? You are seeking a value in the whole table just to determine if its a new record or not.  This shouldn't be necessary since all we need is an environment variable to check the form's mode (since internally AppSheet KNOWS if you are adding or editing).

[aquiroga]

Absolutely, I agree with you, I hope Appsheet add an environment variable or a function in the future.