Virtual Columns - The Theory..

[RoryF]

Hello All..

I have a theoritical question regarding Virtual Columns.

In my mind (possibly wrong) - if I create a VC, then there is a "cell" (RxC) type of "resource" (whatever that is) being allocated to each row for the VC "somewhere".  Which is fine if I actually have a value that needs to be stored in each of these VC "rows". 

However, lets say I have a "global" variable that I am trying to track / store (lets say the number of days data that I need to filter - which is a single App wide value) then surely using a VC - while convenient - is very wasterful.  In my head (again) I need a single varlable that holds a single value for the App (say, 7 or 14 or whatever) but if I use a VC I have that value is all of rows for which I have data.  So an inefficient use of resources?

Now it may be that Appsheet is smarter than me and doesn't actually store a static value in each of the VC "rows" and somehow aggregates them (not sure how that would work). 

My point being that a VC does not appears to be an efficient way to store global variables.  I am looking at what the possible alternatives are (one to spin off a Google sheet that stores these value and use that).  

Thoughts anyone?

Regards,

RoryF

[1minManager]

Your right about VC's and global variables in general.  Some people have used Usersettings to store stuff.  But I've had issues where you can't always hide it or otherwise stop users from editing it.  Another way (taught to me by Appsheeet Jedi @MultiTech) is to use a slice to hold data.  Finally there's the old fashioned method of just writing it to an actual spreadsheet cell then referencing it from there.

[RoryF]

Thanks for the reply.  My thoughts now on Global variable(s) is to use a Google Sheet "worksheet" (the terminology surrounding Google Sheets is a bit confusing) to store "Settings" - a static table with only possibly a couple of records.  And to use this as the repository for these variable values.  This can then easily (I assume) be used as a table / data source that I can refer to (don't see the need / benefit in getting a Slice..).  And the bonus is that I can create any number of VC's on this table in the knowledge that there will only be a handful of records ever in that table. 

That is the theory - I will see how it works out in practice..

Regards,

RoryF

[MultiTech]

---

@RoryF wrote:

Let's say I have a "global" variable that I am trying to track / store (like the number of days that I need to filter the data for - which is a single App wide value)

---

This type of variable is user-specific, so in this case you'd be best served storing that value inside a record for your user in a Users table.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Current-User-Slice-How-to-conform-your-app-aroun...

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

Another thing you might do is combine a User variable (like I was talking about above) with another slice, which actually holds the results of the selection.

For example:

• Let's say I had a field where users could specify the "Locations" they want to see data for
  • This would live on the Users table
  • Each user would be modifying their own user record, so you don't have cross-contamination issues
• I could also create a slice that's looking at this field and holding the results of the selection
  • This would give me the list of locations the user selected inside the slice.
  • Anytime I needed to know what locations are selected by the user, I can simply call that slice:
    Current_User_Selected_Locations[Location_ID]

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

With this method you can also get derivative lists globally as well

Since the slice is holding the actual records, you can get any other column values from the slice just as easily as you can the actual Location IDs.

• Current_User_Selected_Locations[Location_City]
  • Gives a list of all the cities from the locations selected by the user
• Current_User_Selected_Locations[Location_State]
  • Gives a list of all the states from the locations selected by the user
• Current_User_Selected_Locations[Area_Code]
  • Gives a list of the area codes
• Split(Concatenate(Current_User_Selected_Locations[Related Contacts]), " , ")
  • Gives a list of all the [Related Contacts] from the locations selected by the user
• Split(Concatenate(Current_User_Selected_Locations[Related Items]), " , ")
  • Gives a list of all the items at the locations selected by the user
• Split(Concatenate(Current_User_Selected_Locations[Related Orders]), " , ")
  • Gives a list of all the orders at the locations selected by the user

You can also utilize the slice in other slices for further derivative global variable slices

• in([Contact_Location], Current_User_Selected_Locations[Location_ID])
  • Will show Contacts where the location is selected by the user
• in([Order_Location], Current_User_Selected_Locations[Location_ID])
  • Will show Orders where the location is selected by the user
• in([Order_State], Current_User_Selected_Locations[Location_State])
  • Will show all the corresponding orders from the states where a location is selected by the user; meaning: each location has a state, this will show all orders from any state where a location is selected

Lots of advanced stuff can be accomplished with global variables like this

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