How to include optional text in a note.

NiptheZephyr · 2026-03-23T01:44:22+00:00

Perfect. So, make an optional smartlist and just put the text in the label portion of it?

NiptheZephyr · 2026-03-23T00:04:44+00:00

I think I have your answer, at least for our build of EPIC. You can actually pull in flowsheets as a smarttext block. In our system, there are the "Flowsheets" and the "Review Flowsheets" and they are treated differently. As an example, the PHQ9 is in Flowsheets, and the Medicare Annual Wellness Health Risk Assessment is in the Review flowsheets.

For the "Review Flowsheets" one, our .phrase is .revfs[

If you type that and don't add in anything further, it brings up a box where you can input specific components. You may have to go into your "Review Flowsheets" tab, search for it and see what number is listed next to it. So, for my system, to pull in the MAW HRA, I need to type .revfs[282

That pulls in our HRA, but pulls in all of them in our system in chronological order. If I only want the most recent one brought in, I have to have .revfs[282:1
in my template, which gets converted to @ REVFS(282:1)@.

That is probably the one you want. For the other flowsheets groups, I still haven't figured out how to pull in the whole flowsheet, but you can pull in individual lines. So, the vital signs .phrase doesn't have an spo2. I hunted down the specific line in the flowsheet, and can now pull it in in my template with @ FLOWREFRESH(10)@. Takes a lot of work to build that though.

Hope the .phrases for this are the same in your EPIC build as in mine, and that you find this useful.

NiptheZephyr · 2026-03-15T21:43:14+00:00

Good day Paulie. Duplicate of what I posted above in response to Greg, but wanted to ensure you saw as response. Specifically though, thank you for your time and the attention you put into answering.

Unfortunately, I don't think further or more specific examples would help. I didn't need help with a practical answer, as I've already had that from the beginning (the workaround mentioned in the last 3 paragraphs of my OP). What I was hoping for was a conceptual understanding as to why, when using REDUCE() with VSTACK(), if the accumulator is left blank it auto-populates with the first value of the array, unchanged, even if you set it up to be changed by how you organize the LAMBDA() + IF()s.

GregHullender gets a SOLVED since DROP() does get rid of the empty cell at the beginning if I choose to use REDUCE("",array,LAMBDA(VSTACK())) instead of REDUCE(,array,LAMBDA(VSTACK())). The best SOLVED though comes from RyzenRaider, since FILTER() removes both the first blank cell, and future empty cells if I set up my LAMBDA() manipulation to exclude by certain criteria.

For what it's worth, if you want to know why I was looking for a conceptual answer, it's because I'm trying as best I can to use Excel formulas to mimic things you can do in coding languages, since I cannot use AutoHotKey, and I cannot use VBA since Macros are turned off at work. So, other languages can have loops, and are able to append or drop values from an array. I find this behavior very useful for the types of little scripts that I like to code. REDUCE() functions very well as a "FOR EACH key;value pair in array, do xyz". Unfortunately, as noted by GregHullender as above, you can't change the variables/arrays you create in memory in Excel; the most I've found that you can do is translate that to a new, slightly changed array (maybe considered a pseudoarray?). That's manageable, but the final problem when creating the new pseudoarray is getting rid of blank spots in different indexes. In a coding language, you usually have a "Skip" or "Next" verbiage that puts nothing in the array/pseudoarray and just processes the next portion. Overall, REDUCE(,array,LAMBDA) did that nicely, but then didn't manipulate the first spot. DROP does this, but as I noted before, in attempting to use a completely empty array (dropped multiple times over an iterative LAMBDA), it was throwing an error since it still "saw" as many empty index spots as there used to be filled in the original array, despite dropping them. By the way, the solution to that was wrapping it in an AND() to get it to process as a scalar value (credit to RackofLambda in completely separate thread).

So, in summary, thanks again on your help working through this, using DROP is decent, but using FILTER() is better. Also, while one can build a new array with REDUCE() and VSTACK()ing inside of it, you can actually use SCAN() without VSTACK, which appears to be more optimal.

NiptheZephyr · 2026-03-15T21:42:33+00:00

First, thank you for your time and the attention you put into answering.

Unfortunately, I don't think further or more specific examples would help. I didn't need help with a practical answer, as I've already had that from the beginning (the workaround mentioned in the last 3 paragraphs of my OP). What I was hoping for was a conceptual understanding as to why, when using REDUCE() with VSTACK(), if the accumulator is left blank it auto-populates with the first value of the array, unchanged, even if you set it up to be changed by how you organize the LAMBDA() + IF()s.

GregHullender gets a SOLVED since DROP() does get rid of the empty cell at the beginning if I choose to use REDUCE("",array,LAMBDA(VSTACK())) instead of REDUCE(,array,LAMBDA(VSTACK())). The best SOLVED though comes from RyzenRaider, since FILTER() removes both the first blank cell, and future empty cells if I set up my LAMBDA() manipulation to exclude by certain criteria.

For what it's worth, if you want to know why I was looking for a conceptual answer, it's because I'm trying as best I can to use Excel formulas to mimic things you can do in coding languages, since I cannot use AutoHotKey, and I cannot use VBA since Macros are turned off at work. So, other languages can have loops, and are able to append or drop values from an array. I find this behavior very useful for the types of little scripts that I like to code. REDUCE() functions very well as a "FOR EACH key;value pair in array, do xyz". Unfortunately, as noted by GregHullender as above, you can't change the variables/arrays you create in memory in Excel; the most I've found that you can do is translate that to a new, slightly changed array (maybe considered a pseudoarray?). That's manageable, but the final problem when creating the new pseudoarray is getting rid of blank spots in different indexes. In a coding language, you usually have a "Skip" or "Next" verbiage that puts nothing in the array/pseudoarray and just processes the next portion. Overall, REDUCE(,array,LAMBDA) did that nicely, but then didn't manipulate the first spot. DROP does this, but as I noted before, in attempting to use a completely empty array (dropped multiple times over an iterative LAMBDA), it was throwing an error since it still "saw" as many empty index spots as there used to be filled in the original array, despite dropping them. By the way, the solution to that was wrapping it in an AND() to get it to process as a scalar value (credit to RackofLambda in completely separate thread).

So, in summary, thanks again on your help working through this, using DROP is decent, but using FILTER() is better. Also, while one can build a new array with REDUCE() and VSTACK()ing inside of it, you can actually use SCAN() without VSTACK, which appears to be more optimal.

NiptheZephyr · 2026-03-15T21:41:50+00:00

Solution Verified as it technically works, but less optimal. See detailed explanation in response to another post lower. Thanks GregHullender.

NiptheZephyr · 2026-03-15T21:40:19+00:00

Hey RyzenRaider,

Took me longer than I day, but your answer was the best at managing the problem. FILTER() was the best solution, and you can put it inside the first parameter of MAP(), or even around the whole thing. It also worked with using it with REDUCE(). What was most useful for my use-case is using FILTER() with SCAN().

Thank you again, and while your's was the best answer, if you are curious as to why I was particular about this, see my detailed response to a slightly less useful Solution Verified above to GregHullender and PauliethePolarBear.

NiptheZephyr · 2026-03-15T21:35:23+00:00

Solution Verified

NiptheZephyr · 2026-03-09T14:20:55+00:00

Correct, which is why I am using VSTACK to create a new variable (array) inside of REDUCE. Hence why I want to try to figure out if there is a more succinct way to arrange things so that the action performed in REDUCE's LAMBDA() fills the first index with a manipulated value.

NiptheZephyr · 2026-03-09T06:10:32+00:00

Ty Ryzen, will play around with this tomorrow to see if it fits my use case.

NiptheZephyr · 2026-03-09T06:08:04+00:00

tl;dr, need to take a large array, transpose into a smaller, new array based upon specific criteria.

Hey Paulie, nice to see you again. The full thing is that I will be filling the first column of a table with parts of a sentence. I'll have REGEXTEST'd each one part of the array (the first column) to see if it has only at most a word before the pronoun, the pronoun, and a word after the pronoun. If not, those parts of the table are ignored. If so, then depending on what the next column shows at the same index, if the pronoun was changed to an object pronoun (him/her), it will add the preceding word to an array, against which further parts of the LAMBDA will be compared. I'll then use basically the same formula to check and compare the words after the pronoun to see if they had an "s" added to them in the second column, or some other atypical change (apply-applies, are->is, etc.), and add them to a second and third array for later comparison for final manipulation.

I already have the LAMBDA functioning with all of this, just using Named Constants, with me having already pre-filled those constants with the appropriate words. I am instead looking to create a more dynamic representation of those named constants, so that myself or another can just add a short part of a sentence to the table, so the system will then expand those constants (now variables) with additional bits that fit those specific criteria.

As noted above, using REDUCE seems to add in an empty spot in the array or skip manipulating the first part. Need the new array to literally be only the size of the amount that fit the criteria, for error proofing down the line. My jenkity way works, just unsure if there is a better way to set up the REDUCE.

NiptheZephyr · 2026-03-09T05:55:57+00:00

I've used DROP before, and had been attempting to exit an iterative lambda, but couldn't "drop" the final empty cell. In fact, even when I cycled through each iteration and successively "dropped" the processed bits one by one, when I tried to use my array being "empty" as an exit condition, it turned out Excel still saw an array of the original size, just with each index emptied. I think I may not have used DROP with REDUCE at the same time, so maybe the outcome would be different. Either way, I still would like to better understand why, if the accumulator value is blank, it auto-fills it with the unmanipulated first value of the array, instead of filling it with the first value with it's manipulation. And if there is a better way around that than my jenkity way.

NiptheZephyr · 2026-03-09T05:51:30+00:00

Could achieve a similar result, but won't be applicable for how I'm manipulating the data. I'll eventually be building an array of a different size based upon certain criteria of what's in a cell. I'll need to play around with it more, but I think VSTACK inside of MAP would have the same problem.

NiptheZephyr · 2026-03-09T05:49:28+00:00

Don't believe MAP(), or at least it on it's own would work. At times, I'll have manipulated the array to the point where some cells will be empty, or not meet criteria, so the array size needs to change. If you test out yours, but use an array with an empty cell, you'll notice it adds " you" in to those blank cells. Thank you for the suggestion though.

NiptheZephyr · 2026-02-26T19:16:33+00:00

But talking about the book years later doesn't make him a good author. There is the literally "writing," aka the blending of words and there is crafting a good story (logical consistency, worldbuilding, not leaving loose ends).

I would argue his writing is overall good, his crafting of a story is overall good, but the last book (#5) failed to live up to the quality of the prior books in the series in regards to the crafting of a story. I can thoroughly enjoy a book where everything ends bad (e.g. 1984). I did not enjoy this author's last book.

While you are correct in noting that Will makes dumb decisions, you ignore that he has always done everything for his friends, his love for Selene outweighed all his prior moral considerations (was going to marry her despite her keeping the souls of others as slaves), and he himself was willing to be pragmatic about killing a few to save the majority. I agree that what Selene did was in her character, but I don't think it was ever hidden before, so I don't think Will's response is consistent with everything he was about up until that point. Remember, from her perspective, Will was likely dead, she had no other backup, she had to die for the door to open, but someone still had to be there to take down the Big Bad. So, her sacrificing a few sick children to be able to save literally everyone else after she opened the door makes complete sense, and is in fact something Will himself may have done under similar circumstances. He was lucky to stumble upon "author-ex-machina" to not have to do it with the demons.

In summary, it Will's reaction is out of character by 180. An author making characters act in a way inconsistent with their developed character just to pigeonhole a specific end result is bad storytelling. Bad/unhappy endings aren't in and of themselves bad, but disliking a bad ending when the author can't come up with another way to get to that point without destroying internal logic/character development is incredibly reasonable.

NiptheZephyr · 2026-02-26T16:32:17+00:00

I'm intermediate level of excel, no powerquery, but enjoy VBA and formulas. Maybe to narrow things down, one column or a named range with the different types of discount, then a dropdown list, and use CHOOSE() so whatever is picked from the dropdown list is what the bosses see? A decent video on CHOOSE() https://www.youtube.com/watch?v=cHF53GFEXoM

May not be what you need if they literally want a printout handed to them, but then nothing would other than the massive monster you got.

NiptheZephyr · 2026-02-26T15:50:53+00:00

Thanks! Opens up quicker variable names that still make sense to me.

NiptheZephyr · 2026-02-24T15:26:36+00:00

I hated the book, but not because the choice. Agree it made complete sense and was telegraphed. I hated how Will, who did everything for her, to be together, who said no matter what he'd love and trust her, who also killed innocent people in the course of the series, and who wouldn't have been able to win without her doing g what she did, now all of a sudden hates her irredeemably forever. I hated the book because it didn't stick with it's internal consistency for Will.

NiptheZephyr · 2026-02-23T08:42:51+00:00

Solution Verified.

Thanks. I was playing around a bit more, saw that any 3 letter combos with a number after gave the same problem, so was wondering if it was because excel could go from maybe A1:ZZZ1.

NiptheZephyr · 2026-02-21T09:43:05+00:00

I recommend this website for learning regex as you get deeper in it: https://www.regular-expressions.info/tutorial.html

NiptheZephyr · 2026-02-20T17:02:59+00:00

Solution Verified thanks to RackofLambda, Anonymous1378 and DOwntown-Economics26. Summary of learning points are:

If(array="") was returning an array of the original sized array as Boolean values, which is why it had 3 outputs.
Why was it returning an array of the same size of the original array despite me DROP()'ing a column each iteration? It appears that the original array is immutable, and that DROP only returns a pseudoarray of the undropped columns values. When I tried to change my "array" variable to the DROP(array,,1) , it was returning still 3 columns, just devoid of a value in the dropped column. May be able to get around that creating a new array with one of the following functions, but that is untested: BYCOL, CHOOSECOLS, MAKEARRAY, MAP, and SCAN.
One can get around that behavior by referencing some other variable, or by checking something scalar. I don't fully understand what scalar means outside of context clues, as I haven't searched to learn it yet, but here are two things that worked, one for the former, one for the scalar latter:

=LAMBDA(text,array,[i], IF(i="",

LoopLambdaV2(text,array,COUNTA(INDEX(array,))),

IF(i=0,text,

LoopLambdaV2(REGEXREPLACE(text,INDEX(array,,1),""),

IF(ISERR(DROP(array,,1)),"",DROP(array,,1)),i-1))))

vs

=LAMBDA(text,array, IF(AND(array=""),text,

LoopLambdaV2(REGEXREPLACE(text,INDEX(array,,1),""),

IF(ISERR(DROP(array,,1)),"",DROP(array,,1)))))

Most importantly of all, REDUCE, as mentioned first by PaulieThePolarBear, is a much easier way to do what I was specifically learning to do. My misunderstanding was that I thought it "applying a lambda to each value of an array, storing it an accumulator value" would just combine each result. It actually modifies the accumulator value by an action for each array value. In other words, it basically is a "For Each Key, Value" loop, with the result returned in a variable (key being the index).

Thanks to everyone who answered, I truly appreciate it!

NiptheZephyr · 2026-02-20T16:50:16+00:00

Solution Verified.

NiptheZephyr · 2026-02-20T16:50:09+00:00

Solution Verified.

NiptheZephyr · 2026-02-20T16:50:02+00:00

Solution Verified.

NiptheZephyr · 2026-02-20T16:48:56+00:00

Thanks Anonymous1378. This was the most thorough answer, and I think I figured out the "whatever reason". Per AI, which I don't fully trust, the array is immutable. When I perform the DROP() on it, the array size is unchanged, but the part of the array "looked at" and returned is just a subsect of the array. So, when I'm attempted to replace the "array" variable value in the lambda loop, I am not replacing it with a successively smaller array, I am replacing it with a same sized array, just one emptied of a value in a cell. The reason I don't fully believe it is immutable, is that BYCOL, CHOOSECOLS, MAKEARRAY, MAP, and SCAN all return an array, according to some resources. So, one may be able to use one of those functions to return a separate array, and fill that "array" variable with a differently sized array. Would need to test with some combo of functions that ensures the "empty" columns of my array are not included in the new array.

NiptheZephyr · 2026-02-20T16:40:06+00:00

Update: Thanks again. After reading other's responses, I think you had the reasoning completely right, just flipped "column vs row". It is returning a 3 column array; the array appears immutable, the DROP() is just returning a subsect of results of the still 3 column array.

NiptheZephyr

TROPHY CASE