Reduction of Column Count from dynamic form

L_123
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.

Answers

  • Paul Newcome
    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
    L_123 ✭✭✭✭✭✭

    I've found a partial solution, still testing.

    Start with 10 columns

    Reduce to 5 column using conditional index formula

    Archive to remove formulas

    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
    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
    L_123 ✭✭✭✭✭✭
    edited 09/02/20

    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
    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
    L_123 ✭✭✭✭✭✭
    edited 09/02/20

    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
    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
    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
    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
    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
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!