I think I've found a way to identify whether ...

(Steven Coile) #1

I think I’ve found a way to identify whether the user is just viewing an existing row, editing an existing one, or adding a new one:

First, add a (physical, not virtual) column to track the number of times the row has been edited. I do this by adding

column, [ecount] (“edit count”), of type Number, with an App formula of =([_this] + 1).

Then, add a virtual column to indicate whether the row is being viewed, added, or edited. I call mine [vtype] (“view type”). Use the App formula:

=if(([ecount] = lookup([_thisrow].[id], MyTable, id, ecount)), “view”, if(([ecount] = 1), “add”, “edit”))

Replace MyTable with the name of the table, and id with the name of the key column.

You can then query [vtype] to determine how the row is being accessed: add, edit, or view.

The way this works is that lookup() goes into the actual table to retrieve values and bypasses the copy of a row being edited. If the the row is only being viewed (as in a Detail view, or anywhere outside a Form view), the row isn’t copied, so [ecount] and lookup(…, ecount) refer to the same thing, making them equal. If the row is being used in a Form view, column references refer to a copy of the row that contains the new values from the form. The form copy’s [ecount] column will have been updated by its App formula, so the copy’s won’t equal the original’s still in the table. A new row will always have an [ecount] value of 1; any other value means it was a preexisting row.


1 Like
(Steven Coile) #2

Having the [ecount] column will also provide you with some insight into how active each row is.

(Grant Stead) #3

@Steven_Coile dude, I haven’t even thought of doing this… Very smart! It would be cool if appsheet had this built in somehow… @praveen @MultiTech_Visions

However, short term this just opened up some design options for me!

(Multi Tech Visions) #4

@Steven_Coile very nice!

Very nice indeed.

Have you given thought to how to keep track of who’s editing and when?

A paper trail?

Is that important?

If it is, you could try this:

Create an action that’s watching the [vtype] column, and when it’s switched to edit and you save it concatenates the datetime/user to the paper-trail.

concatenate([PaperTrail], "| ", NOW(), “(”, USEREMAIL(), “)”)

or some such formula like that.

the important part is the concatenation of the original column with new stuff.

It simply adds on.

Now you’ve got a paper trail. :wink:


(Grant Stead) #5

@MultiTech_Visions well… Crazy talk… How do you think I can bend this for financial period based task progressing?

(Multi Tech Visions) #6

@Grant_Stead not sure about your exact specifications, but one thing I’ve found with AppSheet is that it’s incredibly powerful with the logic you can apply to your data.

For processing certain events at a certain period, sounds like you need a status column, or maybe a phase column, to track the progress of the row through the whatever process it’s going through.

(Grant Stead) #7

@MultiTech_Visions yeah, it’s more like a percent complete… So I’ve got lots of tasks, and one of them is a task to install a deck, and it’s worth $10,000.00 …

Period 1 - 0% - $0.00 Period 2 - 0% - $0.00 Period 3 - 10% - $1,000.00 Period 4 - 40% - $3,000.00 Period 5 - 90% - $5,000.00 Period 6 - 0% - $0.00 Period 7 - 10% - $1,000.00

So, right now I’m doing these updates in a secondary table…, And it’s not very smooth…

(Multi Tech Visions) #8

@Grant_Stead so what are you looking to do here?

(Grant Stead) #9

Well, the foreman are updating activities as they go every day… Then the mangers lock the financial period and invoice off the progress… So I need to be able to show what process was earned in each period… But it has to be easy to progress the steps…