Skip to main content

Hi community! I’m trying to build a formula that generates a Pre-Processing Lot ID for each seed movement record. The logic depends on whether the movement is linked to a contract or not.

1. When the seed movement is linked to a contract

The Lot ID should concatenate:

  • {Final Initials (from Farmers)}
  • {Program (from Contracts)}
  • {Seed Variety (from Contracts)}
  • The last two digits of {Crop Year (from Contracts)}
  • {Contracts} (the contract number)
  • The final Bin code (from {To Bin}) following these rules:
    1. If the seed is moved to one of these bins — HK 01, HK 02, HK 03, HK 48, HK 49 — the lot number should display the full Bin code (alphanumeric).
    2. Any “Bin ” text prefix should be removed.
    3. If the seed is moved to HPB1, HPB2, or GP WHPB, or the movement goes to a Cleaner (detected in {To Location}), the lot should take the Bin code from {From Bin} instead.
    4. Otherwise, it uses the Bin code from {To Bin}.

Example (desired output):
MCO-OH-FINOLA-25-070-02

2. When there is no contract

The Lot ID should concatenate:

  • {Final Initials (from Farmers)}
  • {Program}
  • {Seed Variety} (direct link to Seed Varieties)
  • The last two digits of {Crop Year}
  • The final Bin code (from {To Bin}), following the same rules:
    1. Full Bin code for HK 01, HK 02, HK 03, HK 48, HK 49
    2. Remove “Bin ” prefix
    3. Use {From Bin} if To Bin is HPB1, HPB2, GP WHPB, or a Cleaner
    4. Otherwise, use {To Bin}

Example output:
RVSF-CH-FINOLA-25-07

Hey ​@LizRVSF,

For this uses cases you can always try leveragin ChatGPT :D.
I did not give it a shot yet manually on my side (I promise I’ll try to get back to this). However, you might want to try this formula out in the meantime:
 

IF(
  {Contracts},
  {Final Initials (from Farmers)} & "-" &
  {Program (from Contracts)} & "-" &
  {Seed Variety (from Contracts)} & "-" &
  RIGHT({Crop Year (from Contracts)}, 2) & "-" &
  {Contracts} & "-" &
  SUBSTITUTE(
    IF(
      OR(
        {To Bin} = "HK 01",
        {To Bin} = "HK 02",
        {To Bin} = "HK 03",
        {To Bin} = "HK 48",
        {To Bin} = "HK 49"
      ),
      {To Bin},
      IF(
        OR(
          {To Bin} = "HPB1",
          {To Bin} = "HPB2",
          {To Bin} = "GP WHPB",
          FIND("Cleaner", {To Location})
        ),
        {From Bin},
        {To Bin}
      )
    ),
    "Bin ",
    ""
  ),
  {Final Initials (from Farmers)} & "-" &
  {Program} & "-" &
  {Seed Variety} & "-" &
  RIGHT({Crop Year}, 2) & "-" &
  SUBSTITUTE(
    IF(
      OR(
        {To Bin} = "HK 01",
        {To Bin} = "HK 02",
        {To Bin} = "HK 03",
        {To Bin} = "HK 48",
        {To Bin} = "HK 49"
      ),
      {To Bin},
      IF(
        OR(
          {To Bin} = "HPB1",
          {To Bin} = "HPB2",
          {To Bin} = "GP WHPB",
          FIND("Cleaner", {To Location})
        ),
        {From Bin},
        {To Bin}
      )
    ),
    "Bin ",
    ""
  )
)

 
I took a quick look at it and it seems to make sense!

Completely different matter, but would love to have you join our Airtable Hackathon! Make sure to sign up!!

Mike, Consultant @ Automatic Nation 
YouTube Channel

Thank you so much Mike. I’ve been using ChatGPT and still not working 🤣

Mike you are a rock star! 😎 got an error with your formula, then I ask ChatGPT to improve it. It fixed it and now it’s working. What changed:

RIGHT("" & {Crop Year...}, 2) is used to avoid an error when the year is stored as a number.

I replaced FIND("Cleaner", {To Location}) with REGEX_MATCH(LOWER({To Location} & ""), "cleaner") — this prevents an error if {To Location} is empty and also makes the match case-insensitive.

I kept SUBSTITUTE(..., "Bin ", "") to remove the "Bin " prefix when it exists.

Here is the final version:

IF(
  {Contracts},
  {Final Initials (from Farmers)} & "-" &
  {Program (from Contracts)} & "-" &
  {Seed Variety (from Contracts)} & "-" &
  RIGHT("" & {Crop Year (from Contracts)}, 2) & "-" &
  {Contracts} & "-" &
  SUBSTITUTE(
    IF(
      OR(
        {To Bin} = "HK 01",
        {To Bin} = "HK 02",
        {To Bin} = "HK 03",
        {To Bin} = "HK 48",
        {To Bin} = "HK 49"
      ),
      {To Bin},
      IF(
        OR(
          {To Bin} = "HPB1",
          {To Bin} = "HPB2",
          {To Bin} = "GP WHPB",
          REGEX_MATCH(LOWER({To Location} & ""), "cleaner")
        ),
        {From Bin},
        {To Bin}
      )
    ),
    "Bin ",
    ""
  ),
  {Final Initials (from Farmers)} & "-" &
  {Program} & "-" &
  {Seed Variety} & "-" &
  RIGHT("" & {Crop Year}, 2) & "-" &
  SUBSTITUTE(
    IF(
      OR(
        {To Bin} = "HK 01",
        {To Bin} = "HK 02",
        {To Bin} = "HK 03",
        {To Bin} = "HK 48",
        {To Bin} = "HK 49"
      ),
      {To Bin},
      IF(
        OR(
          {To Bin} = "HPB1",
          {To Bin} = "HPB2",
          {To Bin} = "GP WHPB",
          REGEX_MATCH(LOWER({To Location} & ""), "cleaner")
        ),
        {From Bin},
        {To Bin}
      )
    ),
    "Bin ",
    ""
  )
)
 

Terms & ConditionsAccessibility statement