On a form with multiple TextDispayObjects, each with a formula to display an array, I was wanting to update those TextDisplayObjects if the current record changed via .CurrentRecord.
If I use ShowVariables calling out each Array name, or If I use ‘GraphicsMode / DataMode’ the TextDisplayObjects update when the record changes.
But if I use ShowRectangle Rectangle(0,0,0,0) the TextDisplayObjects do not update.
Should the ShowRectangle statement update those TextDisplayObjects ?
Step 1: Delete the .CurrentRecord procedure.
Step 2: You need to add a database field to the formula. Once this is done, the text will automatically update whenever the current record changes.
Of course, you probably don’t want any database fields displayed in the Text Display objects. That’s fine, there are a couple of ways to manage this.
If you pick a text field, you can use a text funnel to strip out the text. This text funnel has negative length, so it will always result in empty text. However, since the formula now includes this field, it will automatically update whenever that field changes – including any time that the record changes. No code is needed.
arrayformula+Field[2,1]
Another way to do this is with the ignore( function:
arrayformula+ignore("",Field)
Or, you could do this:
ignore(arrayformula,Field)
If you just want the solution, you can stop reading. The rest of this post is just further background information.
What does “Array name” mean? If this is the name of a variable, this should work. But in that case, the variable isn’t going to change depending on what the current record is, so why would the display update? It’s just going to show the same variable’s contents no matter what the current record is.
If the “Array name” is a field, then it should already work without any further ado.
If the array needs to be calculated for each record, I would do that using a formula right in the Text Display object. You could use the callwithin( function if the code is complicated (but it better be FAST code with no side effects).
AAARG! DON’T DO THAT! This falls under “should not ever change the current window” - switching to graphics mode is essentially changing to a different window.
Hadn’t really thought of this before, but I suspect that showrectangle doesn’t work when display is deferred. This is only designed to be used with showvariables, showfields, etc.
However, I don’t think this is a problem. ShowRectangle is intended for use when you’ve changed the configuration of form objects, for example moving them, creating them, or deleting them. You shouldn’t be performing any of these operations in an implicitly triggered procedure.
Bottom Line - don’t use .CurrentRecord for this task, instead add a field to your formula as described above.
When I say Array Name, I am referring to the variable that I use to refer to the Array. To me, arrays are so powerful that they deserve some name beyond just variable. 
I tried adding the field «Qty Returned» to the end of the below formula in the TextDisplayObject and it did then show the correct result of the formula when the current record changed. But it also created a spinning beach ball for 24 seconds every time I moved between records. I took it out, and no beach ball, & no refresh of the formula after changing records. I put it back, the ball returned but the formula was calculated.
Max(Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-365) AND «Sale Date» < (today()-273) AND ««SKU»»=«Product Sku»},"Sales History",true()),
Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-273) AND «Sale Date» < (today()-182) AND ««SKU»» = «Product Sku»},"Sales History",true()),
Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-182) AND «Sale Date» < (today()-91) AND ««SKU»»=«Product Sku»},"Sales History",true()),
Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-91) AND «Sale Date» ≤ today() AND ««SKU»»=«Product Sku»},"Sales History",true()))+«Qty Returned»
I also tried Ignore({Complete Formula here},«Field having 0 as value») and the same 24 second beach ball.
Well sure. You’re using four formulas that scan the entire database when updated. So each time you move to a different record, it’s scanning the entire database four times. Since the scans are taking 6 seconds each, your database probably has quite a few records. Panorama is fast, but it’s not infinitely fast. You’re essentially asking it to group and total the entire database every time you move to a different record.
There no grouping going on. I am building an array of records that contain a single SKU, and then doing the calcs on those records.
Yes, there are 84,000 records in the database being scanned. But if I do the same thing via a procedure, it accomplishes each of those calculations plus 1 more for the Max of all records that meet the requirement and then displays the message window. All is less than 2 seconds.
I didn’t say you were “literally” asking it to group, I said “essentially”. Before the aggregate( function existed the only way you could have calculated this would be by grouping.
The aggregate( function works by scanning the entire database. So your formula is scanning the entire database of 84,000 records 4 times. There’s no scenario where scanning nearly 400k items is going to produce acceptable performance in a form object.
Since I don’t know anything about your procedure, I can’t comment as to why it might take less time. If the procedure selected that specific SKU and then did the aggregate on only those selected records it would be a lot faster.
Local LSKU, LAggregate1, LAggregate2, LAggregate3, LAggregate4, LMaxSale
LSKU = ""
GetTextDialog LSKU, "Prompt", "What SKU?"
Select «Product Sku» Contains LSKU
SelectAdditional «Sku» Contains LSKU
If Info("Empty") = True()
Message "No previous sales were found in the history file."
Field «Product Sku»
OpenFile "Purchasing"
«3MoTempField» = -1
OpenForm "The Bids"
Stop
EndIf
LAggregate1 = Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-365) AND «Sale Date» < (today()-273)},"Sales History")
LAggregate2 = Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-273) AND «Sale Date» < (today()-182)},"Sales History")
LAggregate3 = Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-182) AND «Sale Date» < (today()-91)},"Sales History")
LAggregate4 = Aggregate({Quantity},"sum",{«Sale Date» ≥ (today()-91) AND «Sale Date» ≤ (today())},"Sales History")
LMaxSale = Aggregate({Quantity},"Maximum",{«Sale Date» ≥ (today()-365) AND «Sale Date» ≤ (today())},"Sales History")
Message «Details» + "
SKU: "+ LSKU + "
Quarter 1 ending: " + DatePattern((today()-273),"mm/dd/yy") + ": " + LAggregate1 + "
Quarter 2 ending: " + DatePattern((today()-182),"mm/dd/yy") + ": " + LAggregate2 + "
Quarter 3 ending: " + DatePattern((today()-91),"mm/dd/yy") + ": " +LAggregate3 + "
Quarter 4 ending: " + DatePattern(today(),"mm/dd/yy") + ": " + LAggregate4 + "
Max Single Sale: " + LMaxSale
Well see, your code does exactly what I said. So of course the procedure is much faster, the aggregate( function is only scanning a few hundred records 4 times, not 84,000.
By the way, it would be slightly faster if you used:
Select «Product Sku» Contains LSKU or «Sku» Contains LSKU
instead of using the separate selectadditional step. Probably not noticeable, but I think it is cleaner to do it that way.