Reset on Edit not resetting to initial value

[Daisy_Ramirez]

I’ve configured a field (BIN Barcode) to Reset on Edit by using a Change Time Stamp field that looks for changes in 2 specific fields (Barcode Flag and Metal BC Flag).

When there’s any change in either of the two fields, I’d like the BIN Barcode field to Reset to the fields Initial Value. It seems to work fine, until an entry is made directly into the BIN Barcode field or the calculated value is adjusted. Once this is done the field no longer Resets.

Here is the Initial Value expression:
IFS(
OR([Metal BC Flag]=Y,[Barcode Flag]=Y)," ",
AND([Barcode Flag]=“N”,NOT(ISBLANK([vir_site_id]))),
IFS(
AND([Barcode Flag]=“N”,LEN([vir_site_id])=5),“00000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=4),“000000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=3),“0000000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=2),“00000000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=1),“000000000”&[vir_site_id]
),
AND([Barcode Flag]=“N”,ISBLANK([vir_site_id])),
“5555555555”
)

Is there any way to make the BIN Barcode field Reset to blank every time the Barcode Flag and Metal BC Flag is set to Y? The use case is when a user inputs the BIN Barcode and then changes their mind and updates the Barcode Flag or Metal Barcode Flag.

[LeventK]

IFS(
	OR(
		AND(
			NOT([_THISROW_BEFORE].[Metal BC Flag]=[_THISROW_AFTER].[Metal BC Flag]),
			[Metal BC Flag]=Y,
		),
		AND(
			NOT([_THISROW_BEFORE].[Barcode Flag]=[_THISROW_AFTER].[Barcode Flag]),
			[Barcode Flag]=Y
		)
	),"",
	AND([Barcode Flag]=“N”,NOT(ISBLANK([vir_site_id]))),
	IFS(
	AND([Barcode Flag]=“N”,LEN([vir_site_id])=5),“00000”&[vir_site_id],
	AND([Barcode Flag]=“N”,LEN([vir_site_id])=4),“000000”&[vir_site_id],
	AND([Barcode Flag]=“N”,LEN([vir_site_id])=3),“0000000”&[vir_site_id],
	AND([Barcode Flag]=“N”,LEN([vir_site_id])=2),“00000000”&[vir_site_id],
	AND([Barcode Flag]=“N”,LEN([vir_site_id])=1),“000000000”&[vir_site_id]
	),
	AND([Barcode Flag]=“N”,ISBLANK([vir_site_id])),
	“5555555555”
)

[Daisy_Ramirez]

Thanks Levent, I inserted the expression and received the error below:

[LeventK]

@Daisy_Ramirez
Can you try with this one pls?

IFS(
	OR(
		AND(
			NOT([_THISROW_BEFORE].[Metal BC Flag]=[_THISROW_AFTER].[Metal BC Flag]),
			[_THISROW_AFTER].[Metal BC Flag]=TRUE,
		),
		AND(
			NOT([_THISROW_BEFORE].[Barcode Flag]=[_THISROW_AFTER].[Barcode Flag]),
			[_THISROW_AFTER].[Barcode Flag]=TRUE
		)
	),CONCATENATE(""),
	AND([Barcode Flag]=FALSE,NOT(ISBLANK([vir_site_id]))),
	IFS(
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=5),"00000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=4),"000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=3),"0000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=2),"00000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=1),"000000000"&[vir_site_id]
	),
	AND([Barcode Flag]=FALSE,ISBLANK([vir_site_id])),
	"5555555555"
)

[Daisy_Ramirez]

Same error:

[LeventK]

@Daisy_Ramirez
That’s my bad forgetting about a redundant comma inside the first AND expression of OR envelope, sorry. Try with this:

IFS(
	OR(
		AND(
			NOT([_THISROW_BEFORE].[Metal BC Flag]=[_THISROW_AFTER].[Metal BC Flag]),
			[_THISROW_AFTER].[Metal BC Flag]=TRUE
		),
		AND(
			NOT([_THISROW_BEFORE].[Barcode Flag]=[_THISROW_AFTER].[Barcode Flag]),
			[_THISROW_AFTER].[Barcode Flag]=TRUE
		)
	),CONCATENATE(""),
	AND([Barcode Flag]=FALSE,NOT(ISBLANK([vir_site_id]))),
	IFS(
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=5),"00000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=4),"000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=3),"0000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=2),"00000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=1),"000000000"&[vir_site_id]
	),
	AND([Barcode Flag]=FALSE,ISBLANK([vir_site_id])),
	"5555555555"
)

