Power Platform Community Forum Thread Details

Hello - I have a join table that defines the relationship between two records from ANY two tables. The join record includes the TableAName, TableAFieldName, TableAFiledValue and TableBName, TableBFieldName, TableBFiledValue. In th e join table record, I want to use LOOKUP to return the Name from Table A and the Name from Table B. I was thinking something like the following might work:

LOOKUP(TableAName.text, TableAName.TableAFieldValue.text = TableAFieldValue, TableAFieldName.Text)

But every possible version of this I try give me: Invalid Argument Type. Cannot use Unknown values in this context.

Is there another way to retrieve the name from a record if you have the table name, field name and record number (field Name value) as fields within the solution?

Background on selecting this approach: We need to define the roles and responsibilities of people/positions/groups as they relate to several different tables in our system. The traditional approach would be one static join table between two tables for each possible pair of tables, but with so many system tables (and accounting for growth with new tables) and multiple types of constituents (people/positions/groups, each in their own tables), the the permutations get very numerous. We also wish to be able to have a single table view from a record (a staff member, for example) of all the records in the various tables to which they are related. If the Roles were captured in limitless tables, creating, and maintaining these views would require constant collection of data from across the system and constant updates to code to ensure all tables were included in the collection. The Dynamic Join table resolved this, and worked well in a SQL environment. Having trouble replicating that in PowerApps.

Thanks,

Andy.

Categories:

Building Power Apps

PowerApps does not support dynamic table names in LOOKUP() directly, which is why you're seeing the Invalid Argument Type error. Unlike SQL, where you can construct dynamic queries using table names stored in fields, PowerApps requires explicit table references in functions like LOOKUP() and FILTER().

However, there are workarounds that can help you achieve the same goal.

Workaround : Use SWITCH() for Dynamic Table Selection

Since PowerApps does not allow dynamic table names in LOOKUP(), one way to achieve this is by predefining the tables in a SWITCH() function:

Switch(
    TableAName.Text,
    "Employees", LookUp(Employees, EmployeeID = TableAFieldValue, FullName),
    "Projects", LookUp(Projects, ProjectID = TableAFieldValue, ProjectName),
    "Departments", LookUp(Departments, DeptID = TableAFieldValue, DeptName),
    "Clients", LookUp(Clients, ClientID = TableAFieldValue, ClientName),
    "Vendors", LookUp(Vendors, VendorID = TableAFieldValue, VendorName),
    Blank()
)

How This Works:

The SWITCH() function checks the TableAName.Text value.
It then runs a LOOKUP() function on the correct table, retrieving the corresponding Name field.
If TableAName.Text = "Employees", it will retrieve the FullName from the Employees table.
You can add as many tables as needed.