Virtual table for summing data

[Ami]

Is it possible to create a table that has only 1 real column and all other columns would be virtual this in order to have a table that keeps summing and counting data?

[Steve]

Yes!

[teambelair]

Is this the best practice to create a stats table? I am looking to create a similar table that would load the user stats. I only need the user’s email so I do not need a real column.

I am asking because I have multiple stat pages and every time I implement it that way, I have to synch them with a parent page. So I create for each page: 2 workflows (Add and Delete) and 3 behavior actions (1 for Add and 2 for Delete).

Instead, I wonder if we could have an entire virtual table that do not require a single real column.

[Ami]

I have created a table with ref IDs and the rest are virtual columns, works great

[teambelair]

Did the same but I have to synch that ref IDs column with the parent table every time a record is added or deleted (2 WF, 3 actions). Was just wondering if there was a better way?

[Ami]

Do your ref values change or are they constant?

[teambelair]

If I understand well your question, it changes. The parent table contains all the Students. The children stat table has a real column with the ref to one Student. Anytime a student is added or remove in the Students table, I am syncing the children stat table.

[Steve]

teambelair:

I have to synch that ref IDs column with the parent table every time a record is added or deleted (2 WF, 3 actions).

The actions will remain necessary, but you could eliminate the workflow by performing the actions from the app that performs the add or delete.

Another approach that I toyed with but never got around to putting into service would be to make your stats spreadsheet generated entirely with spreadsheet formulas. As I recall, I gave cell A1 a formula something like this:

={ "Email"; Users!A2:A }

This sets the value of A1 to Email (the column name for the app), then populates the remainder of the column with the values of the A column of the Users sheet. This sheet will then always be in sync with the Users sheet: rows added and removed in Users will always be reflected in this sheet. This sheet only contains email addresses, which are unique in Users and therefore unique here, and thus suitable as keys. Create a table in your app, link it to this sheet, and you have an auto-updating adjunct table.

[teambelair]

Second option makes so much sense but I worry about sync issue. I m going to get rid of these 2 WF. Thanks!

[Austin]

Why use a second table for all the stats when you could have all the stats in the students table? If your stats require a student put the stats at the source of students.

[teambelair]

I thought of that too but I don’t want to overload the parent table and prefer to keep features in separate tables (especially if the stat table is moved in another app).

[Steve]

teambelair:

Is this the best practice to create a stats table?

I’m not sure there is community opinion on this, let alone enough consensus to declare it a best practice.

teambelair:

I wonder if we could have an entire virtual table that do not require a single real column.

Nope. A row’s existence requires a key column value. A row must exist before virtual columns can be computed.