I need some help with an issue I'm facing. I have a formula field called "Order Quantities" and a "Meal Quantities" field (Long Text) that references it. I'm using the Fillout form to gather customer information. However, the quantity field in Fillout is not connected to my Airtable.
To address this, I created an automation to pull the quantity details into Airtable (see the attached file).
I'm looking for a workaround to sum all the quantities of each meal for all records in the "Order Quantities" field without changing the structure of my table, as I need to launch this system this week.
CONCATENATE(
"Lamb Keema: ", IF(
ISERROR(SEARCH("Lamb Keema x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Lamb Keema x ", {Meal Quantities}) + LEN("Lamb Keema x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Lamb Keema x ", {Meal Quantities}) + LEN("Lamb Keema x ")) - SEARCH("Lamb Keema x ", {Meal Quantities}) - LEN("Lamb Keema x ")
))
), "\n",
"Miso Chicken Noodle Broth: ", IF(
ISERROR(SEARCH("Miso Chicken Noodle Broth x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Miso Chicken Noodle Broth x ", {Meal Quantities}) + LEN("Miso Chicken Noodle Broth x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Miso Chicken Noodle Broth x ", {Meal Quantities}) + LEN("Miso Chicken Noodle Broth x ")) - SEARCH("Miso Chicken Noodle Broth x ", {Meal Quantities}) - LEN("Miso Chicken Noodle Broth x ")
))
), "\n",
"Pork Bolognaise, Linguine, Kale: ", IF(
ISERROR(SEARCH("Pork Bolognaise, Linguine, Kale x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Pork Bolognaise, Linguine, Kale x ", {Meal Quantities}) + LEN("Pork Bolognaise, Linguine, Kale x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Pork Bolognaise, Linguine, Kale x ", {Meal Quantities}) + LEN("Pork Bolognaise, Linguine, Kale x ")) - SEARCH("Pork Bolognaise, Linguine, Kale x ", {Meal Quantities}) - LEN("Pork Bolognaise, Linguine, Kale x ")
))
), "\n",
"Salmon & Ratatouille: ", IF(
ISERROR(SEARCH("Salmon & Ratatouille x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Salmon & Ratatouille x ", {Meal Quantities}) + LEN("Salmon & Ratatouille x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Salmon & Ratatouille x ", {Meal Quantities}) + LEN("Salmon & Ratatouille x ")) - SEARCH("Salmon & Ratatouille x ", {Meal Quantities}) - LEN("Salmon & Ratatouille x ")
))
), "\n",
"Squash Mac N cheese with Beef Ragu: ", IF(
ISERROR(SEARCH("Squash Mac N cheese with Beef Ragu x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Squash Mac N cheese with Beef Ragu x ", {Meal Quantities}) + LEN("Squash Mac N cheese with Beef Ragu x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Squash Mac N cheese with Beef Ragu x ", {Meal Quantities}) + LEN("Squash Mac N cheese with Beef Ragu x ")) - SEARCH("Squash Mac N cheese with Beef Ragu x ", {Meal Quantities}) - LEN("Squash Mac N cheese with Beef Ragu x ")
))
), "\n",
"Lentil Bolognaise, Linguine & Kale: ", IF(
ISERROR(SEARCH("Lentil Bolognaise, Linguine & Kale x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Lentil Bolognaise, Linguine & Kale x ", {Meal Quantities}) + LEN("Lentil Bolognaise, Linguine & Kale x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Lentil Bolognaise, Linguine & Kale x ", {Meal Quantities}) + LEN("Lentil Bolognaise, Linguine & Kale x ")) - SEARCH("Lentil Bolognaise, Linguine & Kale x ", {Meal Quantities}) - LEN("Lentil Bolognaise, Linguine & Kale x ")
))
), "\n",
"Ratatouille: ", IF(
ISERROR(SEARCH("Ratatouille x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Ratatouille x ", {Meal Quantities}) + LEN("Ratatouille x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Ratatouille x ", {Meal Quantities}) + LEN("Ratatouille x ")) - SEARCH("Ratatouille x ", {Meal Quantities}) - LEN("Ratatouille x ")
))
), "\n",
"Squash Mac and cheese with Mushroom Ragu: ", IF(
ISERROR(SEARCH("Squash Mac and cheese with Mushroom Ragu x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Squash Mac and cheese with Mushroom Ragu x ", {Meal Quantities}) + LEN("Squash Mac and cheese with Mushroom Ragu x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Squash Mac and cheese with Mushroom Ragu x ", {Meal Quantities}) + LEN("Squash Mac and cheese with Mushroom Ragu x ")) - SEARCH("Squash Mac and cheese with Mushroom Ragu x ", {Meal Quantities}) - LEN("Squash Mac and cheese with Mushroom Ragu x ")
))
), "\n",
"Sweet Potato & Chickpea Keema: ", IF(
ISERROR(SEARCH("Sweet Potato & Chickpea Keema x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Sweet Potato & Chickpea Keema x ", {Meal Quantities}) + LEN("Sweet Potato & Chickpea Keema x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Sweet Potato & Chickpea Keema x ", {Meal Quantities}) + LEN("Sweet Potato & Chickpea Keema x ")) - SEARCH("Sweet Potato & Chickpea Keema x ", {Meal Quantities}) - LEN("Sweet Potato & Chickpea Keema x ")
))
), "\n",
"Tofu Noodle Broth: ", IF(
ISERROR(SEARCH("Tofu Noodle Broth x ", {Meal Quantities})), 0,
VALUE(MID(
{Meal Quantities},
SEARCH("Tofu Noodle Broth x ", {Meal Quantities}) + LEN("Tofu Noodle Broth x "),
FIND("\n", {Meal Quantities} & "\n", SEARCH("Tofu Noodle Broth x ", {Meal Quantities}) + LEN("Tofu Noodle Broth x ")) - SEARCH("Tofu Noodle Broth x ", {Meal Quantities}) - LEN("Tofu Noodle Broth x ")
))
)
)
