Partitioning tables using variables

Gerald_Kariuki · 03-30-2019 07:29 AM

Can I partition tables to say items from the last 3 months

Mahesh

Similar to your requirement, I am also looking for a way to a express a filter for data slice that can be based on some variable value. Right now, the only one I have found that comes close is USERSETTING() where one can have any setting with an initial desired value (3 in your case). The problem is that for that initial value to be available it requires user to first explicitly enter and save the usersetting. I did not find to be an acceptable solution for my app because I felt user will not even be aware of such setting.

Experts
So, to add to @Gerald_Kariuki’s question, is there a way to set variables with initial value for an app and reference those variables in expressions (like in your case to filter records from last 3 months)

Steve

Not easily. You’d have to have a separate table for the settings. If you want them to differ by user, each user would need their own row and your app would have to use the appropriate one.

What I’ve experimented with is an app-wide settings table (e.g., AppSettings) with a single row. To get the value of a single column, I use ANY(), as in ANY(AppSettings[AppTitle]) to get the app’s title. List values are a pain to use like this, and derefs don’t work at all, so there are limitations.

For per-user settings, you could have a table of settings with a row per user keyed to their email address, then use a security filter to limit the row the app sees to only the logged-in user’s. This table could then be used as the app-wide table described above (e.g., ANY(UserSettings[AppTitle])). The (additional) downside here is creating the row per user.

Mahesh

@Gerald_Kariuki, my apologies if I am taking your thread in a direction you do not want. I will gladly stop here and switch to another thread.

@Steve, I like your suggestions. It did not even occur to me about creating a table to hold settings for each user. Let me give a little bit more detail so you understand the context why I need variables.

In my case, the meetings (managed by this app) are held every other week. I have slice filters which bring up data for the session that is the most current i.e. closest to the day the app is being used. So, up until that Saturday when meeting is held, it will show data for that upcoming Saturday. Right after that day it will show data for the next session coming up 2 weeks from then. So as you can imagine date is key to filtering out the right data in these slices
.
I have these really complex expression to give me the date, and using that I filter.
Now the problem is that I have to repeat this complex expression every slice I need to filter like this. Instead if I could hold a variable “CURRENT_SESSION” some place that gets initialized at app initializing, then I can keep using it in all my slice filters. That would not just simplify the filter but also made these expressions more readable.

As I said doing it with UserSetting() works great but the user needs to explicitly enter Setting and save it, which is not reasonable to expect from users to do.

Not sure how I can leverage the various good ideas you offered to solve this.
Interested in your thoughts.

Thanks!

Steve

Oh, even better, since the global value can be calculated rather than input.

Create a worksheet to contain the global settings (AppSettings).
The worksheet needs only a single column (id) to contain the row key.
Add a single row by setting the row’s id column to 1 (or whatever you want).
Add the worksheet as a read-only table in your app.
Configure the id column in the app.
Add a virtual column to contain the calculated session date (current_session) with the appropriate app formula.
Confirm the column calculates the correct date.
Reference the column in your slice filters using ANY(AppSettings[current_session]).

Because there’s only one row in AppSettings, there will only ever be one value in the column value list generated by AppSettings[current_value], so ANY(...) will always return the expected value.

By merit of being a virtual column, the column value will be recalculated each time the app syncs.

Mahesh

Brilliant! I will implement and let you know.

Since I am not initiator of this thread, I cannot not mark it as a solution. Will let @Gerald_Kariuki decide on that.

Thanks a lot @Steve!

Mahesh

@Steve,
Few questions about the idea:

Can AppSettings values that are not read-only be modified at run time by some user Action? In the above example, say user want to look at data for past or future session and they perform some Action that changes AppSettings[current_session]. I guess that will work, just checking if use of virtual column would restrict that.
For those virtual columns, one would intialize the column value using the “Initial Value” expression and not “Formula”?
Is a resync needed for slices using filters that reference AppSettings[current_session] to start showing the new set of filtered data using the changed setting value?
For some complex calculations, it would help to have a virtual column be able to reference the value of another virutal column within the AppSetting table. Will that work?

Steve

Can AppSettings values that are not read-only be modified at run time by some user Action?

They could, but those changes would affect all app users. My suggestion is intended for global settings that affect all users.

For those virtual columns, one would intialize the column value using the “Initial Value” expression and not “Formula”?

No. Virtual columns must have an (app) formula.

Is a resync needed for slices…

No, changes to the AppSettings row would affect slices immediately, without a sync.

[Can other virtual columns reference AppSettings columns?]

