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:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (0)

WarrenBelz 153,079 Most Valuable Professional on at

Like (1)

Report
Hi Theandster33

Firstly can you please post your code in Text - saves retyping and potential errors here.

I cannot see your data, so the below is a bit of a guess on the structure. You cannot filter a Form however and I am not sure the logic is correct, but the structure would be something like this. I am also unsure which field you are referring to in the data to judge against the available date.
If( !IsBlank( LookUp( SPListName, 'OK Testing Machine Asset #'.Value = DataCardValue4.Selected.Value && ExpectedStartDateDatePicker.SelectedDate - 'Time Equipment is Occupied (Days)' <= 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

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (1)

Report

Just following up to see if you received the answer you needed, or if you require further assistance.

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

Was this reply helpful? Yes No
mmbr1606 14,605 Super User 2025 Season 2 on at

Like (1)

Report

hey

just as an add on to warrens reply, please make sure to use the code snippet thing the next time you post a topic, it makes it much more easier for us to help when we do not have to retype everything and can just copy paste it

thanks in advance ;)

Was this reply helpful? Yes No
Theandster33 45 on at

Like (1)

Report

Sorry did not see this until today, I will try that code and report back this week. So basically use a "lookup" for the sharepoint list instead of a "filter". Also here is the code, sorry.

If(

CountRows(

Filter(

'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

)

It is on the "OnChange Property" of 'OFC Testing Machine Assest #' data field. This is what the form looks like more or less.

Was this reply helpful? Yes No
Theandster33 45 on at

Like (1)

Report

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

)

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (1)

Report
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

Was this reply helpful? Yes No
Theandster33 45 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (1)

Report
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 ?

Was this reply helpful? Yes No
Theandster33 45 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (1)

Report

OK what about the date in the collection in the Occupied field ?

Was this reply helpful? Yes No