We have amassed thousands of rows of data aft...

[Charlie_Wells]

We have amassed thousands of rows of data after using the app for the last few months (yay!). I have a security filter that only shows today’s data in the app ([Date]=TODAY(), where [Date] is a column in the table). That works great.

We’re using Google as the backend.

I want to allow one of the users to display and generate reports on demand for any day. Rather than reading in thousands of rows I have created a table, called View Date, that allows them to specify the date they want to view data for.

The challenge:

Is it possible to have the security filter check the value in a column in View Date? Something like:

[Date]=View Date[Date]

If so, what is the syntax? Can I use SELECT (the syntax for which is impossible to find in the documentation)? We would then sync to refresh the data view.

EDIT:

I realized I can use MAXROW to get the date specified by the user in ViewDate (rows are keyed by timestamp). I’ll give that a try and will report.

[Charlie_Wells]

@Aleksi_Alkio Sorry I wasn’t clear. Here’s the scenario.

There are two users: the office and the field user. Because we create thousands of data I only show today’s data to both. No real need to view earlier data.

The office sometimes needs to view earlier data, however. I can show them all data, but that takes an eternity to sync and display since we have thousands of records. All I need is for the office to be able to filter the data on demand. So, say, “show me the data for May 15th, 2018” or “show me the data for June 6th, 2018”. Basically I want a user-specified filter.

The ViewDate table allows the user to enter a data in the Date column (the date they want to view the data for). So the user will enter 05/12/2018, say, to filter the data by that date.

To accomplish that I want to do the following:

a) save the value of that column with the user-specified date in the ViewDate table, and then sync;

b) then in the security filter attached to the master data table I want to have an expression that says something like ‘filter the data so that the Date for each row matches the date specified by the user in ViewDate.’

The rows in ViewDate are keyed by DateTime, so what I want to do is get the Date value for the MAXROW in that table to use when filtering data in the master data table.

Pseudocode: MasterDataTable[Date]=MAXROW(‘ViewDate’,Key)[Date]

So all I want to know is how to write that expression.

[Aleksi_Alkio]

For that “View Date” add an email column. Then use a security filter so it will show only user’s own record so he/she can modify the date when needed. From that record you can then read the date (one value) with the expression LOOKUP(USEREMAIL(),View Date,Email,Date)=[Date]

[Aleksi_Alkio]

I’m afraid you don’t have control for that settings view. The only way could be the Validation rule something like IN(USEREMAIL(),{emailA@mail.com,emailB@mail.com,etc})

[Charlie_Wells]

@Aleksi_Alkio Maybe I can set up a Ref in the master data table to ViewDate’s MAXROW, call the column RefViewDate. Then in the security filter I could say [Date]=RefViewDate[Date].

I’ll try it unless you have a more elegant suggestion.

[Fernando_Lopez1]

You can use usersettings, create a date field (ReportDate) on the office app, so this user can select the date. In Security Filters add USERSETTINGS(“ReportDate”) = [Date]

[Charlie_Wells]

Thanks @Fernando_Lopez! That’s sort of what I’m doing (didn’t know about USERSETTINGS), but the question still remains: the ReportDate column/field is in a different table, not the one with the rows with [Date] that I want to filter. So I’m trying to figure out how to look at that from inside the security filter.

Unless I’m missing something about USERSETTINGS? I’ll see if I can find that in the documentation.

[Aleksi_Alkio]

+Charlie Wells I was thinking exactly what you are looking for. There is no need to use MAXROW because you will have only one record in your “ViewData” table (because of the security filter). You can do the same with the Usersettings as well… the workflow is the same.

The purpose of the security filter with your “ViewData” is this… then the user will see only his/her own record.

[Charlie_Wells]

@Aleksi_Alkio thanks! Yeah, I know about the security filter - I already use it. I want to filter the data by a user-specified date and didn’t know what that expression would look like. It seems like usersettings may be what I need. Thank you!

[Aleksi_Alkio]

If you use Usersettings, then the formula could be like Usersettings(FilterDate)=[Date]

[Charlie_Wells]

USERSETTINGS is brilliant! Worked like a charm. One question: can I control which user sees it? I’d prefer it if only the office used it. Not essential, just cleaner. I didn’t see a place where I could set that.