EnumList (Ref) Initial Value & Adding new value confronts with each other?

[Anton_Rybak]

Table “Projects” have an enumlist column “Installation Address”, that is ref to table “Addresses”.

1. This enumlist column “Installation Address” have suggested values (selection from table “Addresses”):
   SELECT(Addresses[Address ID], [Company ID] = [Buyer ID])

2. This enumlist column “Installation Address” should have initial value (“the latest” address of the same selection in table “Addresses”):
   MAXROW(“Addresses”, “_ROWNUMBER”, [Company ID] = [Buyer ID])

3. User may add a new item to the enumlist (by adding new address to the table “Addresses”).

All three are working perfectly fine, separately. But when active all together, weird thing happening:

1. Suggested value works, 2. Initial Value is selected, 3. User may add a new address.
   But if user adds new address, it don’t go into enumlist. After saving new address, App brings user back to table “Projects”, with old addresses in the enumlist, and the new address is not there. But if saved with an old addresses, the new address, that entered by user, is saved on the worksheet. So, it seems that enumlist just don’t want to add the new address, if all three features are working together. Any way to fix this?

I also tried to use Valid If instead of Suggested Value:
IN([_THIS], SELECT(Addresses[Address ID], [Company ID] = [Buyer ID]))
But in this case user can’t add new address. Maybe there’s a way to allow user new adds?

Thanks!

[Anton_Rybak · Accepted solution]

After ~8 hours wasted, it is solved. To all those, who want to save some time, dedicated.
The goal is:

1. To have an enumlist column, that holds refs (IDs) of another table (Addresses in my case)
2. To have suggested values filtered to show only current client’s addresses. This is done by setting the Suggested Value: SELECT(Addresses[Address ID], [Company ID] = [Buyer ID])
3. To have in that list of suggested values one to be selected (checked) by default - the most recent client’s address. This supposed to be done this way: MAXROW(“Addresses”, “_ROWNUMBER”, [Company ID] = [Buyer ID])
4. Allow user to add a new client’s address (by clicking “New” in the enumlist selection window).

All of these are working as supposed, except when user tries to add a new address. When the new address is entered and saved, it doesn’t appear in the enumlist. Although, the new address input is saved on the device, and will be saved on the Worksheet, if the record will be saved with any of the suggested addresses.
So, when user adds a new address it don’t appear in the enumlist, as it supposed to. My guess: the Initial Value triggers here and resets the enumlist to initial value (when only the latest address is selected).

The solution:
Instead of Initial Value MAXROW(“Addresses”, “_ROWNUMBER”, [Company ID] = [Buyer ID])
I used this: [_THIS] + LIST(MAXROW(“Addresses”, “_ROWNUMBER”, [Company ID] = [Buyer ID]))
This adds the initial value to the enumlist. The new address, entered by user, appears in the enumlist. But, in this case there will be duplicated. So, the final solution is:
UNIQUE([_THIS] + LIST(MAXROW(“Addresses”, “_ROWNUMBER”, [Company ID] = [Buyer ID])))
This force the enumlist to work properly with all the above requirements together.

[Steve]

Very nice!

[Anton_Rybak]

Small contribution to all, who struggling getting properly sorted List out of EnumList(Refs).
Instead of Dereference should be used multi-dereference, like this:
[EnumList_Tbl1][Referenced_Column_Tbl2]
(without a dot in between EnumList column and referenced column)

[Steve]

For reference:

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.