Discussions

Expand all | Collapse all

One form to create records in multiple tables at once?

  • 1.  One form to create records in multiple tables at once?

    Posted 09-08-2020 16:54
    I feel like this is a dead-end, but I have a request to create a form for our office assistants to gather information from customers at one time, rather than multiple screens, etc. Here's a rundown of what we do:

    Table - Customers
    1. Create new customer (name, #, email, address, etc.)
    Table - Opportunity
    2. Create new opportunity under that customer's name (Customer < Opportunities)
    Table - Activities
    3. Create new activity to meet with customer at set date / time. Associated to both the customer and the opportunity
    (Customer <activities, Opportunity < activities)

    We use these tables daily, but the person taking the call has to access all 3 tables and create all 3 records while they're on the call with the customer. Typically this is from the customer page, as I have the "add opportunity" and "add activity" buttons there. However, a branch manager asked me if there was a way I could create a form that would perform all 3 of these functions at once, in 1 screen, without all the clicking and loading. I don't think I can do that, based on the searching I've done on the forums here, but I wanted to throw it out there and see what comes of it. Hopefully someone has an answer.

    ------------------------------
    Thanks,
    Chris Newsome
    ------------------------------


  • 2.  RE: One form to create records in multiple tables at once?

    Posted 09-08-2020 17:07
    Have you considered marking the embedded reports for Opportunities s Editable on Form Properties so that the Opportunities can be entered in Grid Edit mode? That would cut down on one screen load.  You would still need to do the Activity separately.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: One form to create records in multiple tables at once?

    Posted 09-09-2020 08:35
    I did consider that idea, roughly. It doesn't fully solve the problem as it's still a save, add new activity. Their main goal is to cut down on the time and clicking.

    I'm considering trying an automation or pipeline (haven't built one of those yet) that would take some fields on the customer form and auto create these records after the fact. Do you think that would work?

    ------------------------------
    Thanks,
    Chris Newsome
    ------------------------------



  • 4.  RE: One form to create records in multiple tables at once?

    Posted 09-09-2020 11:13
    OK, if you are willing do some playing around, you can try this approach.

    Make fields on the Customer record to capture the data for the Opportunity record and the Activity record.  Obviously, you can easily have an Automation to create those two records but the challenge is how to connect them properly in terms of populating on the Opportunity the Related Customer, and then on the Activity, the Related Customer and the related Opportunity.
     

    Populating the Related Customer is easy because this is from the Trigger record.  So we are now good for 2 of the 3 relationships.

    The challenge is to connect the Activity to the Opportunity.

    One way is to make a helper table with 1 record in it to summarize up the Maximum Record ID# of the Opportunity records, ie the most recently created record.

    Then look that up down to all Activity records, and create an Automation that says when an Activity is created and the Related Opportunity is blank, then change the Related Opportunity to the value of that lookup of the most recently Opportunity.

     


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------