I’m new to Looker and I have set up a simple form for staff to use regarding a grading system for students.
Parents are given a unique code and entering their code will provide a brief report based on the field data entered by staff. I have set up a text default value (Enter Code Here) when the Looker form is opened and no data can be seen.The issue is when “Enter Code Here” text is deleted and the enter key is accidentally or deliberately hit by a parent, all results can be seen. Filtering using Null does not seem to work. I have used Gemini AI to assist but no luck. I have tried a variety of filters recommended including ways to hide a null entry but I seem to go around in circles. The filters I have tried either allow everything or lock everything so when an actual code is entered, no data is shown. Thank you for your help.
Not sure i’m fully understanding what is going on, but are you trying to make sure Explore results are filtered in a specific way based on the person running it? Since you said parents have unique codes, maybe apply those codes as user attributes and apply the filtering via LookML instead of through user input in the form.
Yes, it sounds like you need row-level-security to ensure that users can only see the records that relate to their own parent account, and cannot see reports that relate to other parents.
In Looker, this is acheived by adding an access_filter paramater to the Explore definition. The access filter basically restricts access to the named field, so that people can only see a subset of the data for that field i.e the rows they have been granted permission to view.
This works in conjunction with user attributes. You will need a Looker Admin to set up a user attribute for you, so that you can reference it in your access filter. If you only have a few codes, the Admin can manually set the allowed values for each user (e.g. roberta.smith@gmail.com > 235643). If you have hundreds of codes, you will probably need to create some sort of mapping table in your database in order to scale this.
Or, even better, you could map users to a parent code via your identity provider. In your identity provider (e.g. Google Workspace), create a custom attribute for users e.g. parent_code, and assign the appropriate value to each user. You can then use this in your access filter.
You can also apply report filters using user attributes, but note that this does NOT secure the data. A user could still see another user’s records e.g. if they manually edited the parameterised URL, or changed the filter operator.
