Provide Developer control at the TABLE level over which Lists are auto-generated as columns

[WillowMobileSys]

Add the ability at the Table level to pick and choose which “proposed” auto-generated Lists are actually added to the table’s column list.

I envision this being a new expandable section within each Table listed under the tables tab. The section presents a List of possible auto-generated virtual column lists in EnumList format - i.e. a checkbox next to each possible list to mark as “include”. By default thy are ALL marked. And of course the standard “choose all” and “clear all” choices.

This provides a single cohesive place where a developer can make these choices for THAT table rather than having to navigate to several table columns to choose if a reverse reference of other auto-generated list should be produced.

Auto-generated Virtual Column Lists are a beautiful thing…when they are needed. However, there are many instances when they are not needed and they clutter up the column lists. More importantly, they unnecessarily increase the Sync times not to mention ALL of the additional burden placed on AppSheet servers across all apps for things that will never be used.

---

A Similar request but choice made at the column source

A separate Feature Request was suggested to provide control over a generated reverse reference list at the source column.

Data only references || Meaning: they produce no reverse reference list Questions

When you create a reference between tables, a reverse reference list will be created in the parent in the form of a “Related Whatever” virtual column that’s a list. [image] [image] Sometimes though, you only wish to create a reference so you can use the de-reference system - or to otherwise hold that information as a variable. (Such as who the “lead” for a project is) But whenever you create a reference, the reverse reference list is automatically created. In fact, if you make any signifi…

[MultiTech]

WillowMobileSystems:

Auto-generated Virtual Column Lists are a beautiful thing…when they are needed. However, there are many instances when they are not needed and they clutter up the column lists. More importantly, they unnecessarily increase the Sync times not to mention ALL of the additional burden placed on AppSheet servers across all apps for things that will never be used.

@WillowMobileSystems my friend, AppSheet gave us the tools to solve these problems - they just didn’t explain them very well.

That feature request you referenced from me - literally answered and solved, but never communicated as such.

---

Enum & EnumList (BaseType: Ref) - De-Referenceing

With the ability to de-reference from an Enum (with the base type ref), you can now eliminate the problem of all these “extra” related lists.

Instead of marking a column as a reference, make it an Enum with the base type ref (you’ll have to save the editor, then you can select the table the reference is to) and you can de-reference from that!

Now you have the power of de-references without the overhead of related lists.

---

In fact, not only can you de-reference from an Enum column type… but you can also list de-reference from an Enumlist!

I’ve put together a simple sample app showing how to accomplish both:
https://www.appsheet.com/samples/Showing-how-to-dereference-from-enum-columns?appGuidString=038fd74c...

((((I’ve updated the referenced feature request with a video explaining the app, here’s a link if you wish to view it))))

[WillowMobileSys]

Ok, got it. I made a change to one column and that required a few other column changes for the types to match up across the app where the data value is passed. Not very intuitive but if that is the design intention, I’ll make the changes.

Now, let’s say I decide in the future that I DO want see several of these reverse reference lists? I have to change the columns back to Ref type. Maybe that’s the expected way to handle the change?

I think I still prefer explicitly indicating to include the virtual columns or not. Having to make column type changes to show or hide these auto virtual columns just doesn’t make sense to me in a rapid development platform. Plus, the decision would be made at the most logical place, the Table potentially receiving the columns rather than at each source of the column.

[WillowMobileSys]

With these Enum/Ref and EnumList/Ref usages, have you experienced yet assigning a value from the EnumList to an Enum column?

Here is my usage. I have a list of options represented by an EnumList. A user chooses a single option BUT if there happens to be only a single value in the EnumList, I want to default to that value.

I am using this expression:

       COUNT([Product Code].[Duty]) = 1,
               ANY([Product Code].[Duty])

But getting the error:

This worked as is when the column was defined as Ref. The change that produced the error was simply changing from Ref to Enum/Ref.

It doesn’t seem to like the value returned by ANY() being assigned to an Enum. Enum does have the same source table defined as the EnumList - “Options”.

Any ideas how I assign the single EnumList value to an Enum?

[GreenFlux]

WillowMobileSystems:

Any ideas how I assign the single EnumList value to an Enum?

Try storing it in a new Ref-VC first, and then use that in your IFS().

Release Notes: 7/17/20 Feature Release Notes

Thank you for this! I’m just now getting a chance to experiment with it. We can now dereference an ORDERBY list of Refs to get other values from that row and sort by any column. Awesome! However, I’ve noticed you can not dereference the ORDERBY() expression directly. The ordered Ref-list has to be stored to a Virtual Column first, then that column can be dereferenced to a new list. ORDERBY(<<LIST_EXPRESSION_OF_REFS>>)[Column] does not work, but [OrderedRefs_VC]= ORDERBY(<<LIST_EXPRESSION_O…

[Steve]

What does Expression Assistant say the expression is doing?

[MultiTech]

WillowMobileSystems:

It doesn’t seem to like the value returned by ANY() being assigned to an Enum.

I’ve discontinued use of ANY() in preference of INDEX()

It seems to me that ANY() removes type qualifier data sometimes, whereas INDEX() preserves it.

[MultiTech]

WillowMobileSystems:

ANY([Product Code].[Duty])

hang on…
is [Dute] a list???

if it’s not, remove the ANY() from around that, you just need the de-reference from the enum for the value.

[WillowMobileSys]

@MultiTech_Visions

Sorry, I thought I had withdrawn the post as it really should be a Question. I ended up wrapping the ANY() with TEXT(). That seems to have resolved it.

[WillowMobileSys]

Yes its a list. It should have been named Duty Options…but I was lazy!

[MultiTech]

In this case I would use

INDEX([Duty], 1)

• this will likely preserve the root type of the enumlist and carry it over.

[WillowMobileSys]

I tried INDEX() first thing. It didn’t make a difference. But your right, it is probably a better option.

What is also interesting is that I am changing other Option columns that have the same exact pattern as the problem one and they are not complaining about the use of ANY().

I’m going to try to refresh the editor environment as I can’t see any other differences or problem area.

[WillowMobileSys]

WillowMobileSystems:

       COUNT([Product Code].[Duty]) = 1,
               ANY([Product Code].[Duty])

@Steve @MultiTech_Visions @GreenFlux

I finally found the issue. The error above was reporting inconsistent type on “Input 4”. So that’s what I was looking at. It really was input 2 that was the problem - I hadn’t update the source table of that Enum/Ref.

[MultiTech]

It’s usually some type qualifier somewhere down the chain that you THINK you’ve set.
Happened to me so many times… lol

[WillowMobileSys]

@GreenFlux @MultiTech_Visions @Steve

So I have gone through my app and changed many columns from Ref to Enum/Ref to prevent the “Related” lists from being automatically built where they are not needed. For the most part this works fine.

However, Enum/Ref’s don’t show the “Add” button to be able to add new records. And as far as I can tell I can’t get it show for this type of column. So I am still forced to used a Ref column to retain that ability.

A Prime example of this are shipping addresses when placing Orders. If I am in the middle of inserting an order and find out the needed shipping address is not yet in the system, I want to be able to add the address right there. But I don’t really care to see Orders related to the Shipping Address. So it would be nice to be able to Appsheet not to create the Related list.

On the other hand, maybe Enum/Ref’s should show the Add button just like Ref columns do?

[MultiTech]

WillowMobileSystems:

Enum/Ref’s don’t show the “Add” button to be able to add new records

True

WillowMobileSystems:

maybe Enum/Ref’s should show the Add button just like Ref columns do?

This would be nice; tie it to the “Allow Others” toggle or something.