"Iterative" Formulas or Loops
Hello everyone. I am having an issue where I do not see a way to make any of my formulas iterate any specified number of times, let's say similarly to a "for" loop in a programming language. I am curious if there is a solution I am overlooking or if this is not currently doable with Smartsheet. Please see my example below, which uses for loop syntax from C++ as a way of summarizing what I am trying to achieve:
FORMULA = X
for(# =1; # <= 35; #++) {
X = X + IF(CONTAINS("NO", [Q#]@row), UNICHAR(10) + UNICHAR(10) + ">>> FINDING " + COUNTIF([Q1]@row:[Q#]@row, CONTAINS("NO", @cell)) + " OF " + [TOTAL NUMBER OF FINDINGS]@row + " <<<" + UNICHAR(10) + UNICHAR(10) + "QUESTION: Q#" + UNICHAR(10) + "QUESTION TEXT: \"" + INDEX({Question Text}, #) + "\"" + UNICHAR(10) + "FINDING: " + INDEX({Reason Names}, VALUE(LEFT(RIGHT([Q#]@row, 3), 2))) + UNICHAR(10) + "OPERATOR NAME: " + [Q# Operator Name]@row + UNICHAR(10) + "DEFECT ORIGIN: " + [Q# Defect Origin]@row + UNICHAR(10) + "COMMENTS: " + [Q# Comments]@row, "")
}
I am writing a formula to do the operation contained in the loop above for 35 question responses received from a form. The only solution I can see right now is to manually paste the part in the loop for every single # from 1 through 35. Note: This creates a HUGE formula which actually cannot even fit in a single cell, which I solve by using several intermediate steps across several columns in order to reach the same result as having the whole formula in one cell.
Does anyone have a solution to problems like these? If so, this would be a HUGE help!
Thank you!!!
Best Answer
-
considering that you already got this working I do not know of any other way to do this without multiple helper columns (which sounds like you have already got it done).
If there was a way for each of the question to be setup as rows, then you can easily simplify this and would make for a lot easier modifications later.
Answers
-
There is no such thing in Smartsheet. You can always set up an API program to accomplish whatever it is you are looking for...
also, if you can explain the goal of what you need, myself or others may be able to help you accomplish it without the complications mentioned above.
-
Thank you for your response. What I am trying to do is make an inspection form and make a summary of findings made during the inspection (the formula I mentioned above). The "loop" is supposed to look through all questions and whichever ones contain "NO" means something went wrong, and therefore that question and its associated data should be included in the summary. Example of output:
*** ORDER NUMBER: 123123 ***
INSPECTOR: Inspector 1
LOCATION: BUILDING 1, PLANT NUMBER 1
INSPECTION DATE & TIME: 01/26/21 10:51 AM
TOTAL NUMBER OF FINDINGS: 2
>>> FINDING 1 OF 2<<<
QUESTION: Q1
QUESTION TEXT: "Was ___ filled out correctly?, etc. etc."
FINDING: TYPO ON PAPERWORK
OPERATOR NAME: JOE SMITH
DEFECT ORIGIN: DEPARTMENT 1
COMMENTS: OPERATOR SUBMITTED PAPERWORK WITHOUT CHECKING FOR ERRORS
>>> FINDING 2 OF 2 <<<
... So on and so forth...
END OF OUTPUT
I started considering using the API due to things like this. My only concern is the learning curve I may experience, but perhaps it is worth it. I'm not sure how difficult it is. If you need any more info in order to help me, I am glad to oblige.
Thank you again!
-
This sounds like something you should use Generate Documents for...
-
Hi @Leibel S, that feature seems pretty useful, however my issue is that it seems I have to map columns to fields on a PDF, and since I do not know what the results of the inspection will be, it would be a bit of challenge to only put the findings found at inspection, rather than all responses, even when nothing was found to be wrong. Does this make sense? I suppose it might be possible to make something where I leave room for a large amount of findings but if there are only a few, the remaining fields will be blank... One other thing, the documents seem to have to be generated by a user, and I am preferably trying to automatically create this summary and display it for all to see during our daily meetings, without anyone ever having to intervene. This being automatic would be extremely useful.
What do you think?
Thank you
-
Ok. I get it now. Is this submitted via a Form?
-
@Leibel S Yes, it intakes via a form & I am trying to generate a summary from the responses.
-
considering that you already got this working I do not know of any other way to do this without multiple helper columns (which sounds like you have already got it done).
If there was a way for each of the question to be setup as rows, then you can easily simplify this and would make for a lot easier modifications later.
-
@Leibel S Yes, that's a good point. I agree it would be a lot easier if they were rows... unfortunately I do not think that is possible since it is from a form though. I greatly appreciate your input. It seems as of now, the multiple helper columns will be the best solution, but hopefully eventually Smartsheet will offer a feature to take care of this sort of thing in a better way.
Thanks again!
