Select Statements: Use value of column as table name

[Alicia_Meyer]

Hi all,

I’d like to use the value of a virtual column as the name of the table in a select statement. Something like: Select(valueOfVirtualColumn, condition…)

Is there any solution or workaround for this problem? To use IF Statements instead is very laborious… we would prefer a generic solution.

Every hint is appreciated!
Kind regards, Alicia

[1minManager]

So I’m presuming the Select() need to reference a number of different tables depening on some condition?

[Alicia_Meyer]

Yes, that’s our issue.

[1minManager]

Ok so as @WillowMobileSystems said you can’t select a table name dynamically so you’re going to have to use something like this

IFS(
[Column]="foo",
Select(Table1[Column],TRUE),
[Column]="bar",
Select(Table2[Column],TRUE)
)

Hope this helps

Simon@1minManager.com

[WillowMobileSys]

Alicia_Meyer:

Is there any solution or workaround for this problem? To use IF Statements instead is very laborious…

Unfortunately, there is not a solution. Table names are static and cannot, at the moment, be dynamic. You would need to use IF() or IFS().

However, the very nature of this request is suspect as to the data schema. If the tables are similar in structure, or maybe even exact, then maybe dividing them into separate tables is the issue.

What is the context of your tables and why are they separated? There likely is another solution within AppSheet to help simplify development and maintenance.

[WillowMobileSys]

Alicia_Meyer:

To use IF Statements instead is very laborious…

It may be that you MUST have the separated tables but I wanted to describe how it could be if they were combined - putting together ​what @1minManager proposed as the solution, your comment above and what I mentioned about combining tables

With combined tables consider an expression like this:

SELECT(CombinedTable[ReturnColumn], [Column] =  [_THISROW].[DesiredTableRow]

In the CombinedTable, the [Column] value would correspond to rows from the separated tables - i.e. "foo’ for all the rows from “Table1” - the first IFS above, “bar” for all the rows from “Table2” - the second IFS.

The [DesiredTableRow] is the dynamic value synonymous with the dynamic table name idea.

Now you can dynamically specify the value of [Column] to get the rows for what was a separated table before - basically the [Column] value is filtering by what was the separated table.

And of course you can add additional filtering logic when needed using AND(), OR(), NOT() ect.

---

The only reason I am hitting this again is that I know from experience that if you have like tables that are separated you will be finding yourself implementing complex IF() and IFS() expressions, like the above, all over the app as time goes on. If the tables can be combined, it will be MUCH MORE EFFICIENT to do it now and fix up the app views, etc, than dealing with the expensive development and maintenance later.

Just a free piece of professional advice.