PowerApps Filter - Table name as a variable

Question

My PowerApp application decides the Share point list to filter based on user details. For example, the Share Point list would change based on the region the user belongs to.

Share Point List: SalesTableEU SalesTableUSA

The mapping of User and the Sharepoint list to use is in another table named UserSettings.

First, i did a look up on UserSetting list, returned the SPListName (TableName) based on the user. This saved in a variable SelectedDS by

Set(SelectedDS, LookUp(UserSPLMapping, Title = User().Email, SPListName))

How should i pass the table name to the filter function based by this variable name - SelectedDS.

SortByColumns(Filter(SelectedDS, StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

I do not get the gallery populated when i use this variable, however when i hard code the table name, the gallery is populated.

Can you help me where i'm wrong and provide me a solution.

SeaDude SeaDude · Accepted Answer · 2020-10-22T04:18:17

I've successfully done this with Form controls in a DEV/PROD scenario but its buggy and has a few constraints but you could adapt it to a Gallery control:

Example:

The column names have to be EXACTLY the same between the two Sharepoint lists
- You cannot dynamically swap tables in PowerApps and expect a Gallery/Form to know how to display the fields.

I typically use this scenario as follows:

Radio button (rdoEnv) with "Dev" and "Prod" values
OnVisible of the start screen:

If(
    rdoEnv.Selected.Value = "Dev", 
        Set(varEnv, "Dev"),
        Set(varEnv, "Prod")
)

Then for the DataSource property of the Form control:

If(varEnv="Dev", SharepointList1, SharepointList2)

I've also successfully used this with Patch() statements:

Patch(
    If(
        varEnv = "Dev",
        SharepointList1,
        varEnv = "Prod",
        SharepointList2
    ),
    Defaults(
        If(
            varEnv = "Dev",
            SharepointList1,
            varEnv = "Prod",
            SharepointList2
        )
    ),
    {
        exactColumn1: "test",
        exactColumn2: "Yup"
    }
)

One (ridiculous) caveat here... If you want to change a column name/data type/etc. in Sharepoint1 (or vice versa), you need to set the DataSource of the Form control to Sharepoint1, make the change, THEN set it back to the If(dev/prod statements above.

A bit hacky, but I've got it in production for some PowerApps. Its kind of nice for devs to test 1 app between DEV/PROD data sources.

PowerApps Filter - Table name as a variable

1 Answers