Good day to you all. I have an app that I'm creating which will display an item that is due for inspection. They are uniquely identified by a itemSN and have a corresponding inspection interval (measured in days). If an item is due within 14 days, it will display in a gallery.
For this displaying the inventory item that is due for inspection I think the following would work:
filter(itemSN, (today + inspect_interval)
Am I somewhere in the ballpark here?
Yes, in the right ballpark but we need to get the syntax correct, more something like this, probably:
Filter(MyItemTable, XXX >= DateAdd(Today(), -14, Days))
Where XXX is the inspection due date of the item. You should tell me how you calculate XXX from the inspection interval in days, presumably you also need the date of last inspection. If both of these columns are present in your MyItemTable then XXX could be something like this:
DateAdd( LastInspectionDate, InspectionInterval, Days)
Maybe I am misunderstanding, but if you only have the inspection interval and no last inspection date, how can you calculate when a particular item is due for inspection?
I've decided to revisit this issue once again. I have made a table with the following columns:
1. SerialNumber (int)
2. ItemType (NVARCHAR)
3. Location (NVARCHAR)
4. InspectionInterval (numeric)
Ive tried this formula
Filter('[dbo].[ItemTest]', NextInspect >= DateAdd( LastInspect, InspectionInterval, Days)
As well as this formula
Filter('[dbo].[ItemTest]', InspectDue >= DateAdd(Today(), -14, Days))
Both formulas give me a service limitations error. Perhaps this specific filter is too resource intensive?
I think you have come across an issue that has not yet been resolved by the PowerApps team.
Date and datetime columns in SQL Server, Azure SQL DB and AWA are problematic for PowerApps.
One of the problems is that I think you can delegate a filter for MyDate = specific date but not when using inequalities, i.e. MyDate > a specific date.
This is why I have given up for the moment on date and datetime type columns and use integer columns instead.
Since I always only need dates (and not datetimes) in my work, then I use the integer yyyymmdd, so today would be 20170512. This allows me to do any delegated search also using inequalities and removes any ambiguity related to time zones (and therefore ending up with a day earlier/later). It is a little bit more work, but it works flawlessly.
There are a number of posts I have contributed to on all the above issues.
I will go back to using date type columns if and when PowerApps can deal with them properly.
I hope this helps.
Thank you for the response. I'm going to have to find a different route because I do need date and time in my data. I may try to use military date-time format (DDHHMM(Z)MONYY) as a substitute and have the column as an INT. It's rather unfortunate that there's so many issues using time in PowerApps. I'll let you know of my findings later today.
@BrendonBrooksP1, makes perfect sense.
I have not needed to do this, but I have read somewhere that it is best to separate the date and time columns if possible in order to:
1) Keep both the date and time columns of the INT type and not need BIGINT types (not sure how PowerApps deals with these, but I have seen issues in PowerApps related to significant figures)
2) Reduce the cardinality of the resulting table
Hope this helps
Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
Continue your learning in our online communities.
Features releasing from October 2019 through March 2020
Coming to a city near you
Fill out a quick form to claim your user group badge now!
Connect, share, and learn with your peers year-round
Register by September 5 to save $200