cancel
Showing results for 
Search instead for 
Did you mean: 

Patch - Referance a column name via a variable

Is that possible to make PowerApps can refer to a column via a variable?

 

I want to use "Patch" function to update column in Data Source. But the number of columns to be updated and columns name is not fixed. So it would be great of PowerApps to allow dynamically refer to a column.

 

 

For example, is it possible to replace the "DataField_8" below with dynamic variable such as "Dropdown2.Selected.Value"?

 

 
Patch(dbo_Update, LookUp(dbo_Update, ProductCode=Dropdown1.Selected.Value), {
    DataField_8 : TextInput1.Text
})
Status: Under Review

We hear you and appreciate your patience.  Unfortunately this is not easy for us to do.  Unlike VBA and other languages, Power Apps is strongly typed and named - everything is well established at compile time.  It's one of the fundamental principles of the product.  This allows us to detect more errors, optimize, and provide better intellisense during authoring.

 

Can you tell us more about the scenarios in which this would be helpful?  Some examples with code would be great.  Perhaps we can find a solution that addresses the need without making field names fully dynamic.

Comments
TjeerdteDuits
Regular Visitor

This is literally a showstopper. I have more than 50 columns i want to be able to edit using patch. Hardcoding these would cost way too much time, especially since these columns change over time. It would only be possible when users for example could select on of the required columns with a dropdown, and then automatically use this selected value in the patch function. 

JKandar
Frequent Visitor

Another use case for this:

 

I need a simple front end interface for users to input data into a SQL Server database.  This is a new database and tables will be added regularly.  Each table needs its own form in the Power App.  Each field in each table also needs to pass defined validation rules, which could include regex matching.  I am able to store all of this data, whether a field is valid, what field in the DB it corresponds to, the value in the field, etc. in a multi-tiered collection hierarchy.  

 

The idea is that whoever takes over the app in the future should be able to add a new form for a new DB table in minutes and not have to deal with laying out controls and adding validation logic, etc.  Following Reza's dynamic form using galleries tutorial, I have got it so that just at the press of a button, I can have one gallery that instantly swaps to whatever table is indicated in the button.  The owning developer can add a new table just by adding a button and setting a variable to the new table name.

 

The one problem is patching the form results back into the database without having to hard code every DB column into the patch statement.  Unfortunately, there's no way to generate a record variable with dynamically named columns (keys).  I have a 2 column table variable with columns "Key" and "Value", and ideally I'd like to iterate over this list and use the text string stored in the "Key" column as the record key, and feed that final record into a patch statement to add the record to my database.

JChouinard2016
Advocate I

Perhaps there could be some sort of function where when placing a text variable inside of it, it converts it to the table's column if it exists, or gives the red underline error if the column name doesn't exist.

 

For example:

 

A drop down with 4 different strings ["Address", "Name", "Phone#", "Email"] is used to select the search criteria for a gallery connected to a SharePoint list. The user types in TextBox1 to search the list. The SharePoint list columns are named "Address", "Name", "'Phone#'", and "Email". In the On Change event of the drop down, the user sets a string variable to the selected value of the drop down: Set(varColumn, dropdown1.selected.value). varColumn is then used to select the column in the filter for a gallery.

 

Filter(SharePointAddressBook, TextBox1.Text in Columns(varColumn))

 

Columns function converts the string into the actual column based on the selected data source's columns, in this case a SharePoint list called "SharePointAddressBook". This Columns function could be utilized in many other ways, such as in a patch function:

 

Patch(SharePointAddressBook, Defaults(SharePointAddressBook),

{Columns(varColumn): TextBox1.Text});

RussW
Advocate I

@GregLi, I've been watching this thread for a while and not seen a lot of traction on it.

 

Maybe a different approach. 

 

Here's an idea:

What if, instead of making dynamic column names based on a variable or value of some sort, how about opening up a given function (not sure if that's the right term) access to the names of the existing columns in the data source?

 

For example, I have a Sharepoint DataSource: SPDataSource

with columns: Column1, Column2, Column3 (data types irrelevant, unless that becomes a programming barrier)

 

Could we conceivably craft a formula that "polls" the datasource for existing column names similar to how we might pull the value or text that is the DATA in that column, but two levels up?

 

For example, instead of SPDataSource.Column1.Text, we somehow have a property for a function to use on the data source that returns an array in a single column collection of all the existing column names in the data source table.

Collect (colColumnNames, SPDataSource.Columns)

 

The returned array can then be parsed in formulas to assign to variables that can then be compared for app logic.

 

I don't know, does this sound more doable than trying to bend the column names to variables we make?

 

I'd be interested to hear others thoughts on this approach - if it makes sense.

  

tfuller
New Member

I need to build a bi-lingual app, but without this feature it's not much better than hard-coding each text box to use an external SharePoint list. Difficult to scale and adapt to the user's needs.