Reduction of Column Count from dynamic form

L_123

Has anyone found a way to reduce the column count from a long dynamic form? I was thinking of using conditional logic on a separate form with a copy workflow to move data, but I'm not confident it would be reliable. I'm running some tests now, but if anyone already has a solution I would be very happy to hear about it.

Paul Newcome

I would think that even the copy row won't work because it automatically inserts the missing columns into the destination sheet.

L_123

I've found a partial solution, still testing.

Start with 10 columns

https://app.smartsheet.com/b/publish?EQBCT=55900be4b9b045688f4e13d645960ce0

Reduce to 5 column using conditional index formula

https://app.smartsheet.com/b/publish?EQBCT=de24e48fedf04354996ff358a7fe4c7a

Archive to remove formulas

https://app.smartsheet.com/b/publish?EQBCT=cf4a10bb7ffa47ad9cc87515000d8701

I am still testing though, I'm worried the program won't catch up if the form is submitted in succession, so i'm thinking of adding a unique index to the equation, checking the archive for matching ones, then pulling potentially multiple results from the 10 columns sheet into the reduce to 5 sheet that can then be copied over to be archived

Paul Newcome

It looks like your setup is under the assumption that columns 1 and 2 are grouped together, 3 and 4, 5 and 6, 7 and 8, and 9 and 10 in that if column 1 is blank then column 2 will be filled and if column 1 is filled then column 2 is blank kind of thing. What if columns 1 and 2 are both filled? Then you'd only be pulling column 1 and column 2's data would be lost.

L_123

Yeah, I have conditional logic in a form that forces them to only fill out one or the other for the final sheet, and it is about 65 columns I am trying to reduce to about 10. Depending on the options they choose they will follow a path down the form, and I will set up the formulas to pick the chain they went down. I'm less worried about the formulas, and more worried about loss of data from the workflow kicking in at the wrong time when the formulas haven't updated yet.

Paul Newcome

Ah. Ok. As long as that logic is built in, then it looks like you have yourself a solution.

Those INDEX formulas are some pretty slick thinking by the way.

I'm not sure I follow this though:

"I'm worried the program won't catch up if the form is submitted in succession"

Do you mean if for example I submit the form twice in a row, or...?

L_123

Yeah that, and smartsheet tends to save resources by not updating sheets that aren't opened. The conversion sheet is a middleman that shouldn't ever be opened, so if the send catches when it hasn't updated, or to many submissions are sent in at once, then i'm worried it won't keep up. It's taking ~20 minutes for the workflow to catch up to a single submission right now.

and thanks, right now they are just a placeholder. I'll probably do a more standard horizontal collect for the final solution to simplify it since there will be a lot more columns and complexity I will have to deal with

Paul Newcome

~20 minutes? Ugh.

As for the automation triggering before all data is updated, I have found that using a checkbox column with a basic nested IF to only check the box once all columns are not blank and then triggering the automation off of that seems to be pretty reliable.

=IF(AND(columnA@row <> "", columnB@row <> "", columnC@row <> ""), 1)

L_123

That's an interesting solution, thanks. I'll definitely include that check in my final draft if I go this route.

I'm not sure if i'm going to pursue this or not, it's just to slow. this is to track maintenance items, and I don't want there to be the potential for long wait periods prior to alerts being sent out.I may have to separate the 1 large form into several other ones, or simply use a different software to get what I want.

Paul Newcome

Yeah. I can imagine that many columns can bog things down with this type of setup.

And I tend to use the above trigger for automation as much as I can. In my mind it is more reliable to have a formula run the check and then create a very basic automation based on that than it is to build out more complex automations that may or may not trigger before the rest of the data is updated.

That and formulas are MUCH easier to test than automations and having to wait on emails to come through or rows to move or constantly hitting the refresh button on your browser. Hahaha.

L_123

I 100% agree. I wish there was more visibility on where the workflows are at in their process, and if they have updated. Personally I feel like workflows sending update requests/notifications/alerts should be included inside of the activity log. I can troubleshoot formulas all day and usually the wait is on me. When you troubleshoot workflows you have a delay for every test where you are just there sitting praying it works.

Paul Newcome

+1 for all of that. Better visibility, activity log, troubleshooting...