Struggling with Lookup against date fields in Sharepoint

(0) Share

Report

Posted on by crispybits1977

195

Hi all

Hitting my head against a brick wall with this and I don't understand why.... it seems like it should be really simple...

I'm trying to set a variable for a Payroll Month name (which will come out like "December 2023" for example) by looking up today's date vs start and end dates in a sharepoint list. The code to set the variable is:

Set(TSCurrMonth,LookUp(HRPayrollMonths,StartDate<=Today() && EndDate>=Today()).Title)

The payroll months don't line up with the calendar months which is why I can't just use normal date functions. There is an item in the "HRPayrollMonths" list that would qualify for today:

However there are no red error bars and the variable comes out as empty. What am I missing?

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (3)

Sort by

Verified answer

Sickle-Cell 600 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @crispybits1977,

Try this instead.

Set(TSCurrMonth,LookUp(HRPayrollMonths,StartDate<=Today() && EndDate>=Today(),Title))

Was this reply helpful? Yes No
crispybits1977 195 on at

Like
a
(0)

Report
Copy link

Link copied!

Still nothing I'm afraid - it's returning text just the text is blank... (I did have it returning table or record for some of my other tries at different variations on the theme...)

Was this reply helpful? Yes No
Sickle-Cell 600 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @crispybits1977,

I'm not seeing any obvious reasons for the error, would you mind doing a few things to help me narrow this down?

Can you highlight part of the Loopkup code until you get the option to view the information it's collecting and see if that's blank? If that doesn't work (it can be fiddly in my experience) could you create a new text label and set the text code to
LookUp(HRPayrollMonths,StartDate<=Today() && EndDate>=Today(),Title)
and confirm what it displays?

Was this reply helpful? Yes No
crispybits1977 195 on at

Like
a
(0)

Report
Copy link

Link copied!

OK so this is just getting weird.

If I set the variable and have the variable display in a text label, it's blank with data type text.

If I set the label directly with the formula it displays correctly.

Was this reply helpful? Yes No
Sickle-Cell 600 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @crispybits1977,

Yeah, that's very odd. Let's try changing the syntax and see if that sorts it. Try the below.

Set( TSCurrMonth, LookUp( HRPayrollMonths, And( StartDate<=Today(), EndDate>=Today() ), Title ) )

Was this reply helpful? Yes No
crispybits1977 195 on at

Like
a
(0)

Report
Copy link

Link copied!

A bit more info - I noticed the "format text" option was greyed out signalling an error somewhere in the code (this is all running in my "OnStart" code) so I cut the two lines out and pasted them back in - suddenly I have red lines and intellisense text on the error...

Was this reply helpful? Yes No
Verified answer

Sickle-Cell 600 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @crispybits1977,

Okay, one issue I've had in the past which may be related to this. Sharepoint has a habit of renaming fields. If you try to rewrite the Lookup function, does it auto-complete StartDate as a field? If not, try looking for fields called field_1 or field_2, that kind of thing, as Sharepoint may not be pulling through the correct name.

Was this reply helpful? Yes No
crispybits1977 195 on at

Like
a
(0)

Report
Copy link

Link copied!

Same thing is happening with the latest batch of new code btw - only the text box with the variable in is now throwing an explicit error of 'expects data type "text", being given "error"'

Was this reply helpful? Yes No
crispybits1977 195 on at

Like
a
(0)

Report
Copy link

Link copied!

It's not even popping up the StartDate field in the auto-complete options when I clear it to retype

Was this reply helpful? Yes No
crispybits1977 195 on at

Like
a
(0)

Report
Copy link

Link copied!

Retyped it all from scratch and it gave me the autocompletes, but I'm back to where I started now with the variable displaying blank text.

Was this reply helpful? Yes No