Append value to a List (in a Form View) when values of a column change

[loresani]

Hello everyone,

I have a requirement: I would like to append value to a List (in a Form View) when values of a column change.

1. I've a virtual column "PROVA", type Enum, valorized with values from another column [BILANCIO] (I've renamed [BILANCIO] in "PROBLEMA")

2. So, when I select a value, on the Form view, for the field "PROBLEMA", this value appears in the enum field "prova:

3. My need is that if in column PROBLEMA I change the value, this new value appears as second element in the "prova" enum field (and so on.... every time I change a value, the new selected value appears at the bottom of the "prova field").

4. For now, when I select a new value in PROBLEMA, this replaces the old value in prova:

Thank you so much for the hand,

Lorenzo

[WillowMobileSys]

Would EnumList, instead of Enum, not be a better option?  It is a way to select multiple values from a single dropdown.

Example of EnumList

[dbaum]

I don't understand what you're trying to accomplish. One reason is that I don't understand the purpose of using the Enum data type for a virtual column.

If what you need is for the [prova] column to maintain a running list of each value ever selected in the row's [BILANCIO] column, then try something like:

1. Convert prova from a virtual column to a data source column
2. Change prova's App formula to append the current BILANCIO value to its existing list--for example:

[_THIS] + LIST([BILANCIO])

[loresani]

Hello dbaum,

thank you: your solution works, but only for a column. I would like to do something like: 

[_THIS] + LIST([BILANCIO],[APPORTO],[PESO])

 and, in this way, I would like to have always the last value selected (in one of the 3 columns specified) in the last position of the prova EnumList column.

I've tried with the formula written above.. but it doesn't work.

I hope I was clear,

Lorenzo

[dbaum]

Where did you enter the expression? What doesn't work about it--what do you expect to happen and what actually happens?

[loresani]

Hello:

1. I've entered this expression in the "AppFormula" of a data source column ([ELENCO_PROB_SCELTI]), EnumList type:

2. I'd like  that by choosing different values of the fields [BILANCIO],[APPORTO_ORALE] or [APPORTO_LIQUIDI], the last value on the EnumList column ELENCO_PROB_SCELTI is the last values choosen by the user.

3. Using the following formula , it doesn't work in a good way.

[_THIS] + 
LIST([BILANCIO], [APPORTO_ORALE],[APPORTO_LIQUIDI])

a. I choose a value of [BILANCIO]: it is correctly reported on ELENCO_PROB_SCELTI

b. I choose a value of [APPORTO_ORALE]: it is correctly reported on ELENCO_PROB_SCELTI in the last position (though the value selected on [BILANCIO] appears twice)

c. If now I change the value already selected in the [BILANCIO] field, it doesn't appear in the last position of ELENCO_PROB_SCELTI:

This is the problem! My ultimate need is to set the last value of ELENCO_PROB_SCELTI in another field, using a formula like:

INDEX([ELENCO_PROB_SCELTI], COUNT([ELENCO_PROB_SCELTI]))

Thanks so much

[dbaum]

---

@loresani wrote:

([ELENCO_PROB_SCELTI]), EnumList type

---

I don't understand the purpose of using an EnumList type for a column with an App formula. I would expect it to be a List type, although I imagine it likely doesn't matter for your goal.

---

@loresani wrote:

[_THIS] +
LIST([BILANCIO], [APPORTO_ORALE],[APPORTO_LIQUIDI])

---

This expression will always append each value of all 3 columns in the sequence that you've listed them. In other words, every time you save a change a row, the existing value in the ELENCO_PROB_SCELTI column will have the value from the BILANCIO column appended to the end, followed by the value from the APPORTO_ORALE column, followed by the value from the APPORTO_LIQUIDI column. If changes to more than 1 of the 3 columns are saved simultaneously, there's no straightforward way for AppSheet to know which of the changes was selected first--maybe you could accomplish that by adding the values in sequence via some interim column's initial value expression.

Assuming that the columns are only ever revised and saved one at a time, maybe comparing the current values to the prior values would help you meet your need.

[_THIS] + 
LIST(
IFS([_THISROW.BEFORE].[BILANCIO] <> [BILANCIO], [BILANCIO]), 
IFS([_THISROW.BEFORE].[APPORTO_ORALE] <> [APPORTO_ORALE], [APPORTO_ORALE]), 
IFS([_THISROW.BEFORE].[APPORTO_LIQUIDI] <> [APPORTO_LIQUIDI], [APPORTO_LIQUIDI])
)

