Forum Discussion

MattStephens's avatar
Qrew Cadet
10 months ago

Allow child tables to lookup parent data even when user does not have permission to view parent.

Override sub-field access

I like this field setting:

Use the access permissions above even if they are less restrictive than those of the sub-fields

Sub-fields are the fields used to calculate this field's value
However, I'm trying to achieve a similar thing between tables, not just for fields within a table.
Is there any way I can have a user view child table data that is based on lookups from a parent table,  even though the user doesn't have permission to view the parent? I would similarly like to have one for summary fields too.
Something like this imaginary field setting for a lookup field:

Use the access permissions above for this lookup field even if the user does not have permission to view the parent data within the parent table.

Here's my specific example.

I have a table of Orders and a table of Supplier Inventory. The Supplier Inventory is viewable by all, whereas I have restricted  viewer access on the Orders table to only those users that are involved in the Order (I don't want customers to ever be able to see other customers' orders). However, I do want to base the Remaining Stock in the Supplier Inventory off any new Orders since the last Supplier Inventory stocktake.

I have thought of a few ways to do this, but nothing seems pleasantly simple.

  1. Quickbase actions to update a separate Public Orders Info table.
    1. This ends up getting complicated because the actual withdrawn quantity is based on at least 5 other tables (Deletions, variations, etc.),
  2. Quickbase Pipeline to update a static Remaining Stock field based off the formulated Remaining Stock field whenever something happens in any of the other tables.
    1. Probably my best bet.
  3. Open up the Orders table to be viewable to all
    1. Too risky

I'll go ahead with Option 2 in a couple of days... but would love to know if my imaginary field setting is possible... or if there's a better way to do it.

Matt Stephens

4 Replies

  • Unfortunately, I think that you're stuck with those options. For option 3, I would say that you can limit the risk of exposing other customer's data by limiting the reports and forms that the Customer Role has access to.

    I have a many-to-many table set up that drives my user permissions. If I were to make either parent restricted, then the permissions break. So, I just hide the reports and forms from the users for those tables.

    Good luck!

    John Crosland
    • MattStephens's avatar
      Qrew Cadet
      Thanks John, I think I will go with your suggestion of limiting report and form views. Especially since the I expect the app to grow on complexity, requiring more and more automatons to keep the withdrawn order up to date. Your suggestion has saved me a few hours today, and possibly days of work in the future. 

      NOTICE: This email, if it relates to a specific contract, is sent on behalf of the Beca company which entered into the contract. Please contact the sender if you are unsure of the contracting Beca company or visit our web page for further information on the Beca Group. If this email relates to a specific contract, by responding you agree that, regardless of its terms, this email and the response by you will be a valid communication for the purposes of that contract, and may bind the parties accordingly. This e-mail together with any attachments is confidential, may be subject to legal privilege and applicable privacy laws, and may contain proprietary information, including information protected by copyright. If you are not the intended recipient, please do not copy, use or disclose this e-mail; please notify us immediately by return e-mail and then delete this e-mail.

      Sensitivity: General

      • AdamKrzyzanek's avatar
        Qrew Captain

        You wrote: I don't want customers to ever be able to see other customers' orders

        In role you can restrict it in a way to show to you customers only data associated with them. It means you need to create separate roles for them, or manage roles via "access table" but it is doable.

        I am doing it in my app, used by over 1000 people, and many external partners. Unfortunately because of this I am having 166 separate roles (some with similar settings but "filtered" to show specific customer number).

        Also you can hide table from viewing. So user can have access to table but cannot see it (User Interface --> Hide in Table Bar)


        Adam Krzyzanek
  • When you say "User," this is someone who has been given a Quickbase User account in your app (vs. the "Everyone On The Internet" Role), right? Put another way, are you able to distinguish which User is logged in?

    If you are authenticating Users, it seems like you'd create a custom Role for Customers and use the native QB Access restrict visibility as necessary. For example, a given record is viewable where the logged in User matches the User stored on the Order. Then, you'd apply this same access logic to both the parent and the child by looking up the parent's stored User on the child.

    Sometimes, when logic becomes complex or needs to be reused throughout the app, I'll create a Formula Checkbox called "Viewable" that evaluates to true under the right conditions, otherwise, it defaults to false (not viewable). Then, you could place this Formula Checkbox on the parent and pass it to the children as necessary. This keeps your "code" DRY and in sync across tables.

    Or perhaps you could leverage Formula Queries with overridden sub-field access vs. lookups (untested)? Or leverage native QB emails with a "more minimal" custom form included, then trigger the email send when an order is created or updated (instead of the User logging in)? The only other way that comes to mind at the moment would be to create a Customer Portal with further authentication and authorization logic and use the API to to pull in Order data … but, I imagine that's a heavier lift than you were hoping for!

    Brian Seymour