Generate a Specific Number of Rows from a Single Added Row

Jacob Minnich

A little background here: I use Smartsheet primarily to track lab testing and release on each lot of our products. I have almost everything configured so all of the work is done automatically, but there's one piece I can't figure out.

When the lab sends me an export file of all of the results, sometimes the values that are returned are for several lots of product (tested as a "composite"). For example, if we have a sample tested at the lab as "X0111-X0119 COMPOSITE", those results will apply to all of the lots X0111 through X0119. My main sheet uses DataMesh to pull the results from the lab data and put it in the right row so I can generate a Certificate of Analysis for the customer (matching primary column product and lot numbers between the two sheets). What I would like to do is for Smartsheet to recognize when I have one of these "composite" rows and trigger the creation of x number of rows with duplicate results given the total number contained in the "composite" row.

I've tried to do this a number of ways with automation, but it stops after a few iterations and gives me an error message, since the automations are triggering each other in what Smartsheet thinks is an infinite loop.

Anybody have any ideas?

Genevieve P.

Hi @Jacob Minnich

There currently isn't a way that I can think of which would allow Smartsheet to recognize that a text string with a - between two numbers indicates multiple rows need to be generated, then generate those rows.

We could possibly use a formula to identify the number of rows required (e.g using LEFT and MID to grab the number and subtract one from the other), such as returning "8" from your example above.

However in order to use DataMesh I would assume that you're using the "X" value as the unique value to match. This means you'd need each of those numbers actually spelled out in individual rows in your source sheet, changing "X0111-X0119 COMPOSITE" to ""X0111 COMPOSITE" , "X0112 COMPOSITE", "X0113 COMPOSITE"...etc. At this time I believe this would need to be a manual update to your source sheet, although I'd love to be proven wrong by one of our incredible Community members!

Cheers,

Genevieve