Checking to see if a record has been completed this month already

[BassettN]

I have a gallery of employees on the Start Screen of my app, and I need the fill to show either green or red, depending on if a record has been entered with their name on it already for that current month.  This is what I have tried thus far and have not been able to get to work (the solid line is used to denote the end of one failed formula and the start of a new formula, as I have tried multiple things):

If(
     CountRows(
          Filter(
              'SharePointListName',
              ThisItem.displayName = 'Name',
              Month(DataCardValue4.SelectedDate) = Month(Now())
           ) > 0
       ),
       RGBA(111,255,0,.35),
       RGBA(255,0,0,.35)
)

________________________________________

With(
       {
            wList:
                 LookUp(
                     'SharePointListName',
                     ThisItem.displayName = 'Name'
                )
         },
         If(
              !IsBlank(
                   LookUp(
                        wList,
                        Month(DataCardValue4.SelectedDate) = Month(Now())
                    )
                ),
                RGBA(255,0,0,.35),
                RGBA(111,255,0,.35)
          )
)

The first solution was something I tried to do on my own, but that didn't work.  As a note, there is a date column 'Completed Date', but when I tried to enter that into the Month() function, it said it was invalid.

The second was a solution that I had found via Google, but I could not get it to work for me.  I wasn't sure what wList was - my guess was that was creating a variable/name for the list being created with the With function (first argument, identifying the scope).

Is what I'm asking even possible?

P.S. - Please forgive my formula formatting - I did the best I could.

[Giraldoj]

Hi @BassettN 

You need to make some small changes, your code should include both month and year comparisons to ensure that records are correctly identified within the current month and year, which resolves issues with date filtering

If(
    CountRows(
        Filter(
            'SharePointListName',
            ThisItem.displayName = 'Name',
            Month('Completed Date') = Month(Now()) && // Changed from DataCardValue4.SelectedDate
            Year('Completed Date') = Year(Now())     // Added Year comparison
        )
    ) > 0,
    RGBA(111,255,0,.35), // Green color if record exists
    RGBA(255,0,0,.35)   // Red color if no record exists
)

If my response resolved your issue, please feel free to mark it as the solution by clicking accept it as a solution. 

If you liked my solution, please give it a thumbs up. 
This helps others find the answer more easily.

Connect with me: LinkedIn 

[BassettN]

I'm getting a delegation warning, and it's not filtering like it's supposed to unfortunately.  Of the 8 names I'm looking at right now, they are all still red, but 1 of them should be green.

"Delegation warning.  The "Month" part of this formula might not work correctly on large data sets"

[Giraldoj]

Hi @BassettN 

you are going to face delegation warning if you keep using Month function, to avoid delegation warning you should load all your sharepoint data into a collection first, and then you can apply any filter you want.

In the onvisible property of your app you should collect the data:

ClearCollect(colEmployeeRecords, 'SharePointListName');

then you need to set your gallery items to:

colEmployeeRecords

and finally your corrected code should be something like:

If(
    CountRows(
        Filter(
            colEmployeeRecords,
            ThisItem.displayName = 'Name' &&
            Month('Completed Date') = Month(Now()) &&
            Year('Completed Date') = Year(Now())
        )
    ) > 0,
    RGBA(111,255,0,.35), // Green color if record exists
    RGBA(255,0,0,.35)    // Red color if no record exists
)

please give it a shot and let me know if you have any other question.

If my response resolved your issue, please feel free to mark it as the solution by clicking accept it as a solution. 

If you liked my solution, please give it a thumbs up. 
This helps others find the answer more easily.

Connect with me: LinkedIn 

[WarrenBelz · Accepted solution]

Hi @BassettN ,

Firstly you do not need a Collection as the With() function effectively provides this as a Table Variable that is then executed "locally" (and hence not being subject to Delegation). Your issue is that you are referring to a control DataCardValue4 rather than the field in the filtered table 'Completed Date' - so try this

With(
   {
      wList:
      Filter(
         'SharePointListName',
         'Name' = ThisItem.displayName
      )
   },
   If(
      !IsBlank(
         LookUp(
            wList,
            Month('Completed Date') = Month(Now())
         ).'Completed Date'
      ),
      RGBA(255,0,0,.35),
      RGBA(111,255,0,.35)
   )
)

Please click Accept as solution 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 Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[BassettN]

This worked!  Thank you so much!

@Giraldoj, I also appreciate your help in helping me troubleshoot this!