Context("table") using the filtered table

[Mitchos]

Ahoy,

I’ve just started using AppSheet and I’m really liking it so far. I’ve just started dabbling in the security filters for the permissions of the users (based on roles) in my app and I’ve hit a problem that is stopping my intended design.

The function context(“Table”) is dynamically switching context within the formula of my Security filter.

I have a table called let’s say “IT Help Desk”.

If I set a crude security filter up on the table such as:

CONTEXT(“Table”) = “IT Help Desk”

It will return all records because the table is “IT Help Desk”. Nice.

However, if I modify the security filter to have a test formula of:
ISNOTBLANK(
FILTER(
"Alternative Table",
CONTEXT(“Table”) = “IT Help Desk”
)
)

There will be no records returned, but we already saw previously CONTEXT(“Table”) = “IT Help Desk”, so if "Alternative Table" has records (which it does), then all records from that table should be returned and therefore not blank and all records of the “IT Help Desk” are shown based on an overall True.

But instead the same line CONTEXT(“Table”) = “IT Help Desk” is no longer True.

Regardless of where in the formula CONTEXT(“Table”) is called the table should remain the same as per the help file (which doesn’t say, “as per the table being read by the formula at that point in time”). Also, why would I want to know/retrieve that when I’ve just explicitly had to reference the table name in the formula.

• “Table - Name of the table used by the current view.”

If I do this instead (match the context table to the filter table being read):

ISNOTBLANK(
FILTER(
"Alternative Table",
CONTEXT(“Table”) = “Alternative Table”
)
)

Then CONTEXT(“Table”) = “Alternative Table” is True and all records in “IT Help Desk” are returned.

But without the preceding filter read, CONTEXT(“Table”) = “Alternative Table” is false.

I would think that this is bug along the lines of not saving the index location of the read prior to the table read within the filter and that being referenced at all time for CONTEXT(“Table”).

Love some help with this to unblock me. At this stage I’m looking to probably add a field to every table to record CONTEXT(“Table”) and then refer to that field in the Security Filter, which appears to work.

The other thing I have not yet tried and won’t apply to me (as I want a copy and past formula to apply to every table) is within the filters conditional statement is instead of directly reading the CONTEXT(“Table”), re-read the table I’m already on (to change the pointer, then doing the CONTEXT(“Table”) read at that point). But I don’t want to specify the table name in the formula otherwise I may as well write it as text instead of re-reading it, which makes CONTEXT(“Table”) redundant.

Cheers,
Mitch

[Suvrutt_Gurjar]

The CONTEXT(“Table”) returns the table used by the current view. In turn, the current view is specific to each device or user. As such I believe, the CONTEXT(“Table”) function will not work with the security filters.

Please check the posts below:

Problem/Bug with CONTEXT() - Google Cloud Community

Solved: Security filter - Google Cloud Community

[Mitchos]

Thanks for your very prompt reply.

It does work though. It just works differently if you read a table within the formula or not and you can control the response based on those reads. I just want it to work the one way all the time.
So I’m not sure the responses in those articles are entirely accurate compared to what I’ve seen and mentioned above.

Tables are server side so I would think CONTEXT(“Table”) should also be able to work in that way.

[Suvrutt_Gurjar]

The CONTEXT(“Table”) returns the table used by the current view and in turn the current view is specific to each device/user.

Please take a look at the description of CONTEXT(“Table”) in the article.

CONTEXT() - AppSheet Help

However, you have a valid point and some other member may add / guide more to your query. I am sorry that presently I have nothing more to add, as I have myself not come across the specific use case you describe- use of table name returned by CONTEXT() in security filter.

I will revert if I am able to test and find something more useful to add.

[Mitchos]

Hi @Suvrutt_Gurjar ,

I quoted that help file line in my original question.

If it always failed then I’d raise it as an enhancement. But it does work under certain conditions and only ‘fails’ (or works differently) under conditions that can be replicated and those conditions do not align to the help file statement.
I think you will find it’s all covered off in my original question.

It’s ok that you’re not able to help, but hopefully someone can, it’s just the links you have provided do not explain the issue blocking me.

Thanks

[Mitchos]

Just to help make this clearer I’ve run a test to remove the “Security Filter” component from the discussion, so as not to side-track things and better highlight this defect.

Setup:

“Table 1” has a real column called “Give Me” with a formula below.

“Table 2” has a real column called “Have it” with several values e.g. {1,2,3,4,5}.

“Table 1”, “Give Me”, Auto Compute, formula:

