Reset on edit updates with specific field

TalkNet_TechnoS · 07-29-2019 03:28 PM

Hello everyone
I need to control the behavior of “Reset on edit” so that it only updates if changes were made only in a specific field.
I have a [Price] field that updates its “Initial Value” when selecting a [Product].
If the [Price] value was changed manually, updating another field returns to the original value.
If I use ISBLANK ([_ THIS]) in the condition, the manually entered value is not lost, but if it is decided to change the [Product], it keeps the previous price and is not updated because it was no longer blank.
What condition would you have to set to achieve it?
Thank you so much

LeventK

@TalkNet_TechnoStore
I did understood this:

You have a [Product] column and a [Price] column
[Price] column is associated with [Product] column and has an initial value dependent to this column. Meaning when [Product] changes, [Price] column’s initial value changes.
[Price] column’s value can be edited and another value can be entered other than its initial value

How do you want the reset on edit shall be working? Can you elaborate?

TalkNet_TechnoS

Thats right.
I need the [Price] field to update its value, only if the value of the [Product] enum field changes.

Steve

Do you want to allow the user to enter a value for the Price column? If not, you’d be better off using an app formula for the (normal, not virtual) Price column, rather than using Initial value, Reset on edit, and Editable? expressions. In this case, just set the app formula for the Price column to an expression to lookup whatever product is specified by the Product column. The app formula will always ensure the price is up-to-date whenever the form is saved.

Unless you don’t want the price to be updated once the user has made the initial choice, so that the user’s choice isn’t affected by a later price change…

Still, I’d think an app formula would be preferable. Either way, what you really need is the logic the avoid a price change if the product hasn’t changed.

The following answers the question, “is the Product column value in this form different from that saved in the table?”

ISBLANK(
  FILTER(
    "MyTable",
    AND(
      ([RowKey] = [_THISROW].[RowKey]),
      ([Product] = [_THISROW].[Product])
    )
  )
)

FILTER("MyTable", AND(..., ...)) gathers the list of rows from the (e.g.) MyTable table that match both of the given criteria (...; see (2) and (3)).
([RowKey] = [_THISROW].[RowKey]) matches only the row in MyTable with the same key column value as the row currently in the form. Replace both occurrences of RowKey with the name of the table’s key column.
([Product] = [_THISROW].[Product]) matches only if the saved row has the same Product column value as the form.
ISBLANK(...) is TRUE if the list of rows from (1) is empty (blank), meaning either the row in the form is new, or it exists but has a different Product column value.

TalkNet_TechnoS

@Steve
I just need the [Price] field to update its value, only if the value of the [Product] field changes, otherwise it will be maintained whether it is a result of the Product field or has been manually modified.

In the [Price] field there are

Init Value: [Product Id]. [Price] // This places the price of the selected product in the “Enum” [Product] field as the initial value, but allows you to modify it manually.
Since it is an Init Value, if you want to choose another [Product] later, the [Price] must be updated, but only if the [Product] is changed, it must not be changed if a value was entered manually and other fields of the Form are edited.
What I intend to do would be something like this:
Reset On Edit
IF (
[_THISROW_BEFORE]. [Product] <> [_THISROW_AFTER]. [Product],
TRUE,
FALSE
)
but these expressions only work in templates apparently and I can’t think of how to do it.

Steve

I believe my earlier post gave you a way to do what you want.

TalkNet_TechnoS

@Steve
Ahhh ok, I had interpreted that with that expression you couldn’t enter the price manually.
That expression should be placed as Init Value and Enable Reset on edit. Is that so?

Steve

Reset on edit only.

TalkNet_TechnoS

@Steve
It did not work, I created a virtual column with that expression, to see that it returns and always returns the selected product.

I opted to create an “Item” called Custom Product, so that you can only manually enter the price of that item and the others are fixed. and I put the following expressions:
In Init Value:
[Product]. [Price]
In Editable If:
[_THISROW]. [Product] = “Custom Product”
In Reset on Edit:
[_THISROW]. [Product] <> “Custom Product”

