Security Filter : Parent Table “Orders Table” to only show if reference table “deliveries” has today date under “Delivery Date” Column

Security Filter : Parent Table “Orders Table” to only show if reference table “deliveries” has todays date under “Delivery Date” Column

Having difficulty writing this expression. People suggest horizontal scaling or dereference expression but I am unsure & unable to figure this out.

Need some help

Security Filter may not be the best approach for filtering the Orders as you describe. My thought is you might have other Orders that need to be reviewed or edited but are scheduled for delivery in the future.

Instead,a Data Slice named “Todays Deliveries” (or some such name) might be more appropriate. Then you can create a view using that slice which will only show Orders scheduled for delivery today - but as another view in the app as opposed to the app showing ONLY those orders to be delivered today.

Whichever approach you use, the expression would be the same. I’ll assume you have a column in the Order table named as “Delivery” that references the Deliveries table.

[Delivery].[Delivery Date] = TODAY()

Yep! That should be all you need unless of course you truly have other criteria to consider like cancelled orders that were scheduled for delivery today.

Now, to explain dereference. The period in the expression is the dereference operator. It can only be used on columns that are defined as Ref or have a sub-type of Ref (like in Enum or EnumList columns). With the dereference operator you can specify any column in the referenced table in order to access that column value. And you can chain dereferences such that you have [Ref Column].[Another Ref Column].[Still Another Ref Column].[Column for Value].

For more info on dereferences, check out the article below:

1 Like

Yes. I have orders that may need to be reviewed as far back as a year. But looking for a way to reduce data being synced, because data tables have become to large.
We have :
Orders Table (Parent)

  • Below all ref. tables to Orders
    Order Details
    Product Table
    Delivery Table
    Furniture Table
    Rendered Payment Table
    Warranty Table
    Invoice Notes

Some of these above have references themselves

Over the past two years, 4 of our main tables above have 35k plus rows.

We really need to figure out a way to reduce sync time and avoid random crashes/ resets

Then Security Filters would be a great way to eliminate the old seldom used records and keep the app performant. So it sound like you need both 1) a Security Filter to remove the old seldom used rows and 2) a Slice to pick out Today’s Deliveries.

2 Likes

What expression within the scaling method could I use in a situation like below:
Order Table “Parent”

Delivery Table (Reference)

  • within delivery table, specific columns of delivery date and driver. Filter out all Orders without a reference that does not have a delivery today of today.

Not sure if that makes sense how I tried explaining it