---

@loresani wrote:

My ultimate need is to set the last value of ELENCO_PROB_SCELTI in another field

---

If you explain more why you need to compile a running list in the first place even though your goal is only to access the list's final item anyway, someone might be able to recommend a different technique altogether.

[loresani]

Hi,

this formula doesn't work for my puropose (I'm using [_THISROW_BEFORE] instead of [_THISROW.BEFORE]... I think it's your typo)

[_THIS] + 
LIST(
IFS([_THISROW_BEFORE].[BILANCIO] <> [BILANCIO], [BILANCIO]), 
IFS([_THISROW_BEFORE].[APPORTO_ORALE] <> [APPORTO_ORALE], [APPORTO_ORALE]), 
IFS([_THISROW_BEFORE].[APPORTO_LIQUIDI] <> [APPORTO_LIQUIDI], [APPORTO_LIQUIDI])
)

Infact:

1. OK

2. OK

3. UGLY BUT OK

4. WRONG

---

@dbaum wrote:

If you explain more why you need to compile a running list in the first place even though your goal is only to access the list's final item anyway, someone might be able to recommend a different technique altogether.

---

I am trying to do the following: the user have to choose a problem and, depending on the chosen problem, I want to show a different message. 
The elements of difficulty are two:

1. The user must be able to "navigate" through the problems (also to understand what the actual problem is) before clicking the save button.
   Obviously the relevant message must be shown, depending on the selected problem.
   This is why I was trying to create a list of all "clicked" problems and get the last one out (in chronological order).
2. There is no single "problem" column of type Enum (from which the user selects the problem).
   However, the various problems are contained in different columns (I have reported 3 in this thread ([BILANCIO], [APPORTO_ORALE], [APPORTO_LIQUIDI] ... but I have many more), shown according to the values contained in other columns ( if you see the screenshots above, they are the [DOMINIO] and [CLASSE] columns).

Thank you so much,

Lorenzo 

[dbaum]

---

@loresani wrote:

I think it's your typo

---

Yes, sorry for causing confusion.

---

@loresani wrote:

UGLY BUT OK

---

If you don't want entries to be repeated, you could remove duplicates using techniques like the UNIQUE function or list subtraction. Depending on your desired outcome, you could remove new entries from [_THIS] before then appending them or you could append new entries and then remove duplicates.

---

@loresani wrote:

before clicking the save button

---

Again, I don't know of any straightforward way for AppSheet to know which of the changes was selected first but you could investigate creating an additional data source column that captures the values selected in sequence via a creative Initial value expression. Otherwise, if this is critical to your app, I suggest instead using a detail view with pertinent columns enabled for quick edit and maybe limiting your ELENCO_PROB_SCELTI column to the value from just the most recently changed column. You could try an expression like the sample I drafted in the Initial value property and enable the Reset on edit property with an expression that detects changes to the columns of interest.

[WillowMobileSys]

I don't quite understand why an EnumList doesn't work for you.  You are basically doing what that column type does but going through a LOT of extra effort to make your implementation work.

Question:  What happens if a user makes a mistake, chooses the wrong item and needs to remove it?

[loresani]

I can't understand what you mean. My [ELENCO_PROB_SCELTI] column is of type EnumList.

But the problem selected by the user (problem for which I have to show a message) could be only one for a time. So: the user change the problem chosen, the displayed message changes.

[WillowMobileSys]

What I meant was to use an editable EnumList directly rather than choose in one field and then add to the EnumList.   I was hoping to help simplify things for you.

But I don't know your use case and how you are using the data.

[loresani]

---

@dbaum wrote:

creating an additional data source column that captures the values selected in sequence via a creative Initial value expression

---

Hi, how can I do this? I don't understand how to achieve this.

[dbaum]

I don't know. You'd need to experiment. Trying to think through it, I believe it gets very convoluted very quickly, and I don't assume it's possible at all. If it is possible, you might even need multiple columns--one to capture the first value selected, another to capture the second value selected, and another to capture the third value selected. In any case, it's likely a complex combination of nested IF functions to capture a value if it's the only one selected, if it's one of two selected, etc.