Discussions

 View Only
Expand all | Collapse all

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

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

    Posted 01-11-2019 06:00
    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!


  • 2.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 07:57
    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?


  • 3.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 13:08
    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.


  • 4.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 16:50

    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?


  • 5.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 17:00
    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. 


  • 6.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 17:04

    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!


  • 7.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 17:06
    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.


  • 8.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 17:28

    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?


  • 9.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 17:32
    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. 


  • 10.  RE: A lookup field that contains a number formula displays properly on the form except for 'viewer' role

    Posted 01-11-2019 17:37
    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.