lookupTable not working as expected with a Date

[John Funk]

Hello everyone! I have an automation rule that uses a Lookup Table. I am wanting to get a value from the table when the value of a date field matches the key. 

I am logging out the value of the date field and it shows a value exactly like the value in the Lookup Table. But when I try to retrieve the value of the table to use in a Variable, I get an error. 

See the screenshots below. @Bill Sheboy 

[Bill Sheboy · Accepted solution]

Hi @John Funk 

Yes, and...to the suggestion from @Trudy Claspill --

When the date field is formatted with jiraDate (in order to use a text string for the key lookup), the value attribute is not needed:

{{fiscalWeek.get(triggerIssue.Go Live Date.jiraDate)}}

And, when you need the fiscal week, that could be done without a lookup table:

• increment your date by the offset amount for your fiscal year (e.g., plusMonths, plusWeeks, etc.)
• format the date using format("w") to get the numeric week
• concat the string after your "Week " prefix

Week {{triggerIssue.Go Live Date.plusMonths(2).format("w")}}

Please adjust as needed for your own fiscal amount shift.

Kind regards,
Bill

[John Funk]

Thanks Bill - removing the .value worked! Was just thinking that I needed that to get the value of the second column in the lookup table. 

The problem with the offset is that I am starting the first week of February, not January, so the numeric week returned from "w" wouldn't be right would it? 

[Bill Sheboy]

When using fiscal weeks not aligned to the calendar, an offset will effectively change the range of the year for any date / time functions (such as format): they are based on the adjusted value.  As you are only using the week, that will usually be correct.

Another approach is to get the week, convert to a number, and subtract into your fiscal year...remembering to account for differences in the number of weeks per year.

[Kai Becker · Accepted solution]

Hi @John Funk 

I guess the .value at the end of the lookup table get is not need. I assume it will try to return the value of the value which results in null

[John Funk]

Yep, that was it - thanks!

[Trudy Claspill · Accepted solution]

Hello @John Funk 

You appear to be missing a ")" between .value and }} in your Create Variable action.

[John Funk]

Good catch! I corrected that and no longer get an error, but the variable is not bringing back the value - it is blank (logging that out also).