Data in data source, but not showing in app

[Kwan]

A new row of data was entered into our app. The data was recorded in the data source and no error data entry errors where present in the logs, but we are now unable to see the record in our app. We have since entered new rows of data and they all show up in both the data source and app.

[Aleksi]

Did you add that record with the app or directly into your data source?

[Kwan]

With the app.

[Aleksi]

And you don’t see it with the same app?

[Kwan]

correct

[Aleksi]

Any chance that two users have used the app at the same time and they both have filled the same key column’s value?

[Kwan]

I don’t see any key duplicates.

[Steve]

How are you key values chosen?

[Kwan]

Key values are generated by initial value = uniqueID().

[Aleksi]

Are you able to take a printscreen from your data source where we can see that column and record? What are you using as a data source?

[Kwan]

Data source is Microsoft Excel file that is located on a SharePoint site. We have re-entered the exact same data a 2nd time and it displays correctly. The first entry is in the data source, but still not displaying in the app. Column A is the Claim Number which is the key for the Claim table. Row is highlighted.

[Aleksi]

Have you checked that you don’t have exact same value twice in column A?

[Kwan]

Yes. I do not.

[Aleksi]

Then the reason should be in the app. Do you have any slice or security filter that would filter that row away?

[Kwan]

Yes. We use security filters. But, we re-entered the same data and it shows the second time in the app, but not the first. Doesn’t make sense.

[Aleksi]

Is it okay if I check your app directly?

[Kwan]

Yes

[Aleksi]

For that, I would need to know your app name and account ID number. Would you please go to My Account > Settings and check the box next to “Allow AppSheet staff to access all of my apps…” and click Save? That will allow us to take a look at the app. Thanks

[Kwan]

Thanks. Can you see the info you need in the print screen below. The app is named Gemstone.

[Aleksi]

Have you tried to remove the security filter?

[Kwan]

I tried and that was the issue. It still doesn’t make sense though, because other Claim.Program = “Atlas” records are visible to us. Just not this one. Below is the security filter logic for the Claim table. This was put in place because we want our partners to only have inquiry access to their claim data and not each others. Any “Rockingham” employees can see all claims.

if(OR(contains(useremail(),ses),contains(useremail(),alliant)),IN([Claim Number],SELECT(Policy[Claim Number],[Program]=“SES”)),if(OR(contains(useremail(),atlas),contains(useremail(),titan)),IN([Claim Number],SELECT(Policy[Claim Number],[Program]=“Atlas”)),if(contains(useremail(),ethos),IN([Claim Number],SELECT(Policy[Claim Number],[Program]=“Ethos”)),IN([Claim Number],SELECT(Policy[Claim Number],OR([Program]=“SES”,[Program]=“Atlas”,[Program]=“Ethos”,ISBLANK([Program])))))))

[Aleksi]

So… when you didn’t have any sercurity filter, the result was the same?

[Steve]

There is a quirk with the is-equal operator (=) and is-not-equal operator (<>) that may be affecting your expression’s result: if the left-side operand is blank, the expression will be TRUE regardless of the right-side operator.

In the else clause of the last IF() expression, you have this:

IN(
  [Claim Number],
  SELECT(
    Policy[Claim Number],
    OR(
      ([Program] = “SES”),
      ([Program] = “Atlas”),
      ([Program] = “Ethos”),
      ISBLANK([Program])
    )
  )
)

You explicitly acknowledge that [Program] may be a blank value. You compare [Program] to other values throughout your filter expression. For instance:

SELECT(
  Policy[Claim Number],
  ([Program] = “SES”)
)

I encourage you to consider what might be included if the result of that SELECT() also includes Claim Number values from rows with a blank Program column value.

[Kwan]

The issue was that the corresponding Policy table row did not exist. There was some sort of error where the Copy to Policy action did not execute and thus didn’t copy values from the Claim table to the Policy table. Is there a way to rewrite the security statement to avoid the claim row from not being visible in these rare instances?

[Aleksi]

Yes your data in those two records are the same but the Claim number is different and you don’t have “e06d58c6” in your Policy table and that’s why it doesn’t show that record. If you temporary remove the security filter away, I believe you should see that record as well.

[Kwan]

You are correct. I see the issue now. I have an action on the Claim table that auto writes a row to the Policy table and inserts the Policy.Program field. That action did not execute. There must have been an error of some sort when the user added the new claim to the Claim table. Thanks for your help!

[Aleksi]

You’re welcome