[Daisy_Ramirez]

Hi Levent, no error this time but the field still stays the same after an input is made into field. In this case I adjusted the field, pressed “Y” to “Is there a Metal Barcode to scan?” field. Once I press 'Y" here or in the “Is Barcode Available?” field, it should revert to a blank field.

The Change Time Stamp is changing so I can see it recognizes the change was made.

[LeventK]

May I ask what’s you Reset_On_Edit expression for that [BIN Barcode] column?

[Daisy_Ramirez]

[_THISROW_BEFORE].[ChangeStamp_BINBC]<> [_THISROW_AFTER].[ChangeStamp_BINBC]

[LeventK]

Which column(s) that [ChangeStamp_BINBC] checks for the ChangeTimestamp?

[Daisy_Ramirez]

[LeventK]

Change your Reset_On_Edit expression with this:

OR(
	AND(
		NOT([_THISROW_BEFORE].[Metal BC Flag]=[_THISROW_AFTER].[Metal BC Flag]),
		[_THISROW_AFTER].[Metal BC Flag]=TRUE
	),
	AND(
		NOT([_THISROW_BEFORE].[Barcode Flag]=[_THISROW_AFTER].[Barcode Flag]),
		[_THISROW_AFTER].[Barcode Flag]=TRUE
	)
)

And you can use this as the Initial Value

IFS(
	OR(
		[Metal BC Flag]=TRUE,
		[Barcode Flag]=TRUE
	),CONCATENATE(""),
	AND([Barcode Flag]=FALSE,NOT(ISBLANK([vir_site_id]))),
	IFS(
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=5),"00000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=4),"000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=3),"0000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=2),"00000000"&[vir_site_id],
		AND([Barcode Flag]=FALSE,LEN([vir_site_id])=1),"000000000"&[vir_site_id]
	),
	AND([Barcode Flag]=FALSE,ISBLANK([vir_site_id])),
	"5555555555"
)

[Daisy_Ramirez]

Ok updated the Reset on Edit and Initial Value fields and no luck. Once the BIN Barcode field is edited, it does not respond to the Reset on Edit.

[LeventK]

@Steve? @Aleksi?

[Daisy_Ramirez]

Thanks so much for your help Levent. I’ll keep an eye out for Steve or Aleksi.

Thanks again.

[Steve]

Daisy_Ramirez:

It seems to work fine, until an entry is made directly into the BIN Barcode field or the calculated value is adjusted. Once this is done the field no longer Resets.

Any column with an Initial value expression will only be automatically until the user manually changes the column’s value.

help.appsheet.com

App Formulas and Initial Values

How you can set app formulas and initial values to dynamically assign values.

Is this the behavior you’re seeing?

[Koichi_Tsuji]

Reset on Edit kinda stuffs, i would share my recent experiences.

When we refer to other column on other fields being change, we normally do that with expression

[_thisrow_before].[Col1]<>[_thisrow_after].[Col1]

to fire to reset the value to the initial value.

But due to the bug, it wont work for the moment.

This syntax only work on this occassion.

[_thisrow_before].[Col1]<>[Col1]

This is a bug, and I reckon DEV team should have been working on, but not hearing this is solved.

[LeventK]

@tsuji_koichi
@Daisy_Ramirez
Then I would recommend using a LOOKUP expression instead of [_THISROW_BEFORE] and [_THISROW_AFTER]

[Metal BC Flag] <> LOOKUP([_THISROW],"Tablename","KeyColumnName","Metal BC Flag")

[Koichi_Tsuji]

For those like a expression monstar like you , it is easy, but for me it is difficult.

I need the simplest syntax!! haha.

[Koichi_Tsuji]

tsuji_koichi:

For those like a expression monstar like you , it is easy, but for me it is difficult.

I need the simplest syntax!! haha.