FILTER(
       "Table 2",
       CONTEXT("Table") = "Table 2" 
       )

Outcome:

When you view the app in the “Table 1” form or Detail view it will show up {1,2,3,4,5} in column “Give Me”.

Because of the Filter read. Which is incorrect because why would the view be “Table 2”?

[Suvrutt_Gurjar]

Thank you very much for the details of the test environment. My initial testing shows it otherwise.

if I use CONTEXT(“Table”)= “Table 1” in Y/N type column a view based on Table 2, it returns FALSE or N.

In the example, below, my expression for the [Context_Table] column is CONTEXT(“Table”)=“Orders” and the column is in view based on “Products” table. It returns FALSE as expected.

The column definition in the “Products” table

[Mitchos]

@Suvrutt_Gurjar you are nearly there replicating it all the way. However, you’ve only done the first part of what I’ve put down.

You will need to add this formula to your Products table in the Context_Table field:

FILTER(
       "Orders",
       CONTEXT("Table") = "Orders" 
       )

By your first half of the replication above, you should be expecting no orders, because as you said: CONTEXT(“Table”)=“Orders” is False (right?) and False in a filter condition should not return anything?

But it does, because the filter changes the pointer of the function.

[Suvrutt_Gurjar]

Yes, I will also test with the FILTER() condition , because by the time you had given your specific test condition with FILTER() function, I had performed my test and shared results as a starting point.

I am stepping out of work desk for some work and will revert afterwards. In the meantime, it will be great, if you also share the relevant screenshots of your tests also

@Mitchos

By your first half of the replication above, you should be expecting no orders, because as you said: CONTEXT(“Table”)=“Orders” is False (right?) and False in a filter condition should not return anything?

Correct, if I expect “Orders” records in the “Products” view, it should not return any records.

@Mitchos

But it does, because the filter changes the pointer of the function.

I do not understand this specifically. But I understand you mean Filter returns all rows from a table that is not related to the view based on another table. That needs to be tested and I will revert.

[Mitchos]

Hi @Suvrutt_Gurjar ,

My requirements and problem statement haven’t deviated, but I can see how you might have confused my use of the term “Filter” in the original post to be the “Security Filter” instead of the function “FILTER()” as I used both terms but assumed the context of the examples that accompanied the terms.

I say the following with all due respect as it looks like you dedicate a lot of your time to helping others here, but now that I have gone on to further explain the situation, you might take the time to read over the original post and then if you are still not sure about this issue let me know.

But otherwise, I’m worried all this redundant chatter is diluting the issue at hand.

I’ll try harder next time to make the problem statement more concise when posting.

I’ll also look into how I properly raise any bugs that I find.

Thanks

[Mitchos]

Additional explanation with screenshots.

I’ve set the columns up to show all the different cases at the same time to reduce the need to change the formula’s back and forth.

Here we have “Test Table” where we set up three Columns:

Column “Shouldnt_Work”:

Notice that it’s called “Shouldnt_work” because that Table context is not the table of the column, instead it’s a different table “Test Table 2”.

Column “CONTEXT_Function”

Directly reference the function from a field as a baseline:

Column “Should Work”

Same as the “Shouldnt_Work” column but this time the CONTEXT() function matches the correct “Table”.

MONEY SHOT

Going to add a record on the “Test Table” shows that despite being on the “Test Table” table and view, the FILTER() functions read of a different table has highjacked the CONTEXT results and it’s now localized to that table. “Should Work” just doesn’t show up because it doesn’t return any results.

FYI: “Test Table 2”. Which has been set up to have 5 entries of 1,2,3,4,5

Additional ramble: I can guess (appsheet is new to me) at how this happened. The process appears to be pointer based, so the code thinks it’s on the “Test Table 2” because the conditions in the filter are localized, or index reads, from the table in the first parameter of the function, i.e. you can reference column, e.g. [whatever] directly without a table specifier in the second parameter of the function, instead of what you’d do in an object orientated based language of specifying the full object.property. But that being said, the function CONTEXT() should not be influenced by the formula’s function reads and you can’t chuck a [_THISROW]. on the CONTEXT() function (I think) and nor would I if I tie it back to the Security Filter scenario causing me the grief.

[Suvrutt_Gurjar]

So , I am back with my testing.

Test 1

Test a column in Products Table (Table 2) based view by getting records from the Orders table (Table 1) by using CONTEXT( “Table”) = “Orders”

So my expression for the [Context_Table] column is

FILTER(“Orders”, CONTEXT(“Table”)=“Orders”)

