Skip to main content

Concatenate Error

  • April 1, 2021
  • 3 replies
  • 28 views
G
Forum|alt.badge.img+3

Hello,
Airtable newbie here. I have been trying to work out this formula for hours now and I can’t figure out what is wrong. I want to create a custom SKU based on when it was set up. This is my formula that is erroring:

“EY” &
RIGHT(
{LIST DATE}, 1) &
ROUNDUP(
MONTH({LIST DATE}) / 3, 0) &
”-“ & {SKU IDENTIFIER}

The SKU should start with ‘EY’ and then a two digit number where the first digit is the year (1 for 2021, 2 for 2022, etc.) and the second digit is the quarter.

I have plugged each subsection in separately and each work by themselves but when I string them with & it errors out. I have tried both Concatenate and &. I’m sure it’s something very simple I"m doing wrong but I’ve read the documentation many times over and can’t figure it out.

3 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • April 1, 2021

Welcome to the Airtable community!

The formula looks like it should concatenate okay.

You may want to check to make sure that you are using straight quotes instead of curly quotes, "EY" versus “EY” and "-" versus “-”.

"EY" &
RIGHT(
{LIST DATE}, 1) &
ROUNDUP(
MONTH({LIST DATE}) / 3, 0) &
"-" & {SKU IDENTIFIER}

I am also a bit surprised that RIGHT({LIST DATE}, 1) gives you the last digit of the year. You can also get the quarter more directly using DATETIME_FORMAT. Here is one alternative to your fomula.

"EY" &
RIGHT( YEAR({LIST DATE}) & "", 1) &
DATETIME_FORMAT({LIST DATE}, "Q") &
"-" & 
{SKU IDENTIFIER}

Let us know what works for you!

G
Forum|alt.badge.img+3
  • Gregory_Wu
  • Author
  • New Participant
  • April 2, 2021

Thank you for your suggestions! I didn’t know what the documentation meant by straight and curly quotes. I thought they meant “EY” vs ‘EY’. But then I remembered my old college coding days and needing to use Courier font if I was coding in Word and pasting it in.

I was playing with DATETIME_FORMAT and you were correct, my previous formula was returning the letter Z. But I wasn’t getting ‘YYYY’ to work either. Your way is much cleaner so I’ll give that a go. Thank you so much for your guidance.

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • April 2, 2021

Thank you for your suggestions! I didn’t know what the documentation meant by straight and curly quotes. I thought they meant “EY” vs ‘EY’. But then I remembered my old college coding days and needing to use Courier font if I was coding in Word and pasting it in.

I was playing with DATETIME_FORMAT and you were correct, my previous formula was returning the letter Z. But I wasn’t getting ‘YYYY’ to work either. Your way is much cleaner so I’ll give that a go. Thank you so much for your guidance.


On further thought, here is an even simpler formula:

"EY" &
RIGHT(DATETIME_FORMAT({LIST DATE}, "YYQ"), 2) &
"-" & 
{SKU IDENTIFIER}

Is there any chance that you will be using this system for more than 9 years? If you end up using it for 10 or more years, your numbers will repeat.

Terms & ConditionsAccessibility statement