How to set the initial value of an EnumList as I've tried everything and it does not work?

[StephenHind]

I have logged with support but haven't got a solution yet so I'm turning to the community - you're my only help!

I have made a simple text-based EnumList on the [Courses] column of a row in the Attendance table:

The goal is to make the initial value of this EnumList the same as the value for the same day last week and I've used this formula to get that data:

SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], ([Date] = [_THISROW].[Date] -7)))

The formula works when tested:

As you can see for "John Rae" for today (06/03/2024) the returned value is "English , Science" which is the value of [Courses] from the previous week.  Yet this does not set the [Courses] for today when used as the initial value.

I even tried simply setting the initial value to be LIST("English","Science") and still the EnumList was not set.

I thought it was maybe the fact that the Editable formula was preventing an initial value, but I tried removing it and still it's not working.

Any ideas are greatly appreciated.

[Markus_Malessa]

Not sure if this will solve your problem, but there are some issues when using SELECT() of an EnumList column, because it quasi returns a list of lists. So try the following to see if it works correctly:

ANY(SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], [Date] = ([_THISROW].[Date]-7))))

Try this first, if you still have issues then we can start exploring some other possibilities.

[StephenHind]

@Markus_Malessa thank you for the suggestion: I think I tried that earlier but, nevertheless, I tried it again and there's no difference: there's still no initial value.

I'm more concerned that I can't even get an initial value if I simply just specify it manually asa list, i.e.

LIST("English","Science")

Didn't work either, so I'm confused about what is acceptable as an initial value.

[Markus_Malessa]

As @scott192 pointed out, pay particular detail to your separator for your enumlist. The default I think is " , " (space comma space). Just for additional testing see if you can preset the initial value like this:

{"English" , "Science"}

[scott192]

In your screenshot I cannot see an item separator specified for your enumlist column?

It looks as if your Select formula is returning the items as a space comma space separated list?

So try adding space comma space as the item separator for your enumlist column and see if you get any joy?

[Suvrutt_Gurjar]

---

@Markus_Malessa wrote:

but there are some issues when using SELECT() of an EnumList column, because it quasi returns a list of lists. So try the following to see if it works correctly:

---

I think @Markus_Malessa is correct in his causal analysis.

Please try an expression of 

SPLIT(TEXT(SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], ([Date] = [_THISROW].[Date] -7)))) ,",")

A SPLIT() will help flatten the list of lists into a simple list.

[Markus_Malessa]

Emphasis on "flatten the list". I think Google needs to absorb that term into their documentation.

Have you noticed any difference using SPLIT(TEXT(SELECT())) vs just SPLIT(SELECT()) when using select on EnumList columns?

[Suvrutt_Gurjar]

If my memory serves me right, I think @Marc_Dillon had made this nice suggestion of using TEXT() first before using SPLIT() as use of only SPLIT() fails at times. I will post his recommendation post , if I can find it on search. I had found his recommendation very useful and it always works.

Also I believe the "flatten the list of lists" term was used by @Steve first. It is so appropriate for the case.

[Marc_Dillon]

Yes, always use the TEXT() first. This makes sure to retain the standard space-comma-space delimiter, which you can then SPLIT() by. Using just SPLIT by itself tries to use a different delimiter, which can work in some cases, but it also produces erratic behavior in some other cases.

[Suvrutt_Gurjar]

Thank you Marc for putting it succinctly. 

[Suvrutt_Gurjar]

Hi @Markus_Malessa ,

Here is a post on TEXT(SPLIT(......)) by @Marc_Dillon 

Solved: Split in Valid_If not allowing me to select and sa... - Google Cloud Community

I believe there are more posts as well in this regard by him.

[StephenHind]

Many thanks for all the suggestions: none have worked so far:

1. I manually specified space comma space as the item separator and that made no difference
2. I tried manually assigning the initial value as 
   

   {"English" , "Science"}​

   and it didn't set initial value
