Dynamically Display SharePoint Columns based on month

[KyleShriver]

Hello-

I am looking to dynamically display columns in my app based on the current month.

In my SharePoint list, I have a column for every month.

In my PowerApp, if the month is April 2019, I want to be able to display the column 'April 2019' and the next 6 columns. When the month switches to May, I want to display the next 6 columns after that.

I have created variables that will store current month, and can do some DateAdd with them... but can't figure out how to use the variable(s) as a column in a gallery.

[v-yuxima-msft]

Hi @KyleShriver ,

You can try the following workaround, and I test as next:

Data Source:

App Test:

Gallery.Items:Filter('20190416dynamicallyMonth',Month(DateFrom) >= Month(Today()) && Month(DateFrom)< Month(Today())+6)            ('20190416dynamicallyMonth' is my data source name,you can change to yours on your side)

Hope this could be helpful.

Best Regards.

Yumia

[Mr-Dang-MSFT]

Hi @KyleShriver , this is a good question about data architecture: what are best practices for structuring columns?

I recall answering a very similar question recently, but I've not been able to locate the link. You can try searching for 'relational data.'

Big idea: having each column as April 2019, May 2019, June 2019... etc. is a useful view in spreadsheets. In a database though, because each column name is different, doing searches is very difficult with changing columns. Instead, it is better to collapse all the date data into a single date column. 

Depending on your datasource, the table can be filtered to show data in different date ranges. 

[KyleShriver]

Hi @Mr-Dang-MSFT , 

Thanks for bringing a different perspective. I'm not quite clear on what you are recommending though.

This application is actually coming from a spreadsheet perpective that has become too massive and I'm working on redesigning.

My datasource is going to be SharePoint. The application is used to track/plan a person's time/allocation across projects. Currently, a person will have several records, one for each project. Each of those records could potentially have different allocations for their time. Say, 10% of their time in April, and 25% of their time in May.

[Mr-Dang-MSFT]

Hi @KyleShriver, sorry I did not go into more detail. It is indeed a big question on how to best setup a table so you could run filters against it. 

Let me loop in @Drrickryp @who had written a very good piece on relational databases. Also adding @seadude @DavesTechTips @KroonOfficeSol who are very disciplined with setting up good tables and can share some best practices here.

[KroonOfficeSol · Accepted solution]

@KyleShriver 

First because of the goal of your app I expect that the number of lines will get big over time. From this I would advise you to go for SQL as your datasource if you can. With SQL you will be able to make your apps preform quicker and more reliable. Also extracting data in reports, digital file like EDI/XML and Power BI will be a lot easier and more flexible. Also retrieving data to PowerApps is far easier, for example if you have a datecolum you can extract the last 30 days by a simple formula:

Select [columns] for table
Where 1 = 1
AND DateColumn = Getdate() - 30

If you still want to use SharePoint you should add one or two number columns extra to your table beside the datecolumn you have for the date of the time registration. One for the year (2019) and one for the month (01,02,03,04, enz.). I don't do a lot with SharePoint but I thing you could make this a calculated column based on the datecolumn. If this is not working you can use SharePoint Designer 2013, which you can download from your Office365 home screen - Install Office - Other Installation Options - Tool & Add-In. With this designer you can add a tricker on your list which calculates the year and month after created/Modify.

This fields you use to filter the records you want to return to PowerApps and will give you delegatable formula options.

Look at how to set-up a nice list like solution you could check my YouTube channel, were I share some of the design patterns I use. Maybe start with this video
https://youtu.be/f-OZvEGdq9c?list=PL2zsZutKZiTPBnHR1-NnqewollxBfpqbE 

Hope this helps.

Paul

[DavesTechTips]

Hi everyone,

I agree that adding multiple columns to the data for each month seems to present more challenges in future than what it is worth currently.

I also agree that SQL would be more suitable in the long term and would allow for much more options for the specified requirement. It will also allow for a computed integer column to be added (and marked as persisted) in order to do = , > , < operations against as is suggested above by @KroonOfficeSol and @v-yuxima-msft

If you have to use SharePoint however, date filters are unfortunately not delegatable which means that when you reach 500 (can be increased to 2000 under app settings) records in the list, your filters will only ever use the first 500 (or 2000) records which is less than ideal 😉 Another unfortunate thing about SharePoint is that calculated columns are not delegatable either so you won't be able to add a calculated int column as is the case in SQL. Depending on the type of application you could have added choice columns for the year and month for the user to select, but unfortunately choice columns are not delegatable either.

The other option would be to use Flow to fetch the data and return it to PowerApps. Flow can use ODATA queries to get to the correct data and then possibly manipulate or join the data with other services before returning it to PowerApps. I love this approach and it has come in extremely handy in the past. Some might say that using a flow for retrieving data might feel 'expensive' but it does allow for many different functional and security options going forward.

I made a quick video to demo what can be achieved with this. Please see below and let us know if you have any questions or suggestions:

https://youtu.be/m_j_xN7-LjU

Twitter: @dawidvh

YouTube: https://www.youtube.com/user/dawidvanheerden
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions. AND we get points for it 😉

[KyleShriver]

I've set up an Azure SQL db and its definitely the way to go. You all inspired me to think in a more relational database perspective, so thank you all.

[Mr-Dang-MSFT]

Thanks @KroonOfficeSol and @DavesTechTips  for your suggestions, rationale, and even links to videos. I knew you'd be the right experts 🙂