Struggling with Lookup against date fields in Sharepoint

[crispybits1977]

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?

[Sickle-Cell · Accepted solution]

Hi @crispybits1977,

Try this instead.

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

[crispybits1977]

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...)

[Sickle-Cell]

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? 

[crispybits1977]

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.

[Sickle-Cell]

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
     )
)

[crispybits1977]

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...

[Sickle-Cell · Accepted solution]

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.

[crispybits1977]

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"'

[crispybits1977]

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