Still issues with simple date compare with Oracle table

(2) Share

Report

Posted on by bdherh

I'm still having issues with doing date compares with an Oracle table date (which is actually a date/time). I get no errors but the filter is not doing anything.

My table is as follows

My date selection is as follows. I want the user to select the date week and/or day of the week.

I have tried to use collections and direct table select filter my Items property of the gallery.

I banged my head against the wall on this a week or so ago and then left it to do something else. Now I'm back and need suggestions. I'm sure its something staring me in the face.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Suggested answer

Haque 3,384 on at

Like (1)

Report
Copy link

Link copied!
Hi @bdherh,

Root cuase:

When filtering an Oracle date/time column in Power Apps, the main challenge is that the Oracle DATE type includes both date and time components, so direct equality or simple date comparisons often fail because the time part differs.

Best practices to filter Oracle DATE/DATE-TIME columns in Power Apps:

Use a date range filter instead of equality: Instead of checking if the date equals a selected date, filter for records where the Oracle date/time is between the start of the selected day and the end of that day. For example:

Filter( 'YourOracleTable', BSC_START >= DateValue(YourDatePicker.SelectedDate) && BSC_START < DateAdd(DateValue(YourDatePicker.SelectedDate), 1, Days) )

If filtering by week or day of week : Since Oracle DATE includes time, consider filtering by a date range for the week or use a calculated column or view in Oracle that extracts the week or day of week, then filter on that.

Test your filter in Oracle SQL tools: Verify your date range query works as expected in Oracle SQL (e.g., using TO_DATE and BETWEEN), before coming to PowerApps.

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
bdherh 94 on at

Like (1)

Report
Copy link

Link copied!

Hi. Thank you for the response and sorry for the late reply. I just now got a chance to get back on this. I'm now getting the Network error that I started off with. The number of rows that will be returned from this is < 100 so I'm not sure why it is happening. The BSC_START column is defined in an Oracle table as DATE.

Was this reply helpful? Yes No
Suggested answer

Haque 3,384 on at

Like (0)

Report
Copy link

Link copied!

Hi @bdherh,

Can you please log out and reconnect the app? If it still persists - can you please do a simple filter without date field to make sure it's not networ issue?

One more clue to check - dows Oracle view supports delegable query/filter will be a concern.

Was this reply helpful? Yes No
Suggested answer

timl 37,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @bdherh

You're not going to like this, but the Oracle connector doesn't support filtering by date columns. That's why you get the 'error when trying to retrieve data from the network' error.

This is documented in the Microsoft connector documentation below

>> The following data types can't be used as query option predicates: Date

https://learn.microsoft.com/en-us/connectors/oracle/

If your table has fewer than 2000 rows, you can populate a collection and filter the values in the collection.

There are some workarounds in the post below, including a suggestion to retrieve the data via a Power Automate flow, filtered by the year, month, and day components of the date.

https://community.powerplatform.com/forums/thread/details/?threadid=c9789cfd-70bf-41b2-b69d-0df8bf5247a0

Was this reply helpful? Yes No
bdherh 94 on at

Like (0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
bdherh 94 on at

Like (0)

Report
Copy link

Link copied!

I got completely out of Edge and got in, and it cleared this up. But I'm having another issue. I have some dropdowns that when changed, I want to change the Filter on the gallery. I have an OnChange property set to do a Filter based on the dropdown change. When I click a new value, nothing happens in my gallery. So I added a label, and in the OnChange, I set the label text value to a string "Test". This is not even working. Why would an OnChange not work.

Here is the result after changing the Area to MEC. You can see that the text did not change to "Test"

Was this reply helpful? Yes No
timl 37,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @bdherh

It's not possible to make assignments in the OnChange property using the = operator (ie, Label1_17.Text = ""Test)

With On... properties, you would need to specify behaviour formulas. The online documentation here describe what a behaviour formula is.

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/working-with-formulas-in-depth

In your case, the pattern you would use is to set a variable in OnChange. The 'Test' version would look like this:

Set(varSelected, "Test")

In practice you would set it the selected item of your dropdown.

Set(varSelected, Dropdown1.Selected.Value)

If you then set the Text property of Label1_17 to varSelected, the label will display the selected dropdown item.

For the call to the Filter function in your gallery, you can either reference the variable varSelected or you can directly reference the dropdown control.

Was this reply helpful? Yes No
bdherh 94 on at

Like (0)

Report
Copy link

Link copied!

Ok, so the assignment was a mistake on my end. But the root of the problem is why is the Filter not doing anything?

When I change the Area dropdown value, it should fire the OnChange, correct? I have tried using the dropdowns in the filter as well as what I have below. The gallery data does not change at all, in fact it does not look like the filter is even getting executed since I dont have the waiting dots at the top

Was this reply helpful? Yes No
timl 37,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @bdherh

It looks like you added the call to Filter to the end of the 4 Set commands to OnChange?

If that's the case, that's the cause of the problem. You would set the Items property of your gallery to the Filter command that you specified.

The OnChange property would contain only those 4 calls to Set.

Was this reply helpful? Yes No