Security filters Children records

jujogar10
Participant IV

Does security filters applied to a parent table, afect children related records?

Solved Solved
0 37 1,186
1 ACCEPTED SOLUTION

Grandparent references arenโ€™t possible. You could consider copying the grandparent reference down to the child to make it readily available. It uses more storage in the spreadsheet, but is actually more efficient than a virtual column.

View solution in original post

37 REPLIES 37

Steve
Participant V

No

Do they work applied on virtual columns?

Off the top of my head, I donโ€™t think so. Security filters area applied server-side, but virtual columns are client-side.

Any Ideas on how to apply security filter to children records?

If the parent filter is pretty simple, e.g. ([Email] = USEREMAIL()), you can easily apply the same logic to the child using a dereference: ([Parent].[Email] = USEREMAIL()).

Actually on the parent table I have an Id that reference the useremail on another table. Can one do grandparent refference?

Grandparent references arenโ€™t possible. You could consider copying the grandparent reference down to the child to make it readily available. It uses more storage in the spreadsheet, but is actually more efficient than a virtual column.

Changing subject, does security filters afect columns formulas? I have a column formula searching on the table that I want to filter.

The security filter effectively removes rows from the table for all practical purposes, which would therefore prevent your search from seeing any of the filtered rows.

Thanks much dear @Steve for pointing me to this post. If I could ask you one more favour, would you please elaborate more on the quoted part? VCs are inefficient in what sense please?

In my App Iโ€™ve been trying to maintain the date source sheet as clean as possible (true database), using virtual columns instead for references and calculations, etc. If there are efficiency concerns, I might need to correct my course and reverse what Iโ€™ve been doing. Thanks again!

Virtual column values are recomputed at numerous times during normal app operation, most notably during a sync. These recalculations during sync can affect the amount of time the sync operation takes, and will increase in proportion to the amount of data in your tables. In cases where the recalculations are expected to always produce the same result, the time cost of recalculating those values can be recovered by performing the calculations once and storing the results. For trivial calculations, the benefit is trivial, but for more complex calculations, the benefit can be huge!

Generally speaking, my first suggestion is to identify virtual columns that use FILTER(), LOOKUP(), MAXROW(), MINROW(), and/or SELECT(). If the expressions are expected to always return the same value, they should be converted to normal (non-virtual) columns. If those computed values are used only when the user is in a form view, they are good candidates for conversion to normal columns.

Thank you @Steve, I will surely now rebuild most of my VCs into physical ones.

Just to confirm my understanding please. From the thread I understand that VCs are computed by the device but the normal columns are computed by the server, right?

Also, your last sentence about the forms, I think you meant virtual instead of normal columns, right?

Not quite.

  • When a sync occurs, the server will recompute all virtual columns and provide the new values to the device. Normal column values will not be recomputed during a sync.

  • When a row is opened in a form view, the device will recompute the virtual column values of that row, and will recompute the values of normal columns that have App formula expressions and Initial value expressions (where appropriate).

  • When an action modifies a row, the values of that rowโ€™s virtual columns and normal columns with App formula expressions or initial value expressions (where appropriate) will be recomputed. If the action is performed from the device, the device will do the computation; if the action is done from the server (e.g., by a bot), the server will do the computation.

Much appreciated @Steve. Iโ€™ve been also reading a number of old performance-related topics and came across many useful posts of yours. I do understand now what I have to do, so thank you!


Iโ€™ve discovered a nice convenience. To turn a virtual column into a normal one, if you add a normal column to the sheet with the same name in the sheet as the VC, upon regenerating the structure, the VC you have in the App is now a normal column with all its previous setting and formulas conserved.

I have a parent table with security filter applied, and it works as expected, optimizing my loading time.

The child table, with simple dereference security filter applied, takes a lot more time to load then if it would have no security filter applied.

I think this is because , somewhere on server or client side, it โ€œre-loadsโ€ and check all children against all parents.

So I think dereference security filter works something like this :
-app downloads filtered parents
-then again ,app somehow loads all parents and all children, so it can compute the VC (VC being client side,as you said)
-app checks the dereference filter for children, and sends to server info about the positive children rows
-app downloads and makes available everything acording to applied security filters

I am searching for a way to filter children (to corespond with loaded parents only) in an optimzed way, without copying a column from parent table, and I could use opinions

Itโ€™s strange , because unfiltered children loads much faster then filtered children ( altough unfiltered means about 30 times more data).

Also, for read only tables, you have to choose between security filters and delta sync, and for read only big tables , delta sync seems to be better .

My rookie conclusion is :
Dereference security filters implies virtual column, and VCs are client side only (generating loading/reloading times) , so this way is working for security reasons only, and not for optimizing loading time too.