If when choosing the Product I choose “Custom Product”, the Price field is editable, but if I choose another Product and then choose “Custom Product”, the value of the “Custom Product” is updated, but it is not editable again. I do not understand why it is.

Then I tried to do it with Data Validity
Valid If:
IF ([Product] = “Custom Product”,
TRUE
IF ([Product]. [Price] = [_ THISROW]. [Price],
TRUE,
FALSE
)
So if the Product is personalized let me write and if not, if the value is what you took from the Init Vaue, validate it and if it is a manually set value do not validate it and put a message indicating that you must select personalized product,
It didn’t work for me either, it gives me an error even though the value written in the price field is the same as the initial value from where it was taken

hsghost

I have a very similar use case here, which a feature change has to be made, I believe, in order to get such a design to be functional.

Say, I have a Purchase Records table which logs the [Supplier ID], the [Product ID] and the [Product Supplier Code] which is used to query the product catalog from the supplier. And I also have a separate Product Supplier Records table which logs uniquely the known combinations of suppliers and products, so as the associated [Product Supplier Code].

Since we are an importer, we can have multiple Suppliers for a same Product. So upon the creation of a Purchase Records entry, the logic is to select a [Supplier ID] first, then a [Product ID], and then, the program should lookup into the Product Supplier Records table. If there is a matching on both of these two columns, then its [Product Supplier Code] value should be copied to fill the new entry. Otherwise, if there’s no matching on Product Supplier Records, rather than disallowing it, this should be recognized as the debut of a new combination. The user should be able to manually enter the [Product Supplier Code] according to the supplier’s catalog, for future reference. A new Product Supplier Records entry should hence being created automatically on the backend using a workflow rule.

While it seems to be feasible through the use of an Initial Value combined with the option of Reset on Edit, it’s actually faulty. Upon the creating a Purchase Records entry through its Form UX, when I first selected a supplier and then a product, the Initial Value generated normally. Yet, on the occasion of a mistake, or change of mind, I reselected a product or supplier, the [Product Supplier Code] field just stayed static. The desired effect of automatically showing a Code upon related selection changes could not be achieved.

On the other hand, if I utilize the Formula field instead of Initial Value, this automatic re-computation would easily be accomplished. Yet, the field would become manually unchangeable. That is also unacceptable, because both the creation of a new Product or Supplier, and the underlying workflow for maintaining the Product Supplier Records entries are relying on the manual enterings. Similar problem with a Valid If dependency approach, as it would disallow any new combination of [Product ID] and [Supplier ID] to be stored in, hence making the workflow for creating new entry on the Product Supplier Records dependency table impossible to be triggered.

So the feature I am demanding, instead of Reset on Edit, is Reset on Change. Whenever I change a related field on the Form UX, the Initial Value expression can get recomputed “on the fly”, rather than staying static until it’s being saved to the data table. This would do the pre-fill, yet leaving the user with an opportunity to update the value whenever it’s necessary.

Thanks.

Lynn

Hi , Have you tried “Suggested values” instead of Valid If

hsghost

Well, the Suggested Values only gives you a list to choose from, yet nothing is automatically selected. So, on my use case, even if I use it together with Initial Value, the selected value won’t change after my re-selection of a product. An extra tap is still required. And this even increases the chance of a mistakenly overriding to the Code, as I may forget to reselect that field as it’d already be showing some value in it.

Overall, my desired outcome would be to automatically filling in the correct value of the Code field whenever I made a change to the prior two. This is to reduce the operations required for our staff on our inventory management process.

Thank you anyway for your suggestion, Lynn. Appreciated.

Steve

From the documentation:

In other words, an Initial Value expression acts just like an App Formula, but it has two differences: (a) the column is still editable by the user, and (b) once edited, the column is no longer automatically updated with continued re-computation of the expression.