Hide a Data Table column based on contents?

[breck]

Has anyone ever hidden a column in a Data Table in a canvas Power App based on the absence / presence of values?

Like, when the user changes a filter on a Data Table such that the current set of records have no value in text Column 4...hide that field?  And show it when it again contains text values with length > 0?

Doesn't seem like a "thing" but wanted to check just in case.

Thanks!

[RandyHayes]

@breck 

Yes...you are stating that you are having to repeat the formula of your Items property of the datatable each time!

There is a way to do this.  It is very little known in PowerApps!  It is what I call a dynamic record variable.

There are steps involved in creating one - something you are interested in knowing??  I can elaborate. 

[breck]

Yes, please!  I'd like to know how I can set this filtering in one place into some variable, and just use that in examples like this!

[RandyHayes]

@breck 

Okay....here we go.  The secret to this is a DataCard.  Not a datacard for a Form, but a DataCard for a Canvas object.  It has one of the most powerful and yet most unknown properties on it - the Update property.

Here's how to get one:

1 - Create a new screen in your app and choose the Scrollable screen when you add it.

This will create a new screen and in it (looking at your left control tree) there will be a Canvas control (most likely called Canvas1 - I will refer to it as Canvas1 for the rest of the instructions)

2 - Click on Canvas1, then in the design surface, grab any top corner of the canvas control and drag it to make the canvas relatively small.  The size of it is not important, just simply that you resize it to cause PowerApps to reset some of the Y, Height and Width properties back to hard values (they will be formulas at first).  This will keep any little red X's from coming up for the next steps.

3 - Now, with the Canvas1 control selected, cut it from the screen (ctrl-X or from the menus)

4 - Return to your screen with the datatable and paste (ctrl-V or from the menus) the Canvas1 control onto that screen.  Having it on the same screen is not relevant to making all this work, but it is more contextual and relevant to this screen, so best to have it there.

5 - Set the Visible property of the Canvas1 control to false - we don't need to see it...we just need to use it.

6 - NOW, in the Canvas1 control, expand it, and you will see a DataCard1 control in it.  You might at this point rename that to be something more related to what you are about to use it for...like: dcdDataForTable

7 - Now go to the Update property of dcdDataForTable.

The Update property is a property that defines the Output of the DataCard.  It can be ANYTHING!!  Well, it needs to be a record, but it can be defined any way you want.

8 - In the Update property add the following formula:

{
    Data:
        FirstN(
            Filter(sqlSourceData, 
                LineID = ddlLines.Selected.LineID,
                !(toggleSpecDate.Value) || (ProducedDateText = Text(DatePicker1.SelectedDate, "mm/dd/yyyy")),
                !(toggleSpecProduct.Value) || (ProductCode = ddlProducts.Selected.ProductCode),
                txtFilter1.Text = "" || txtFilter1.Text in LotNumber
            ),
            10
        )
}

That is it!  You now have a dynamic record variable in your app.  It is to be referenced as dcdDataForTable.Update.Data

So, set your formula in the Items property of your DataTable to dcdDataForTable.Update.Data

And NOW, for your column Visible property:

CountRows(Filter(dcdDataForTable.Update.Data, !IsBlank(sqlFieldName))>0

(note: you don't need all the If and true false stuff...the above already evaluates to a true or false, adding the rest is redundant)

You now know how to make and use a dynamic variable!  

[breck]

I love this, and the step-by-step is amazing, but something about it (my application of it) causes my Data Table to stop displaying the contents of some fields.  Still chasing it.

Some of those fields (which display their contents the "old way" but appear empty with the data card trick) do report as "custom" now.  Maybe it's relevant that these fields with disappearing contents are all number or date/time fields.

Happens at design-time and after deployment.  I'll attach a screenshot just to illustrate.

[RandyHayes]

@breck 

Yes, this is a powerful method!!  Use it wisely!  That is to say, because you are making things very dynamic and highly flexible with it, there are times where you might go overboard and performance can suffer from it.  This simple scenario is not one of those times.  But, the more you use it, the more you start to add to it and find it is so awesome!!

So yes, there is a little fluke in the designer in the way it infers columns after they have already been added.  You can first start by removing the column from the field list and then adding it back.  That will get rid of the "custom" designation (which really is not relevant anyway).

But...either way, it should be still showing the same data as before as the table and the schema of the records is identical.