Temporary Formula Variables

[MultiTech]

Something similar to this has been asked for before, but I thought I would crystalize this version of things.

I would love for there to be a way for me to create and use a temporary variable inside a formula.

!!var [Parent_List] = [Parent_Ref].[Related Whatevers]!!
!!var [Derivative_List] = [Parent_List][Whatever_Values]!!
!!var [First_Item_In_List] = Index([Derivative_List], 1)!!
If(Count([Derivative_List]) > 0, 
  if([First_Item_In_List].[Items_Status] = "Active", 
    DO_SOMETHING_FOR_ACTIVE_THINGS, 

  if(CONTAINS([First_Item_In_List].[Items_Status], "Holding"), 
    DO_SOMETHING_FOR_HOLDING_THINGS, 

  [First_Item_In_List].[Items_Status]
  )), 
DO_SOMETHING_FOR_NO_RELATED_RECORDS
)

• The above formula may be a poor example, I'd probly actually want to store those variables in a table for use elsewhere, but you get the syntax of what I'm trying to say - I hope (^_^)

There are numerous times where we need to do X number of things to the same converted value:

• applying some maths to a number/price/time/etc.
• converting a date into the text version
• creating a derivative list for processing purposes
• looking up some value deep down a chain (that's maybe a list dereference we'll need to create first, in order to process things where/when we need).  

It would be nice if there was a way to create the temporary bits inside the formula, where they're created on the fly when needed, and gone once the processing is done.

--------------------------------------------------------------------------------------------------

Literally taking all my cues here from Apps Script

--------------------------------------------------------------------------------------------------

If we could do this, we wouldn't have to either

1. save the value in an actual column
2. re-using the conversion formula over and over

If we could save the conversion into a temporary variable - INSIDE THE FORMULA - we could save so much overhead that might not need to be there.  Many times, these "supporting columns" are literally just there because we need a piece of data at a certain point, or in a certain way, for things to work.

-------------------------------------------------------------------------------------------------------------

As always, thanks for considering!

[SkrOYC]

I have dreamed of a way we can do something like this so that we don't need to add virtual columns to our tables.

Take a look a the need for a chained list dereference. I mean, we can't do it right now.
The solution is nested SELECT/FILTER expressions which makes everything really messy

[MultiTech]

---

@SkrOYC wrote:

The solution is nested SELECT/FILTER expressions which makes everything really messy

---

Actually the solution is to copy the list-dereference into the level you need to de-reference it from

• NEVER use select() to do these things.
• SELECT() will show you it's possible, then it's a matter of creating the appropriate reference connections between your two levels, or installing a hot-linking system or something, to make everything work

Sometimes you have to stray away from data-normalization techniques to get what you want. This means that sometimes you have to create your list-dereference on a certain level of you database, essentially copying that list from a lower level up higher in the hierarchy - this way you can access it from the de-reference (or deref chain).

[SkrOYC]

Sure, that's why I'm saying it's needed, because we have to add virtual columns today when it could be solved with a variable inside the same expression. The only way to do something like this without the usage of extra VC is by using brute force, which is clearly a really bad idea

[Luis_Rodriguez_]

Like the "LET" function in EXCEL. 

yeah needed.