Row id hidden from data table, how to use it for form

[carlosmonte]

My app has a data table pulled from a SQL table. The id column of the SQL table is not displayed in the table. I want to pass the id of the selected row into a flow when the user submits a form, however the parameter mytable.Selected.id is blank. If I add the column to the table, the parameter mytable.Selected.id shows up just fine.

Is there a way to keep the id column hidden but let it be used as a parameter for my flow?

[poweractivate]

@carlosmonte 

Power Apps is capable of fetching all columns of data even if they aren't being displayed in the DataTable.

The reason you might be facing the issue is that, by default, Power Apps retrieves only those columns that are currently being used in the app in an effort to optimize performance.

To access a hidden column like 'id' from your selected item, you need to make sure that Power Apps retrieves it when you load data. You can do this by adding a call to LookUp() function immediately after your data source loads, using the column you want to ensure is loaded. This way, Power Apps will "realize" it needs to retrieve the 'id' column as well.

For example, if your data source is named 'myDataSource', you might use something like this:

Set(myVariable, LookUp(myDataSource, true).id);

In this example, myDataSource is the name of your data source, myVariable is a variable to hold the 'id' value. The LookUp function is called with a condition that always evaluates to true (simply true) to ensure that at least one record is returned. We're not using the return value, but this is enough to signal to Power Apps that the 'id' column should be loaded.

Now, you should be able to use mytable.Selected.id as a parameter in your flow even though 'id' is not displayed in your DataTable.

Please try this and see if it helps @carlosmonte 

[poweractivate]

@carlosmonte 
Another thing you can do (only recommend to do this temporarily) is turn off this setting, which is the one that is performing the optimization:

Settings -> Upcoming Features -> Preview -> Explicit Column Selection - toggle to OFF (but I recommend you turn it back on when done testing, and get it to work with a way like I said with this setting left ON)

[carlosmonte]

I'm still having trouble. Turning the explicit column selection off worked, but not the load trick.

I put this code in the OnSelect for my gallery (selecting an item in the gallery sets the data table visible to true, and filters the data based on the gallery selection.

I've also tried putting it in the OnSelect for the columns in the data table, so it runs every time I click in the data table, and on app load. Still not getting the value to show in this test label.

[poweractivate · Accepted solution]

@carlosmonte 

I see. Well, you can try ShowColumns instead.
In Power Apps, using the ShowColumns function can be a workaround to this issue.

This function can be used to explicitly specify which columns you want to include in your data source.

To apply this to your case, you could modify your formula that loads data from your SQL table like so:

Set(myDataSource, ShowColumns(SQLTable, "id", "Column2", "Column3", "..."));

Replace SQLTable with the name of your SQL data source, "id" with the name of your hidden id column, and "Column2", "Column3", "..." with the names of all other columns you want to include.

After this, you should be able to access the 'id' value of the selected item in your DataTable with mytable.Selected.id

You could then modify the Items property of your data table to include the ShowColumns function as well:

ShowColumns(SQLTable, "id", "Column2", "Column3", "...")

Remember to replace the names as described above.

This method should help make sure that your 'id' column is loaded into your DataTable.

See if it helps @carlosmonte 

[carlosmonte]

I wasn't sure where to put the first snippet of code, but putting the second in the Items section of the table worked.

[poweractivate]

@carlosmonte Yes essentially you take where you put your whole Data Source and just put in that ShowColumns wrapped around it instead. It also may work if ShowColumns is outside, but it may be easier to put it inside.

However when dealing with record limit it may be technically better to put ShowColumns on the outside of the Filter function not the inside. The reason is if you're filtering down to below the record row limit (500, raise-able to 2,000 max) the innermost function should be the one that is bringing the records down to under the record limit, that's usually the Filter, and usually this filter may have multiple &&, etc. 

As always, whether there's a ShowColumns or not, delegable or not, make sure to design your app so the innermost function call returns below the record limit.

if you have a sensitive scenario like that, your ShowColumns on the inside can mess it up, as it'll process only the 1st 500 (or at most 1st 2,000 records).

Delegable or not just doesn't matter

(it does matter though, as non-delegable may stop at the first 2k of your say 10k large data source - delegable on data source side may go past the first 2k entries, but then the final processing stops at the 2k'th final filtered record, which in some cases may still be incomplete if the filter has more than the record row limit e.g. in the 10k data source, there were more than 2k records satisfying the filter criteria).
Note that ALL individual function calls are subject to the record limit. So if you had a Filter that had multiple && clauses and brought it down to below the record row limit that should be the very innermost function call (the filter inside) and the ShowColumns and everything else like the ShowColumns wrapped outside. 

Just in case that may help to keep in mind @carlosmonte