Excel Data Filtering based on a Text "Date" Column

[Anonymous]

Hello all,

I've done a bit of searching on the site to make sure I'm not repeating a problem that's already been solved but I couldn't find a solution that worked for me.

I've got a column, in Text Format, titled "Date_Logged". I also have two DatePicker controls "contStartDate" and "contEndDate". I am trying to Filter() a table based on something being between the two dates picked in the controls. Currently I have:

Filter(
    Table1, contStartDate.SelectedDate <= DateValue("Date_Logged") And contEndDate.SelectedDate >= DateValue("Date_Logged")
)

I am using the DateValue function for the first time in my experience here, so it's possible that the error lies there. I am getting a Boolean value error for the Filter() function.

[RandyHayes]

@Anonymous 

There are options for searching on other types of fields beside text, but let's solve the error you're seeing first.

Try these troubleshooting steps:

1) Change your formula to this:

Search(
   Filter(DiveLogs, 
          contStartDate.SelectedDate <= DateValue(Date_Logged) && contStartDate.SelectedDate >= DateValue(Date_Logged)
          ),
   Switch(Radio1.Selected.Value,
       "PERNER", TextInput3.Text,
       "Last Name", TextInput3.Text,
       "First Name", TextInput3.Text,
       "Hub ID", TextInput3.Text,       
       "Team", contTeam.SelectedText.Value,      
       "Location", contLocation.SelectedText.Value),

       "PERNER", 
   )      

2) See if you have an error with that.

3) If not, then change the last line "PERNER" to "Name".

4) See if there are issues.  If not, continue changing that field to "Hub_x0020_ID", "Team" and then "Location"

5) See which one gives you the error.

Once we have an idea on the types of fields that PowerApps is seeing, we can re-craft the formula to take it all into account.

[Anonymous]

As I cycle through the individual columns as you've suggested, the error goes away for all of them, including the ones I had in question.

As a side note, although the Items property is error free, the filtering does not work as intended. I'm not sure if this is due to the way we've got it programmed for the troubleshooting we are doing or not.

For example, if I've changed the Column to "Location" in the Search syntax and then search by Location and input dates that make sense, the table displays no items.

[RandyHayes]

@Anonymous 

So, a couple things...

1) How big is the data in the Excel file?  Are we talking about a lot (many rows)?  If so, how many?

2) As for the filter/search formula now - I'm not sure from your post if you cycled through them and the issue went away, or if it still remains.

[Anonymous]

1) Currently has roughly 150 rows of data with the potential to increase greatly as the year progresses.

2) With each individual column tested, no errors were given.

[RandyHayes]

@Anonymous 

So, I would consider what "increase greatly" will mean.  Excel has limits on the number of rows it can work with.  If we're talking about the potential for thousands of rows, then you might want to make some considerations on what to use for your datasource.

On point #2, so each individual column tested had no errors...how about the formula we are trying to put together?  Is it working properly or still displaying and issue?

[Anonymous]

Thank you for your help.

First, currently given our tech situation, Excel is the only database design we can work with. There's no plan to move on to bigger, better database software and so, the Excel sheet will be refreshed quarterly.

As for the formula, when one column is put into it and the others removed, it displays no data, although it should be. There are no errors given either.

[Anonymous]

I've had some time to play around with the formula you've given me. As it turns out, the table does in fact display proper data based on the given Date range, but only if the 'column' syntax of the "Search" function is in fact a specific column, and not calculated based on the second Switch function that you've included in your orignal code.

Any idea why this might be? Or if not, maybe a workaround?

[RandyHayes]

@Anonymous 

Oh I'm glad you reposted something to this thread...I had meant to respond to your last post, but it fell off my radar.  Sorry about that.

So, in response to your previous post:

I am not sure if you are coming into this with an Office 365 for Business license or if you are just using PowerApps as a license.  If you are Office 365 based, you have access to at least use SharePoint as an alternative to Excel.  This would give you much greater flexibility and less problems.  But...for the moment, we'll assume not and Excel is what you have.

Moving on...let's recap.  Is your current formula this or something else?

Search(
   Filter(DiveLogs, 
          contStartDate.SelectedDate <= DateValue(Date_Logged) && contStartDate.SelectedDate >= DateValue(Date_Logged)
          ),
   Switch(Radio1.Selected.Value,
       "PERNER", TextInput3.Text,
       "Last Name", TextInput3.Text,
       "First Name", TextInput3.Text,
       "Hub ID", TextInput3.Text,       
       "Team", contTeam.SelectedText.Value,      
       "Location", contLocation.SelectedText.Value),
   Switch(Radio1.Selected.Value,
       "PERNER", "PERNER",
       "Last Name", "Name",
       "First Name", "Name",
       "Hub ID", "Hub_x0020_ID,       
       "Team", "Team",      
       "Location", "Location")
   )

What, if any, errors do you see on the above formula?

What, if any, results do you see from the above formula?

[Anonymous]

Thank you for the reply, That was the code that I had, however I just messed around a bit and used this instead:

Search(
   Filter(DiveLogs, 
          contStartDate.SelectedDate <= DateValue(Date_Logged) && contStartDate.SelectedDate >= DateValue(Date_Logged)
          ),
   Switch(Radio1.Selected.Value,
       "PERNER", TextInput3.Text,
       "Last Name", TextInput3.Text,
       "First Name", TextInput3.Text,
       "Hub ID", TextInput3.Text,       
       "Team", contTeam.SelectedText.Value,      
       "Location", contLocation.SelectedText.Value),
 "Name",
"Team",
"Location"
   )

And now the filtering works as it should. It's seeming as though PowerApps is considering everything after the first 'column' syntax as more columns. The table is only returning values for the specified date range based on the radio selection button, which is what I needed so for now it seems to work.

Side note; we are coming from a business license and do have a SharePoint however I've heard that SharePoint Lists are very not-user-friendly and haven't done much research into that instead of Excel. Honestly, I wish I had access to Azure or something similar but that's not a possibility currently.

[RandyHayes]

@Anonymous 

This is correct, the syntax of the Search function is:

   Search(  datasource,  text to be searched for,  field(s) to search in)

So in your case, you are searching on Name, Team and Location all with the value that is determined in the Switch statement.  This might be a place to put it on the shelf for a while as it seems to get you what you need, but, technically, it is not what you originally wanted and may cause some confusion later.  So, perhaps circle back on it later.

I wouldn't shy away from SharePoint as a data source.  It's a different world than Excel for sure, but it brings a lot of user-friendly comparability to the table and it brings a ton of functionality that you'll never get with Excel (not to mention the whole sharing thing which, with Excel, you'll have to do with your OneDrive), 

AND...what's more, you are creating an App that interfaces with your data, so, technically, the user would never need to worry over "user-friendly" as your App will be the *ultimate* in user friendly.

Anyway...if you're good for now, then carry on.  If you have any other issues on this formula or other problems, just post back again.