Yes.

Mahesh

Thanks for the clarification @Steve.

I realize I did not word my last question well.
Here’s the reworded question.

For some complex calculations, it would help to have a virtual AppSetting column be able to reference the value of another virutal column within the AppSetting table. Will one virtual column in a table referencing another virtural column in the same table work correctly?

Steve

Indeed it would, yes!

Mahesh

wow! that’s impressive capability. Thanks @Steve.

Mahesh

@Steve,

They could, but those changes would affect all app users. My suggestion is intended for global settings that affect all users.

As I am implementing I realize there are some values that will need to be user specific. These are really variable kind of values that could be different for each user, e.g. the session date user last chose to look at and need other views to use.

I could create another table just like AppSettings that is keyed by USEREMAIL() but that would require knowing all the users who would be using the app ahead of time and creating a record for each one of them. That would be restrictive.
Alternatively would need to find some way to create a new record when a new user logs in. Not sure that is feasible.

Thoughts? Alternate ideas?

Thanks!

Steve

Virtual columns are inherently user-specific, so if all you need are virtual columns, go ahead and attach them to the AppSettings table.

If you must have a normal table for user-specific settings, yes, each user would need their own row, which means finding a way to create those rows, which, yes, is restrictive. The best idea I’ve come up with–that I haven’t implemented yet, so can’t vouch for its feasibility–is to use an expression in UX > Options > Starting view that directs the user to a form to create the row if the row doesn’t already exist. The form doesn’t necessarily need anything more than a Submit button, and perhaps an explanatory message to the user.

Once the UserSettings table is implemented and keyed to USEREMAIL(), you could use a security filter to limit the rows available to the device to only the one row matching the current user. Then you could reference the current user’s column values using the same style of expressions used with AppSettings, e.g., ANY(UserSettings[current_session]) (assuming current_session is also/instead a per-user setting).

Mahesh

@Steve, Nice idea!

form to create the row if the row doesn’t already exist.

So the view would show for everyone? or just folks who do not have a row? If it is the latter, I do not understand how would one make the form show conditionally? In other words, where would the conditional logic reside?

I had previously asked

Can AppSettings values that are not read-only be modified at run time by some user Action?

In response you said

They could, but those changes would affect all app users. My suggestion is intended for global settings that affect all users.

So trying to reconcile that with the following statement

Virtual columns are inherently user-specific, so if all you need are virtual columns, go ahead and attach them to the AppSettings table.

So, here’s what I was thinking
- have virtual columns that would hold values specific to the user
- user could potentially set values on those virtual columns i.e. they are not read-only
- various views would have filters tied to those virtual columns

Just want to confirm with you with this implementation: When the user’s action changes the value of a virtual column that is used by view filters, it won’t affect other user’s view filter.

Steve

In UX > Options > Start view, use an expression like this:

IF(
  ISBLANK(UserSettings[email]),
  "SetupUserSettings_Form",
  "NormalStartingView"
)

where SetupUserSettings_Form is the name of the form view that adds a new UserSettings row, and NormalStartingView is the name of whatever view the user should see otherwise. The expression, ISBLANK(UserSettings[email]), can be any expression that returns TRUE if the current user has no UserSettings row.

Steve

All virtual columns are read-only, in that neither the user nor actions can directly change their value (also true of normal columns with app formulas).

If you want the user to be able to directly modify (or use actions to modify) settings that apply only to them, those settings must be in normal (not virtual) columns and should be in the UserSettings row (not the AppSettings row).

Mahesh

Thanks for the ideas and clarification @Steve !

I will plan on using this approach for read-only variable that are calculated once at app initialization time.
For other variables which need to be modifiable, can a UserSetting be written using a user action?

Steve

Yes

Aleksi

Have you checked this article how to do that… https://help.appsheet.com/performance-scale-reliability/scaling-to-large-data-sets/scaling-using-dat...

Steve

“Partition” is a specific feature in AppSheet. I’m not sure you’re use of the term is the same as AppSheet’s. What are you trying to accomplish? If you want to limit the data the user sees, you can use a slice. If you want to limit the data the app itself has access to, you can use a security filter.

Mahesh

thanks @Steve but which of the following options does one use to set UserSetting via Actions?
My guess is it is setting some column on a “UserSetting” table. Interested in reading any post/documentation on how to do this. Thanks!

Steve

Use an action of type Data: execute an action on a set of rows to select the rows in UserSettings you want to affect and apply another action of type Data: set the value of a column to each of those rows.