Security filter to find a 'search' record or use the 'default' record doesn't work

[Harriswe]

Hi, I am using a security filter to look for two records on a search table, which contains search criteria. This is for a public available app, with no-login.

1. One record is based on a ‘session-id’ based on date (yyyymmdd) and the device UUID

2. The other is a ‘default’ record, which is largely blank with a session-id of “0000000000”

The idea being that if an anonymous user has used the app and the search capability today, then the app should ‘remember’ the search criteria they have entered. If they haven’t used it today, then they are presented with the default blank record.

This is required to ensure multiple anonymous users do not accidentally share the same search record.

The following, can return any of the two records in any order and doesn’t favour one over the other. I have tried MAXROW, but that generated an error.

OR(
[SessionID],CONCATENATE(TEXT(TODAY(), “yyyymmdd”), “-”, CONTEXT(“Device”)),
[SessionID] = “0000000000”
)

I have tried an IF statement but that generated an error too. Currently I am trying the following in an attempt to ensure if a record exists for TODAY for the device being used then that search record is presented.

IN([SessionID],split(CONCATENATE(TEXT(TODAY(), “yyyymmdd”), “-”, CONTEXT(“Device”)),“0000000000”, " , "))

Q: Is there a way to check for the presence of a record and use that if exists or ensure the order of results returned, favours the ‘device’ record not the default record?

[Steve]

Not with the security filter (to my knowledge), but you can layer a slice on top of the table to do it.

[Harriswe]

Hi Steve,

Thank you for your response. Unfortunately I was reaching that conclusion too. I tried creating a slice, but again it doesn’t appear to be working. Below is the filter for the slice created called Search default. I think the ISBLANK may be failing, as when there isn’t a record for this device for today, it should display the default blank one and doesn’t.

IF(
ISBLANK(
SELECT(Search[SearchUUID], [SessionID]= CONCATENATE(TEXT(TODAY(), “yyyymmdd”), “-”, CONTEXT(“Device”)))),
[SessionID] = “0000000000”,
[SessionID] = CONCATENATE(TEXT(TODAY(), “yyyymmdd”), “-”, CONTEXT(“Device”)),
)

See screen shot.

Unless you where suggesting using two slices, one for the default record and the other for the session record based on device and date?

[Bahbus]

This would be easier for you to do if you were to first have a separate Date and DeviceID column that you then combined into your SessionID key.

With that in place, you can easily set a security filter of Context("Device")=[DeviceID] to not load anything that doesn’t belong to that user. Then a slice on top of that can check TODAY()=[Date].

This also helps prevent unnecessarily complex expressions trying to spilt that key.

[Harriswe]

For sure Bahbus, that is a good idea to keep them as separate fields.

The first time an Anonymous user uses the Search product function there is no record present in the DB for the device and date (today). Rather than click the PLUS button to add a record, which would present a form, I instead wanted to present a ‘detail’ view with quick-edit fields displayed, which negates this whole step.

It also provides the ability to show results in real-time, rather than save the search record first and then wait until the detail view is displayed, showing the results.

So the issue is: to show a ‘blank’ pre-existing record if this is the 1st time the search functionality is used (today) by this user, or else show the current search record created for today.

[Bahbus]

For this to work, you need to have a default record already created for you to display.

Or if you have a blank view, create a new action that, when clicked, creates a brand new search record for them without the form view.

[Harriswe]

Hi Bahbus,

Correct, I do have a default record created to display. As soon as the default record is edited an action assigns a new sessionId based on today’s date and the device and then re-creates a new default record, ready for the next user.

[Harriswe]

Tried with two slices, one for the ‘default’ record and the other for an ‘existing record created today’, but over complicates the forms and looks cumbersome in the app, switching between views.

Just to add: if there was a way to ‘order’ the data being filtered from the table (security filter) then this would ensure the correct record was shown.

[Steve · Accepted solution]

The table security filter:

IN(
  [SessionID],
  LIST(
    "0000000000",
    CONCATENATE(
      TEXT(TODAY() "yyyymmdd"),
      "-",
      CONTEXT("device")
    )
)

The slice row filter (replace table with the name of the table above):

(
  [_THISROW]
  = ANY(
    ORDERBY(
      FILTER("table", TRUE),
      ("0000000000" = [SessionID])
    )
  )
)

[Harriswe]

Hi Steve,

The simpler appears to work fine.

OR(
[SessionID] = CONCATENATE(TEXT(TODAY(), “yyyymmdd”), “-”, CONTEXT(“Device”)),
[SessionID] =“0000000000”
)

The trick was in sorting. Although I just sorted the view. Your approach probably more robust as someone could forget why it is sorted by SessionID.

Many thanks