Are child dereference security filters checked only against filtered and loaded parent rows ? How does it logically works?

Is IN() a better way to filter children?

What are the expressions involved?

A strange example would be this :

Parent table : And([data]>โ€œ10.01.2020โ€;[Status]<>โ€œridicataโ€ ; [Status]<>โ€œreceptionataโ€;[nume]=usersettings(โ€œagentโ€))

Child table : And([idx].[Status]<>โ€œridicataโ€ ; [idx].[Status]<>โ€œreceptionataโ€)

Strange because I just did a test drive, and children are not even filtered. Iโ€™m still trying to find what I did wrong

Hmmmโ€ฆ The only thing I can think is that, because the child tableโ€™s security filter uses the parent table, all rows of the parent table must complete processing by the parent security filter before any of the child table rows can even begin loading. Without the child table security filter, the two tables can be loaded at the same time, in parallel. If you have any virtual columns in either table that reference the other, I can imagine that complicating and slowing the process even further.

Iโ€™m not sure I have any advice for you here. Looks to me like itโ€™s a trade-off between speed (without the child filter) and data size (with the child filter).

I changed the child table formula, replacing left side with right side. It still doesnโ€™t filter my children , it loads everything, of course , only referencing whatโ€™s possible with filtered parent.

Iโ€™ll keep studying this behaivor and come back

And yet , in this case, the parent table is filtered completely , and loads zero rows (coincidence of current data) , but the child table is not filtered at all (it fully loads in app, i checked making a view of it).

This is why I canโ€™t explain, and I think thereโ€™s something wrong in my child formula.

I read this too : https://www.google.com/url?sa=t&source=web&rct=j&url=https://community.appsheet.com/t/security-filte...

But my child is not filtered at all, and I have no app errors

AppSheet has an odd quirk in the entirely non-intuitive way it handles an is-equal-to? (=) or is-not-equal-to? (<>) operation, which may be coming into play here.

Given an expression:

(value-1 = value-2)

or:

(value-1 <> value-2)

If value-1 is blank, the expression is TRUE regardless of the value of value-2; the two values are not even compared. If value-1 is not blank, value-1 and value-2 are actually compared.

In your case, if the filtered parent table is empty, [idx].[Status] in the child row filter expression will always be blank: [idx] refers to a non-existent row, so dereferencing it will produce a blank value. Because itโ€™s a blank value being compared with <>, the result is always TRUE. Hence, the child filter condition is always TRUE for all child rows.

The above quirky behavior only affects = and <>.

To work around the quirky behavior, try to construct your = and <> comparisons so that the left-side argument is never blank:

And([data]>โ€œ10.01.2020โ€;โ€œridicataโ€<>[Status] ; โ€œreceptionataโ€<>[Status];[nume]=usersettings(โ€œagentโ€))
And(โ€œridicataโ€<>[idx].[Status] ; [idx].โ€œreceptionataโ€<>[Status])

Or explicitly check for a blank value:

And([data]>โ€œ10.01.2020โ€; isnotblank([Status]) ; [Status]<>โ€œridicataโ€ ; [Status]<>โ€œreceptionataโ€;[nume]=usersettings(โ€œagentโ€))
And(isnotblank([idx].Status]) ; [idx].[Status]<>โ€œridicataโ€ ; [idx].[Status]<>โ€œreceptionataโ€)

The quirky behavior does not affect IN(). In your case, IN() would both fix the problem and be (ever so slightly) more efficient:

And([data]>โ€œ10.01.2020โ€;not(in([Status], {โ€œridicataโ€, โ€œreceptionataโ€}));[nume]=usersettings(โ€œagentโ€))
not(in([idx].[Status], {โ€œridicataโ€, โ€œreceptionataโ€}))

See also



Thank you. Interisting stuff.

So the quirky behavior happens only when left side is blank, and doenโ€™t hapoen if the right side is blank ?

Also, if you know , are child dereference security filters supposed to be checked only against filtered and loaded parent rows ?

Correct.

My experience is that theyโ€™re checked against the loaded-and-filtered parent rows.

Thank you.

Another conclusion could be that autoreference VCs are processed server side, while creator VCs are processed client side.

Also, now Iโ€™m thinking to make an experiment, to filter parrent based on child and child based on parent, at the same time

not(in([idx].[Status], {โ€œridicataโ€, โ€œreceptionataโ€}))

And(โ€œridicataโ€<>[idx].[Status] ; โ€œreceptionataโ€<>[idx].[Status])

Both formulas donโ€™t filter the child table, it loads just like in the previous picture .

