Can I automatically calculate a number ONLY w...

(Rogério Penna) #1

Can I automatically calculate a number ONLY when the new form entry is first created?

My Safety Inspection App have construction sites that may be inspected several times.

Each time a construction site is inspected, a new entry is created for the new inspection, but it reflects the number of the inspection of THAT site.

So if it’s the 4th time I am inspecting site number 413, it should show Site 413 Inspection 4.

So far so good. I got it right by two different methods (counting all previous entries with site number, or getting the max number from the inspection counter

of that site (so if I have 412=1, 413=1, 413=2, 413=3, 412=2 and I open a new 413 inspection, it will find it’s maximum number is 3 and will add 1 giving me the number 4)…

My problem… is when editing. So the form was saved and I have: 412=1 413=1 413=2 413=3 412=2 413=4

Now I want to edit form 413=3. I edit it and the number changes to 5 (either it finds the maxium number of 413 forms was 4 and adds one, or it counts all 413 forms (4 saved) and adds one.

Tried to block the change with editable_if

to no more than 2 seconds after entry creation datetime.

Well, no use. It blocks editing, but not automatic change BY AppSheet formula.

Anyway to freeze the calculation of that number after I save the entry for the first time?

Btw… while when editing a form entry I do not want that number changed… I would like to ALLOW the number to be changed IF they change the Construction Site number. (let’s say the technician by accident made an entire form with the wrong number of a nearby construction site. Instead of having to do an entire new Inspection, he could just change the construction site number… but then, the number of the Inspection should change too.

(Tony Fader) #2

@Rogerio_Penna How about using an initial value in a hidden column, instead of an app formula? Then the value will be calculated once at creation.

(Rogério Penna) #3

@tony Hmmm… sorry Tony, can you be more specific.

So let’s say the initial value in the hidden column is 1.

Ok, so now I create a new Audit entry in a site that was audited 4 times before.

How will that change to 5, if the initial value was 1?

And how, in this way,

will I be able to change the audit number IF if change the Site number?

While I don’t understand your simpler solution, I came up with a hard one that is not working very well yet.

=if([changecounter]=1, max(SELECT(AppInspecaoSESMT[Numero], [Obra] = [_THISROW].[Obra]))+1,[_thisrow].[numero])

So I have a new column which is a CHANGECOUNTER type, set to RESET and checking for changes in the Construction Site number.

So, If I make a change to the Site Number, counter change to 1 and Audit number increases in 1 in relation to that Site audits.

Problem is the ELSE… if counter doesn´t change, how do I keep the SAME number?

Method above made the Audit number disappear when I edited an entry.

How do I use an IF condition where if condition met, than a change occurs. Else, “NOTHING happens” and I stay with the same number?

(Tony Fader) #4

@Rogerio_Penna Initial values on a hidden column will be evaluated/saved only once.

Let’s say you have a Child table that references a Parent table. Create a column on your Child table with the initial formula COUNT([Parent].[Related Children]) + 1. Mark that column as hidden. That should assign each Child record a number when it’s created.

Also, please read this article about why this can have issues with offline/multiple users:

(Rogério Penna) #5

@tony Maybe it’s possible to simplify this issue by having the user inserting the Audit Number?

Of course, I would still would need to tell the user what was the last audit of the work site.

I guess that can be done by checking max timestamp of current chosen working site.

Meaning… choose work site 413. System looks column with Work Site Codes. Under that column, selects only the ones with number 413. Check, amonst these, which one has the highest TimeStamp and then lookup that row’s Audit Number.

Tells the user it’s 5. User can insert Audit Number or Change it if he is editing.

(Praveen Seshadri (AppSheet)) #6

@Rogerio_Penna, Tony’s suggestion is the right one. If you take the formula you were using in the AppFormula property and instead put it into the InitialValue property, then it will be computed once initially and then not repeated each time the entry is edited.