The result when tested in a detail view of Products table ( Table 2) is surprisingly unexpected, at least as per the description of CONTEXT('Table") in the help article "

• Table - Name of the table used by the current view."

Or our( my) interpretation of this description is incorrect. But the results are in line with your testing and finding.

As the image shows, all the keys of the “Orders” Table ( Table 1) are returned in the view based on Products table(Table 2).

Test 2: I tested the expression of filter expression on table 1 ( Orders) in table 2’s security filter as you were testing and you were not getting any records.

Security filter for table Products is as follows

The results are in line with test 1 above and contrary to your findings. The expression does return all the rows of the Products table as the following image shows,

Overall conclusion : ( as of now, till someone guides more The CONTEXT(“Table”) does not seem to be evaluating in the context of the current view.

However your observation of the expression not working in the security filter seems to be incorrect per my testing.

Finally , I am still trying to understand your use case of having CONTEXT(“Table”) in security filter.

[Mitchos]

@Suvrutt_Gurjar , just be careful not to hijack this post. I’ve already captured the issue and provided screenshots of test cases showing the defect. You misinterpreting what I’m doing is just making more work for me and maybe adding confusion for some who could help like an AppSheet dev.

I don’t want (in your version of the test) the function to have a context of Orders (you are probably looking at the case in the original post where I was showing what was happen that was wrong).

I want to look at values in Orders, but within that function filter() Context(“Table) needs to be the original table the Security Filter.

I just need this bug fixed so I don’t have to add an extra column to every table.

[Suvrutt_Gurjar]

@Mitchos

@Suvrutt_Gurjar, just be careful not to hijack this post

I am sorry but I first time got a comment in my so many years of community contribution of such a strong wording of " hijacking" when I was just trying to conclude from my side so as not to leave a thread unfinished from my side.

@Suvrutt_Gurjar

Overall conclusion : ( as of now, till someone guides more

The CONTEXT(“Table”) does not seem to be evaluating in the context of the current view.

But I will surely not add anything further in this thread. Just for context, some of the post threads I have participated have gone for 50 posts ( and solved). But anyway best luck and thank you very much for a different perspective. I am sincerely sorry to be (overly) pitching in and trying to be helpful.

[Mitchos]

Hi @Suvrutt_Gurjar ,

I’m very sorry, I should be a more considerate community member and as stated I respect you dedicating time to helping people. Please accept my apologies if I ended up abrupt.

I was saying it was heading that way because early on you said you’d step back but then every time I started a new thread you jumped in and replied to it, but were several steps behind where I was at and my effort versus reward was starting to look like the post was not working.

You did get to a conclusion but I’d already reached that conclusion before posting, then stated it in post and then put screenshot in of the evidence at your request. But there was lots of time I had to spend for you to get to the same conclusion (the first several comments were just winding back pre-made assumptions) and I felt it was unnecessary to put your last post in when my preceding post/s already had an outcome.

Then you finished the post with adding further ambiguity by saying I was incorrect when I was not, when it’s been shown several times in this post that you’ve just misunderstood. And then that would need even further going over the same stuff.

Maybe now you have seen for yourself that there is a bug, have a reread of the whole thread and see if you can appreciate where I am coming from.

I’m under some time pressure and this bug has not helped, so I sincerely hope we can put this down to a miscommunication and that I have some learnings to take away about how to better post in this space and contribute as a considerate community member.

Thanks

[Suvrutt_Gurjar]

Points to ponder if I may add

• Posts are collaborative. Patience pays.. At times, requirement of question poster is not clear to the person responding at first go as she/he respond based on only written text. Each post in the thread increasingly contributes towards solution. Some questions have taken more than week and more than 20 posts to get a solution.

Example: this thread went on for 25 posts and also featured in Community Round up as conversation the month. There are some more that extended even longer.

AppSheet Community Roundup: May 2022

Solved: UPDATE PARENT TABLE STATUS USING RELATED TABLES - Google Cloud Community

• Almost all members (except those with Google logo in their avatar image) are contributing totally voluntarily ( including myself), just for the love of the platform and urge to help others. They also spend time- in many questions more than the person who asked.

• I have my explanation for responding the way I did in this thread. One major was your later post explaining the requirement , escaped me when I answered my findings with my latest post, as the portal does not flag arrival of new post without refresh. But I believe let us not consume community’s time in justifying ourselves.

I will request you to start a new thread for this question, if you can. It will be of course wise for me to abstain. I assure you that I will not participate in that thread for you to get a fresh perspective from someone.

Best of luck.