cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Identifying an Item Due for Inspection

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?

13 REPLIES 13
Meneghino
Level 10

Re: Identifying an Item Due for Inspection

Hi @BrendonBrooksP1

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)

Re: Identifying an Item Due for Inspection

Meneghino,

 

In the table, we have items that have different inspection variables (90, 180, 360) but no last inspection date.

Meneghino
Level 10

Re: Identifying an Item Due for Inspection

Hi @BrendonBrooksP1

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?

Thanks.

Re: Identifying an Item Due for Inspection

Indeed. Unfortunately that data is on another table.  At this point, I will have to do more backend work than I originally anticipated. Thanks for your help.

Meneghino
Level 10

Re: Identifying an Item Due for Inspection

Hi @BrendonBrooksP1

With PowerApps it is very easy to combine the data from different tables.

Do re-initiate once you are ready.

Re: Identifying an Item Due for Inspection

@Meneghino 

 

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)

5. FirstInspect(DateTime)

6. Lastinspect(DateTime)

7. NextInspect(DateTime)

8. Timestamp

 

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?

 

 

Meneghino
Level 10

Re: Identifying an Item Due for Inspection

Hi @BrendonBrooksP1

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.

Re: Identifying an Item Due for Inspection

@Meneghino

 

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.

 

Brendon

Meneghino
Level 10

Re: Identifying an Item Due for Inspection

@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

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 272 members 4,242 guests
Please welcome our newest community members: