Using a filter with on activate sheet trigger

[zagzebski]

I am trying to filter some data based on the activation of a sheet (using a trigger)

Right now I have a bunch of expressions that look similar to this:

Sum({$<MajorCategory={'Operating Expense'},FilterUser={'$(=OSUser())'}>} Net)

I want to get able to take this section: FilterUser={'$(=OSUser())'} of the expression and use it to filter the data upon activation of the sheet. Is this possible? This part of the set analysis filters on the user's data so I have to put it into every expression if I don't figure out a way to get it at the sheet level. (Note: I realize this is like section access but for different reasons section access is not the best route)

Thanks in advance.

[israrkhan]

you can create a variable , and assign the user name to that variable,

then in the script load, you can write condition on your column, like

where columnname = $(variable)..

[zagzebski]

Thanks for the response!

Unfortunately I don't think that will work. Some of the tabs are filtered (like in my example). However some of the tabs should not be filtered by user. If I put where clause in script then it would eliminate needed data for some of the tabs.

[#4]

Did you try using a "select in field" trigger ?

You can set the Field as FilterUser and in search string you provide the osuser().

[zagzebski]

Yes - I want to use a trigger but it is a little more complicated than your suggestion. Here is the structure

FilterUser = STEPP

OSUser() = STEPP

DEPARTMENT: IT, SALE, FINANCE

DEPARTMENTs specifically defined in the data that point to STEPP = IT and SALES

I would like to use the "select in field" so that the field is DEPARTMENT. However I don't know how to write the search string. In the search string how do I create the relationship between FilterUser=OSUser()and bring back only possible DEPARTMENTS (IT and Sales).

[felipe_dutra]

Look at the example!

[zagzebski]

Thanks for the response.

Not sure I understand the example.

Where does it evaluate (compare) the OSUser to the FilterUser so you know who is currently logged on?

Also - Not sure I am follow your "select in field" expression.

[zagzebski]

Somehow the trigger has to use the OSUser function to compare to the FilterUser field. It can't be a manual process to select a FilterUser.

[felipe_dutra · Accepted solution]

look this:

[felipe_dutra]

You need to change the script, run, save, close and open the application again.