A lookup field that contains a number formula displays properly on the form except for 'viewer' role

  • 1
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I have two tables, Registration (child) containing student information, and Classes (parent) containing class information. The idea is to allow Everyone on the Internet to fill out the form to register for a class.

On the registration form is a dropdown box to select a class, which then displays lookup fields with data related to the class (date, location, etc, and Spaces_Remaining.) The Spaces_Remaining is calculated by taking a summary field from the Class Table grouped per class (Total_Enrolled) subtracted from a number field on the Class table that is the maximum number of allowed students (Max_Students).

All of the information displays perfectly, including Spaces_Remaining for the user roles of administrator and participant, and it updates properly upon changing the selection of the desired class in the dropdown. However, when I go to the form without being logged in to test it as a 'viewer', the spaces remaining field displays only the maximum number of students.

For some reason instead of calculating the formula, which is (Max_Students - Enrolled_Students), it only displays Max_Students. If I change the formula to be just ([Enrolled_Students]), it again displays properly for admins and participant, but for a viewer it shows 0. I've checked and re-checked permissions, and the viewer is given permission "view" to all of the relevant fields. The other lookup fields work perfectly for the viewer, but only this number formula field that is accessed via lookup field on the child table isn't working, and isn't working only for the viewer role.

Is there a buried permission setting I could be missing here, or am I missing something else? It's 99.5% working, so I think the setup is correct, but that lookup field just isn't calculating on the internet-facing version of the form, and am out of ideas, so any tips are greatly appreciated!
Photo of Steve

Steve

  • 212 Points 100 badge 2x thumb
  • brain scrambled

Posted 4 months ago

  • 1
  • 1
Photo of Steve

Steve

  • 212 Points 100 badge 2x thumb
As an update, I believe I figured out the problem. In the App Settings > Roles > Viewer screen, I had to set Table access for the student information table under the View column to All Records. The viewer role was set to view none, because I didn't want everyone to be able to see all the other students. I believe because of the restriction, when the summary field on the class table of number of students would always read 0, and because of that the available spots formula column (Max_Students - Enrolled_Students)would be calculating (Max_Students]-0) and always displaying the maximum capacity of the class.

So now it works, and I've removed the link to the Registration table from the form so it cannot be clicked to see every registered student and their information, but should I be worried that because the viewer has the permission to view everybody that somehow they will be able to?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
One technique is to have a form used for everyone on the internet that simply says

Thank you for your registration. Blah blah blah we will be in contact shorts.

But with no actual personal info.

Set that to be the form to use for Viewing, when in the role viewer.
Photo of Steve

Steve

  • 212 Points 100 badge 2x thumb

Thanks for the tip, I think that'll work out just fine. Are there any backdoors to the data via typing anything directly into the address bar that I should be worried about?

It seems that the URLs used for accessing reports, forms, etc. consist of strings that wouldn't easily be able to be guessed by someone manually typing in the URL in order to access the data in the table without a link - is this correct?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Correct.  It's security by obscurity, but not real security.  But a user with a lot of curiosity or some familiarity with QuickBase could guess their way into a modified URL to change the form. 
Photo of Steve

Steve

  • 212 Points 100 badge 2x thumb

Got it. I'll have to mention that to our office folks; the information isn't too sensitive, but ideally it would be secure. Thanks again for your help with this!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
There are other approaches if the data is more sensitive, but in your case it sounds like this is "good enough". The other approaches take more time to set up.
Photo of Steve

Steve

  • 212 Points 100 badge 2x thumb

That's great to hear - As of now I'll see what the consensus here is about it, but if that became necessary, do you mind pointing me in the direction of where to look to be able to do that?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
You can contact me directly at QuickBaseCoach.com  for one on one assistance, but one approach is to have a separate app  and as the applications come in, you have a process either manually or via an Automation to copy them across to the real table an delete them from the everyone on the internet table. 
Photo of Steve

Steve

  • 212 Points 100 badge 2x thumb
Ah, okay, that makes sense. Thanks again for all the helpful info. If that becomes the route that we decide to go down and run into trouble I'll absolutely get in touch.