Parent is filtered as expected, I checked it :

My current conclusion is that child table doesnโ€™t wait for parrent to be filtered first, this being different from conclusions Iโ€™ve read on other posts

Iโ€™m afraid Iโ€™m at a loss, here. Iโ€™m going to have to suggest you engage support@appsheet.com for further help.

Iโ€™m not giving up yet.

How can I optimally check if an [idx] from child exists in parent column [idx] ?

Try:

IN([idx], parent-table[key-column])

or:

ISNOTBLANK([idx].[_ROWNUMBER])

Thank you guys, I have sent the ticket to support.

But I keep experimenting

Strange, this seems to work, it filters properly my child table

Now Iโ€™m confused

So let me clear up some of the potential confusion. @Govardhan_Mucharla FYI

The most direct way to check if a Ref column (in this case, [idx]) is present in another table is the way Steve didโ€ฆ IN([idx], parent-table[key-column]). As you observed, that works correctly in the security filter. To avoid misunderstandings, all security filters are applied in our cloud backend before any data is sent to the browser/mobile device. Further, if TableA has a security filter, and TableB has a security filter that uses the contents of TableA, then the platform handles the ordering of all of this โ€” only the filtered contents of TableA are used to filter TableB.

In your case, you were using filters along the lines of [idx].[status] <> โ€œvalueโ€. But of course, if [idx].[status] is null because the corresponding row does not exist (it was filtered out), then null <> โ€œvalueโ€ will be true. This is why Steve was suggesting you also check ISNOTBLANK([idx].[status])

I hope this demystifies everything.

Thank you. I understand a lot more stuff now.

One unclear thing, I also tried โ€œvalue2โ€=[idx].[status] ( โ€œpregatitaโ€= [idx].[status] ) , with same result

Yes, unfortunately, that is where the discussion of null values comes in (Steve described that).

Try
AND( ISNOTBLANK([idx].[status]), โ€œpregattitaโ€ = [idx].[status] )

Youโ€™ll see that this works. Weโ€™re hoping to resolve this odd behavior of null values soon.

Support has received at least one other complaint about this behavior. I strongly encourage you to engage them if you canโ€™t get it to work, to give weight to the problem and drive deeper investigation.

@OptimiX_XcrY yes please send this one in to support. That way (once you share the details in the support ticket) we can get the permission to look at your app and figure out what is going on.

For others like me :

My app, that was referenced in this thread , was basically a ticket order app (parent table), with child table for details.

A ticket had a blank [status] at the beginning, some intermediate statuses , and two types of finished statuses.

My security filter for tickets (parent table) , was set to filter out only the two types of finished statuses (so it only loaded intermediate statuses and BLANK โ€œuntreatedโ€ statuses).

When using same logic filter for child table, with dereference to filtered parent table , the fun began :

  • the filtered out rows from parent were considered blanks , and child filter was true for all classic โ€œ=โ€ and โ€œ<>โ€ with [idx].[status] , no matter if the null was in left or right side of the in/equaliti signs . So the child table was not filtered at all

  • if if the child filter would have worked at first, the problem would have appeared when finding real blank cell statuses, that i wanted to keep (this is why I avoided isnotblank() , because I wanted the blank โ€œuntreatedโ€ tickets to pass the filter)

  • i rewired my app a bit, replacing blank statuses with something not blank, as initial value, like โ€œuntreatedโ€

  • i used And(isnotblank([idx].[status]);โ€œridicataโ€<>[idx].[Status] ; โ€œreceptionataโ€<>[idx].[Status]) to filter child table , and everything works perfect now

With all the help and ideas Iโ€™ve got from everybody , I finally understand the logic and order in security filters dereferencing to another filtered table :

  • avoid blank cells in filters, avoid null values from dereferencing , avoid null values in mathemathical expressions involving โ€œ=โ€ or โ€œ<>โ€ , avoid blanks/nulls everywhere, and treat their smallest possibility with isblank() and isnotblank()

  • filtering both parent and child dereferencing parent , happens on server side in the right order (parent , then child)

  • I think this way helps apps with large tables , because repeated columns in parent and child tables can be avoided, increasing performance and reducing size too. Probably for grandchild tables too.

Thank you guys.

Now Iโ€™m gonna check all my apps and child tables, I will sure discover similar situations of unpropper filtering.

Maybe we can improve that order & order details sample app, with a simple security filter for parent and child dereferencing parent at the same time.

Nice job!

Actually , for all my children tables , Isnotblank([idx].[status]) is enough of a filter, and it corresponds 100% with filtered parent rows. The complicated AND() formula from above is not even necesarry

Top Labels in this Space