Skip to main content
Question

Struggling with a formula

  • October 31, 2025
  • 3 replies
  • 39 views
L
Forum|alt.badge.img+2

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

    3 replies

    Mike_AutomaticN
    Forum|alt.badge.img+28

    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

    L
    L
    Forum|alt.badge.img+2
    • LizRVSF
    • Author
    • New Participant
    • October 31, 2025

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

    L
    Forum|alt.badge.img+2
    • LizRVSF
    • Author
    • New Participant
    • October 31, 2025

    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