Unanswered

Filter Sharepoint List

(1) Share

Report

Posted on by Theandster33

Hello,

I am attempting to add code to a dropdown control data field to basically say "Hey, if this machine asset # is already being used then notify the user and have them pick a different machine" I am doing this by taking the expected start day and adding the time the machine is going to be occupied from the Sharepoint list, then comparing it to the present date. If it is greater than the present date, the user can select the machine, if not notify them and not allow them to select it. Is there a good way to do this? This is what I have so far, the code is in the "OnChange" portion of "OFC Testing Machine Asset #" data field.

Thanks,

Categories:

Using Apps Using Formulas

All responses (14)

Answers (0)

WarrenBelz 146,631 Most Valuable Professional on at

Like (1)

Report
Filter Sharepoint List

Hi Theandster33

The easiest way to solve this is to put a Date column into your SharePoint list (I will call it EndDate here) with the DefaultDate

DateAdd( 'Expected Start Date', 'Time Equipment is Occupied (Days)', TimeUnit.Days )

which means every time you change either of those fields and save, it will also update. You will need to back-populate anything still current. Then your query is much simpler
If( !IsBlank( LookUp( 'GHQ and OFC Dashboard', 'OFC Testing Machine Asset #'.Value = DataCardValue4.Selected.Value && EndDate >= Now() ) ), Notify( "This machine is currently in use. Please choose another.", NotificationType.Error ), Reset(DataCardValue4) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
Theandster33 45 on at

Like (1)

Report

Filter Sharepoint List

The expected date + time equipment is occupied (both inputs the user will put in) = the temporary column "occupied". There is no sharepoint for occupied. The math works out as I confirmed with your "ClearCollect" code, "occupied" is calculated correctly.

The original problem still exists of the error code popping up soley by checking if the machine is in the sharepoint list. See image below, I put the machine number in along with the expected start date and time equipment is occupied, this date and time is outside the time frame of when this machine will be used. October 7th is the start date, 60 days of time equipment is being used makes it done by December 7th. I put I am using it on January 8th, the error message should not pop up and allow me to choose this asset #.
WarrenBelz 146,631 Most Valuable Professional on at

Like (1)

Report
Filter Sharepoint List

So are you saying that the expected date is in Occupied and the filter

Occupied >= Today()

Is not working ?
Theandster33 45 on at

Like (1)

Report

Filter Sharepoint List

Yes, I put it in a temp collections and the expected start date and estimated time is adding correctly. So 10/24/2024 as the "expected start date" and 5 as the "time equipment is occupied (days) yields 10/29/2024.
WarrenBelz 146,631 Most Valuable Professional on at

Like (1)

Report

Filter Sharepoint List

OK what about the date in the collection in the Occupied field ?
Theandster33 45 on at

Like (1)

Report

Filter Sharepoint List

I have put the value conversion in.

I tried the second code in a text label and it returns true or false correctly but only in regards to if the machine is in the sharepoint list or not. Instead of taking into consideration the start date to current date comparison logic as well.

I am not sure where to put that third code that you have to test. Did you want me to put it in the text label to test? If so I got this error.
WarrenBelz 146,631 Most Valuable Professional on at

Like (1)

Report
Filter Sharepoint List

You are missing the value conversion of the Choice field I believe
Occupied, DateAdd( 'Expected Start Date', Value(‘Time Equipment is Occupied (Days)'.Value), TimeUnit.Days )

Also for some debugging, if you put this on a Text Label, does it return the expected true or false result ?
!IsBlank( LookUp( AddColumns( Sort( 'GHQ and OFC Dashboard', ID, SortOrder.Descending ), Occupied, DateAdd( 'Expected Start Date', Value('Time Equipment is Occupied (Days)'.Value), TimeUnit.Days ) ), 'OFC Testing Machine Asset #'.Value = DataCardValue4.Selected.Value && Occupied >= Today() ) )

Also if you did this
ClearCollect( colTest, Filter( AddColumns( Sort( 'GHQ and OFC Dashboard', ID, SortOrder.Descending ), Occupied, DateAdd( 'Expected Start Date', Value('Time Equipment is Occupied (Days)'.Value), TimeUnit.Days ) ), 'OFC Testing Machine Asset #'.Value = DataCardValue4.Selected.Value ) )

are the values in the Occupied field what you expect to see ?
Theandster33 45 on at

Like (1)

Report

Filter Sharepoint List

If(
!IsBlank(
LookUp(
AddColumns(
Sort(
'GHQ and OFC Dashboard',
ID,
SortOrder.Descending
),
Occupied,
DateAdd(
'Expected Start Date',
'Time Equipment is Occupied (Days)'.Value,
TimeUnit.Days
)
),
'OFC Testing Machine Asset #'.Value = DataCardValue4.Selected.Value &&
Occupied >= Now()
)
),
Notify(
"This machine is currently in use or will be in use. Please choose another machine or date to start test.",
NotificationType.Error
);
Timer.Start // Starts the timer, which triggers the reset after the delay
)

Thank you so much for the help so far, it has been a lot easier troubleshooting with a more structured code.
Made a couple of tweaks but it does not reset based off of the expected start date + time equipment is occupied value. It just looks in the sharepoint list, and if that machine number is somewhere in that list it will give the error message and reset the datacard. I am trying to figure out how to strictly bring the error message up/reset the datacard only when that machine is in the list AND the sum between expected start time and time equipment is occupied is less than the current date. and yes OFC Testing Machine Asset #' and 'Time Equipment is Occupied (Days)' are both Choice fields.
WarrenBelz 146,631 Most Valuable Professional on at

Like (1)

Report
Filter Sharepoint List

Hi Theandster33

There are still some assumptions I have made here: -

'OFC Testing Machine Asset #' and 'Time Equipment is Occupied (Days)' are both Choice fields. If the last one is a number, you do not need either of the Value references

You realise that none of this is Delegable, so your data size may affect the result here. I have sorted with newest first, which may resolve this issue if it exists.

Also you can use CountRows( . . . ) > 0 OR !IsBlank(LookUp( , , , )) - not pieces of both as you have with !IsBlank(LookUp( , , , )) > 0. With all of that noted, try this
If( !IsBlank( LookUp( AddColumns( Sort( 'GHQ and OFC Dashboard', ID, SortOrder.Descending ), Occupied, DateAdd( 'Expected Start Date', Value('Time Equipment is Occupied (Days)'.Value), TimeUnit.Days ) ), 'OFC Testing Machine Asset #'.Value = DataCardValue4.Selected.Value && Occupied >= Now() ) ), Notify( "This machine is currently in use. Please choose another.", NotificationType.Error ), Reset(DataCardValue4) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
Theandster33 45 on at

Like (1)

Report

Filter Sharepoint List

This is what I have tried but it gives me this error. The goal is when a user chooses an asset #, the form would check the SharePoint list to see if that asset # is in the list already.

If it is, add the "expected start date" and "time equipment is occupied" columns to see if its more or less than the present date.

If it is less than the present date (means it is being used) then error message and tell the user to pick a different one.

If(

!IsBlank(

LookUp(

'GHQ and OFC Dashboard',

ThisItem.'OFC Testing Machine Asset #'.Value = DataCardValue4.Selected.Value &&

DateAdd(ThisItem.'Expected Start Date', Value(ThisItem.'Time Equipment is Occupied (Days)'.Value), TimeUnit.Days) >= Now()

)

) > 0,

Notify("This machine is currently in use. Please choose another.", NotificationType.Error),

Reset(DataCardValue4) // Reset the dropdown if the machine is in use

)