3. I tried various combinations for the new idea on flattening the lists: which looks like there must be some kind of list of lists going on:
   

   SPLIT(TEXT(SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], ([Date] = [_THISROW].[Date] -7)))))​

   When I test gives an interesting response of 

   English , , , Science

   I tried combining with ANY (as there should only be one result):

   SPLIT(TEXT(ANY(SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], ([Date] = [_THISROW].[Date] -7))))))​

   but got exactly the same
   
   

   I tried adding TOP() also as another way to get only 1 and still no good.
   
   

[scott192]

SPLIT(TEXT(SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], ([Date] = [_THISROW].[Date] -7)))))​ - LIST()

Add the - LIST() to the formula of your interesting response...this will remove the empties.  Edit*  You may have to wrap the intial part of the formula in a LIST() expression first.

LIST(SPLIT(TEXT(SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], ([Date] = [_THISROW].[Date] -7)))))​) - LIST()

[Suvrutt_Gurjar]

---

@Suvrutt_Gurjar wrote:

Please try an expression of 

SPLIT(TEXT(SELECT(Attendance[Courses], AND([Student] = [_THISROW].[Student], ([Date] = [_THISROW].[Date] -7)))) ,",")

---

Sorry there was some part missing in my earlier suggestion, that I had corrected in the same post. Please note a "," at the end of the expression that needs to be there.

[Marc_Dillon]

---

@Suvrutt_Gurjar wrote:

Please note a "," at the end of the expression that needs to be there.

---

Should be: " , "

[Suvrutt_Gurjar]

---

@Marc_Dillon wrote:

Should be: " , "

---

 Marc. , oh , yes my bad for that inadvertent but vital miss of spaces.

[scott192]

I addition, it is more worrying that you cannot set an initial value.  Have you got something set in the Validate section of that column?

[StephenHind · Accepted solution]

I think I have found the issue and it's nothing to do with any of the formulas: I'm trying to edit an existing row, rather than making an entirely new row: I didn't realise that if it's a pre-existing row and you edit/quick edit from a detail view then even if the column is empty you don't get the initial value.

I need to take a different approach, e.g.  use a bot to set the values when the row gets created.

I'm disappointed that support has not spotted any of this in our interaction that I'm trying to do something that AppSheet cannot do (yes I can set the "Reset on edit?" option, but I don't want that if there's actual data there).

Thanks for all the suggestions as they will be useful in the future.

[scott192]

You could put a formula on the column's Reset on Edit section so that if you make any changes to the row and the current value of your enumlist is empty then it will fill in the initial value again?

[StephenHind]

Reset on Edit only works if using a Form: it doesn't work when it's a Quick Edit.  We can't use the form as we want to display action buttons (there's another Courses option "No Work" which is not on the EnumList and requires confirmation once pressed to make sure it's something required)

[scott192]

Are you sure that Reset on Edit is only for forms?  I use it all the time and thought it meant that if any part of the row is editied (by anything) and the reset on edit flag is chosen for a column then it resets to the initial value.

[StephenHind]

Yes I double-checked; I added the Reset on Edit formula with a manual list to test:

Tried from the quick edit:

and, as you can see, no initial value. If I went to edit, however, the manual list was present as the initial value:

The Reset on Edit formula would be an option if we didn't need the action buttons, then we could use the form.

[scott192]

Just to pacify me (sorry 😀) could you for the sake of testing, make the status column on your form for the John Rae record a quick edit one as well?

Then go to the John Rae record and modify the status?  Does the Courses value fill in automatically then?

[StephenHind]

@scott192 yes: if I add another column as a quick edit and edit that then the quick edit on Courses does reset and get the initial value, but we only need to edit the Courses, so pre-filling with the bot is the approach we will more likely take.

[scott192]

I think going with the bot method is the way to go for your case.  I just wanted to verify that quick edit fields can reset as well rather than having to be done from a form 😁

[Markus_Malessa]

Can you clarify why you are trying to edit an existing row? Or are you using copy the current row into a new form functionality? If this is a new attendance row under a student for example I am unclear why editing a current row would be necessary.

[StephenHind]

A bot populates the attendance record daily for the student to pick the courses they have done and the parent/guardian to "sign" to say it's done.  The bot also emails to say it's there ready to take data.  If a day is missed then it's highlighted to say it still requires completion.