Let me repeat! @Steve

[LeventK]

@tsuji_koichi
Now see what I meant? LOL

[LeventK]

Amongst the whole community, @Steve is the expression monster, not me…I would be thanking God provided I have 1000th to 1 power of him…You know the saying; “In AppSheet Forest, Steve has the power of 10 devs”

[Koichi_Tsuji]

Im sorry mate, you are one of them.

[Steve]

[Daisy_Ramirez]

Levent is this for Reset expression? Since the record has not yet been “saved”, would a lookup function ok?

[LeventK]

Yes @Daisy_Ramirez for the Reset expression. It doesn’t matter if the record is not saved. As the LOOKUP expression will return an empty string in that case, the equation will eval to TRUE

[LeventK]

@Steve
She wants to reset the value of a column to its initial value provided any one of the 2 watch-up column value is edited/altered. That’s the query basically.

[Daisy_Ramirez]

Yes, this is correct.

Steve your description is exactly what I’m experiencing. The initial value resets as expected with my and Levent’s expressions but once the user overrides the BIN Barcode field, the reset no longer executes even though the record has not yet been saved etc.

[Steve]

This:

IFS(
OR([Metal BC Flag]=Y,[Barcode Flag]=Y)," ",
AND([Barcode Flag]=“N”,NOT(ISBLANK([vir_site_id]))),
IFS(
AND([Barcode Flag]=“N”,LEN([vir_site_id])=5),“00000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=4),“000000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=3),“0000000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=2),“00000000”&[vir_site_id],
AND([Barcode Flag]=“N”,LEN([vir_site_id])=1),“000000000”&[vir_site_id]
),
AND([Barcode Flag]=“N”,ISBLANK([vir_site_id])),
“5555555555”
)

is equivalent to this:

IFS(
  OR([Metal BC Flag], [Barcode Flag]),
    " ",
  ISBLANK([vir_site_id]),
    “5555555555”,
  TRUE,
    RIGHT(("000000000" & [vir_site_id]), 10)
)

[Steve]

Daisy_Ramirez:

Is there any way to make the BIN Barcode field Reset to blank every time the Barcode Flag and Metal BC Flag is set to Y? The use case is when a user inputs the BIN Barcode and then changes their mind and updates the Barcode Flag or Metal Barcode Flag.

Once the user makes any change to the BIN Barcode column value within the form, no further autocomputation will occur for the BIN Barcode column value. The only way to restore autocomputation is to leave and re-enter the form.

[Daisy_Ramirez]

Got it. " Once the user makes any change to the BIN Barcode column value within the form, no further autocomputation will occur for the BIN Barcode column value. The only way to restore autocomputation is to leave and re-enter the form. Once the user makes any change to the BIN Barcode column value within the form, no further autocomputation will occur for the BIN Barcode column value. The only way to restore autocomputation is to leave and re-enter the form."

A million thanks as always @LeventK @tsuji_koichi and @Steve!

[Koichi_Tsuji]

I just advise you not to stay with

[_thisrow_before].[Col1]<>[_thisrow_after].[Col1]

but

[_thisrow_before].[Col1]<>[Col1]

like that.

Remove [_thisrow_after].

from reset on edit expression, as it wont work as expected oddly.

[_thisrow_before].[Col1]<>[_thisrow_after].[Col1]

Is working for workflow, no problem.

Take care and good luck.

[Steve]

Critical point of clarification: Reset on edit? is only considered when the form view is entered, not continuously as the form is changed by the user. When the form is opened, Reset on edit? is consulted once to determine if the Initial value expression should be applied to the column’s value. If so, the column’s value is handled as per Initial value until the user leaves the form.

[Koichi_Tsuji]

I add one more thing.

Once the row is update through the action, it is dealt with the update the row through the form. User wont need to open up the form, but change the field value by the action/workflow, and it is recognized as “new data is saved through the form view”

But once again, defauld syntax used to work before, but no longer work, unless we remove [_thisrow_after] syntex…

[Daisy_Ramirez]

Thanks Tsuji, will test this out. A million thanks

[LeventK]

@tsuji_koichi
Following @Steve’s last input, I honorrably invite you and myself to Seppuku