Security Filter (Reference Tables) Help

Rurrea · 07-14-2021 01:22 PM

Ok. So I’m still having trouble writing this security filter for my tables.
**Order Table (Key Column: [Order ID]

Reference Table:
- Delivery Table [Order ID]
- Order Detail Table [Order ID] [Product ID]
— Product Table [Product ID]
- Render Payment Table [Order ID]

Separate [AOD Table] which has all users info:

Salesperson ID Column, Email Column

All tables except [Product Table] have the [Order ID], Product ID is the connection for that sub table.

Need help writing the security filters to only pull the reference tables needed that apply to unfiltered Orders in [Order Table].

SalesTeam App
My current security filter in my [Orders Table]

AND(
  ([Order Date] >= (TODAY() - 10)),
  ISNOTBLANK(
    INTERSECT(
      LIST(
        [Salesperson],
        [SplitSale]
      ),
      SELECT(
        AOD[Salesperson ID],
        (USEREMAIL() = [Email])
      )
    )
  )
)

So the question is, how do I only have it pull the reference tables needed for each user with Orders they have access. And allow Orders in open status to always show all references applied, but all other statuses will be filtered out as shown above. I’m assuming I would need to modify my current [Order Table] security filter too

Steve

This doesn’t make sense.

What do you mean by “unfiltered”?

Is Order Table a column name, as suggested by the square brackets?

Rurrea

Orders : main table Key Column [Order ID]
All other tables used are connected to Orders.

Column [Order ID] is what I use to keep all the other tables connected.
Delivery Table, Order Details Table, Rendered Payment Table, Order Notes Table, and a few more

I have my security filter on my Orders Table pulling only orders each user needs to see. With the following security filter below.

AND(
  ([Order Date] >= (TODAY() - 10)),
  ISNOTBLANK(
    INTERSECT(
      LIST(
        [Salesperson],
        [SplitSale]
      ),
      SELECT(
        AOD[Salesperson ID],
        (USEREMAIL() = [Email])
      )
    )
  )
)

How can I write a security filter for the other tables to only pull data needed that are applied to that specific users access as shown above.

Steve

For the Delivery, Order Detail, and Render Payment tables:

IN([Order ID], Orders[Order ID])

For the Product table:

IN([Product ID], Order Detail[Product ID])

Does the SplitSale column of the Order table contain only a single Salespersion ID, or is it a list?

Rurrea

Awesome. Thank You.
For key columns in tables. Are filtered rows taken into consideration still to avoid duplicates, for instance in my Order Details Table. With other security filters used in the past, product IDs from my product table have been added when that particular product ID was already attached to a row in Order Detail Table.

How can I avoid that.

The [Product ID] is the key column in both tables below:
Order Details
Product Table

Thanks

Steve

Excellent question! No, they are not. When a security filter filters-out a row, the row will not exist for that user’s device. If the user will have the opportunity to add rows to a table with a security filter, it will be possible for the user to add a duplicate or even replace (overwrite) a filtered-out row. You have to take this into account in your app design.

In a situation like this, a security filter may not be what you need. Instead, a slice may be sufficient, as it can hide select data from the user, while allowing the data to remain available to the app, allowing the app to detect and prevent duplicates, or instance. Slices are not as secure as security filters, though: the data does reach the user’s device, and a very AppSheet-savvy user might be able to get to the hidden data. With a security filter, there’s no way to get to the filtered data, period.

Rurrea

I considered the slicing, but my biggest concern was improving the syncing performance. From my understanding, slicing will not help correct with syncing?

Steve

Correct, slices will not improve sync times.

Are sync times currently a problem? Or are you just trying to plan ahead?

Rurrea

Sync time is a major issue to the point where so much data has been collected from all locations over the past few years that I now need an idea to resolve this or look for an alternative option.

This solution can work for our company drivers who only need to view data, no adding assigned to their apps for any tables.

However app used for management and for sale reps, this will remain an issue. Someone recommended horizontal scaling and partitioning but I have been unable to figure out how to do it even with the link referral from AppSheet on that topic.

Any ideas or help on scaling would be awesome.

Steve

Please clarify how Product ID is used as/in the key for the Order Detail table. A screenshot of the column list from Data >> Columns in the app editor showing the key column (and the Product ID column?) for the Order Detail table might be helpful.

Rurrea

Order Details Column.pdf (87.9 KB)

Steve

Why is Product ID the key column for the Order Detail table? I would expect Order Detail ID to be the key.

Rurrea

Original I chose to make that my key column because key columns do not allow duplicates from my understanding. The order detail ID is generated by computed formula uniqueid()

What do you suggest I change to help reach my objective

Steve

True, making a column the key column prevents duplicates from occurring in that column–but that’s not what you want. The same Product ID may legitimately occur in different orders. Order Detail ID should be the key column for the Order Details table.

Rurrea

Rurrea

In Product table : we scan in barcodes

in Order Details Table: its used as a reference to add [Product Id] that have both:

Available status in Product table [Status] being empty
[Product ID] is not yet included in any row of order details table. Another words not part of any Order yet.

For [Order ID] : I have that being generated in Orders Tables with a computed formula = UNIQUEID()

Steve

In the Order Details table, is Product ID the only key column, or is it a part of( a computed key column?

Rurrea

Here, I need a modification I’m assuming to also include all Product rows with a empty value in [Status] which in our case means it’s available to view for reps and to be able to add to future Order Details Table on a Order.

Not sure if my explanation makes sense thou.