Forum Discussion

KatherineOakey's avatar
KatherineOakey
Qrew Member
5 years ago

Add two tables in one form and optionally using Default record picker

I have two tables

Client, the parent
Problem, the child

What I would like to be able to do, and haven't been able to figure it out yet, is 

When a client adds their own problem via a form on the internet, they add both their client information and their problem in a single form with a single (preferred) submit.  For privacy reasons, we don't want to display any existing client information when the problem is entered via the internet (yes, we could end up with duplicates).

When an employee adds a problem on behalf of a client (via intranet), the employee can take advantage of the default record picker to select the correct client and then add the problem.

The forms will look different as different client information may be collected based on the origin of the form (ie, internet or intranet).

Can two tables be added/updated in a single form/submit?

Can the default record picker behavior be turned on/off for the parent via the form?

------------------------------
Katherine Oakey
------------------------------
App builders
Quickbase Automations
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    There are a variety of solutions.

    Here is one.  The internet users are not experienced Quick Base users so it has to be stupid simple and need to enter their client info and the problem on a single form.

    I suggest making an EOTI Client form (to be used on the Role Everyone On The Internet), where they fill out the Client information and also you will include fields for the child information, right on the Client form.  The form would not have a field for [Related Client]

    I would then set up an Automation to fire, when the "Problem Description" field on the Parent Client record was not blank which would create the Child problem record.

    Internal staff would be notified of new EOTI Client entries and look to see if the client already exists.   If the client does exists, then they would edit the child problem record and attach it to the previously existing Client record, leaving the EOTI Client record childless.  That record should be flagged as "archived" to keep it off reports and drop down choices for new problems. 

    If the client record is a bone fide new Client record, it would remain, and you might want to clear out the problem information entered on the client record or hide that section of the form based on a form rule and a checkbox.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • KatherineOakey's avatar
      KatherineOakey
      Qrew Member
      5 years ago
      Sorry for the delay in responding.

      I will have to mull this over to see if it will work for our setup.

      In the perfect world, client is 'registering' to attend a time-in-the-future event to discuss their problem.

      We would like the client to select the instance of the event they plan on attending and the question/problem they have (with optional photos).

      I'd originally planned on an event table where a date/time could be selected but now I believe I will be adding this to the client record (I think anyway).  When the client signs up along with their problem, they may want to optionally add photos.

      I started with 4 tables...

      Events
      Clients
      Problems
      Images

      The Clients morphed into two tables... Clients where the client info can accessed via the Default Record Picker when the problem comes in not from the internet and Internet-Clients where clients can't be looked up via a Record Picker.  The Internet-Client is the child of the Event (parent) - Internet-Client relationship.  A bit clunky but lets me collect also marketing stats on where the internet-client found out about the event (which we don't need to collect for clients).

      I think I may be able to get rid of the Internet-Client table (not sure yet) but I will be able to get rid of the Event table and just set up a multi-item choice to allow EOTI (new term for me :) ) clients to choose their time.  Means someone will need to periodically update the choices but I think not a big deal.

      Thanks for the suggestions and help from a vintage SQL/transactional/relational database background.  QB is sure nice but having to wrap my head around the differences between what I'm used to and how QB works :)


      ------------------------------
      Katherine Oakey
      ------------------------------