Discussions

 View Only
Expand all | Collapse all

Automatically create a parent record when info saved in the child reference field

Alex Certification!

Alex Certification!07-02-2019 22:50

  • 1.  Automatically create a parent record when info saved in the child reference field

    Posted 01-10-2019 12:38
    Hi, I currently have two tables in a parent-child relationship: effectively I've got 100 parcels in the child table and each parcel is kept in one of ten boxes, which are kept in the parent table. Rather than using the RecordID as the key field of the parent table I'm using a text field, so each box can be individually named.

    Normally when I'm importing a set of child records into a relationship like this I'll first create boxes 1-10 in the parent table, then import the list into the child table, but is this first step necessary? Is there any way that Quick Base can read the contents of the reference field in a child record and, if that information doesn't match an existing parent record, automatically create a corresponding record in the parent table?


  • 2.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-02-2019 21:54
    Yes, this can be done using automations. I just learn this from Harrison today.
    You can create a lookup field  (named: has-parent) on the child record to the parent's formula checkbox field that has the formula value of true. This lookup will be true if the parent exists false otherwise. Then in the automation when a child record is modified/added and its lookup has-parent field is unchecked then create a new parent record using the child content for the reference field value.


  • 3.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-02-2019 22:50
    That's a sweet trick.


  • 4.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 17:04
    I need to do this as well. But my table relation is such that each parent can have multiple children. So when multiple children are created belonging to the same parent, the Quickbase action is creating multiple parents. Is there a way for it to ascertain that it is the same parent and not create duplicates? QB support has said this is not possible and hoping you have a workaround.

    Thanks in advance!!

    ------------------------------
    Deepa Deepa
    ------------------------------



  • 5.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 17:24
    Are your children being created via an import or just one by one.

    If being created one by one, why will the above solution not work for you.  Does the child record know the correct value for the Parent's Key field?  What is your definition of a duplicate parent?

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



  • 6.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 17:35
    Hi Mark!

    Data is being imported. The the key fields for the parent are included in the child import and the QBaction triggers the creation of the parent using those key data fields. Then another QBaction on the parent table triggers linking that parent to the child using a report link field (to link the newly created record ID of parent to imported child).

    Assume I have two children with record IDs 234 and 235 and both belong to one parent with same parent key fields. As soon as parent with record ID 9876 is created for child 234, it should also get linked to child 235. Right now, when the children are imported two parents are created with different record IDs. Within each parent I can see only one child.

    Is this doable?

    Thanks

    ------------------------------
    Deepa Deepa
    ------------------------------



  • 7.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 17:58
    I think it's possible but some clarification first. What is the Key field of the parent table?  Is it Record ID  and a separate field is the value that needs to match the Child?

    if that is the case, can we change the Key field of the parent to that Key field as part of the solution, or is that not an option. 




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



  • 8.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 18:11
    Mark,

    You are right. Currently the key field is just the record ID. The other fields that would uniquely identify the parent would be not one but multiple fields :Run Date, FileTime & Related Bot. A combination of these would identify it as the unique parent. I guess I could create a formula field that stitches these together and make that the key field?

    Thanks!

    ------------------------------
    Deepa Deepa
    ------------------------------



  • 9.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 18:55
    Well, a Key field can't be a formula field, but yes you can create that formula field and use it to populate the key field initially and  if adding a record manually (Form Rules). 

    If you can get that key field changed, then what you do is to create a formula checkbox field on the Parent called [Parent Exists?] with a formula of True. Look that up down to Children.

    Then create or repurpose your Automation to create a Parent when a child is added or modified and the lookup value of [Parent Exists?] is unchecked

    The surprising but convenient behavior of the automation is this.  Suppose you import 1,000 children needing 500 parents created.  You would think that  this would cause a rate limit being hit because you were asking for 1000 automations to run all at once and they can only be triggered at a maximum rate of 20 per second. 

     The surprise is that you will find that the automation is only run once except it will have 1000 steps.  You can watch it run and it doesn't run particularly quickly but eventually it will finish. 










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



  • 10.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 19:01
    Thank you Mark! Will try this out. Have to check what changing the key field does to existing data.

    ------------------------------
    Deepa Deepa
    ------------------------------



  • 11.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 19:04
    There is also a solution for if you can't change the key field, but it's more complicated to implement. We would have to get on a zoom call tother for that solution.

    You would need to create a new table of Key field records.

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



  • 12.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 07-27-2020 19:10
    That would be awesome. Let me try experimenting with changing the key field on a copy of the app and will get back to you tomorrow. Thank you!

    ------------------------------
    Deepa Deepa
    ------------------------------



  • 13.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 08-11-2020 16:29
    This is amazing!  Thank you all for being so forthcoming w/ amazing feedback.  This is exactly what I needed and it works like a charm!

    ------------------------------
    Charlene Wilbur
    ------------------------------



  • 14.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 03-17-2021 02:20
    Hi All,

    Can any one have a example since I tried several times and still cant achieve the results using automation and checkbox field to check the parent record.

    Thank you

    ------------------------------
    Kristie Kristie
    ------------------------------



  • 15.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 03-17-2021 06:50
    Kristi,
    you would need to explain your tables and their relationships and their Key fields and expect lain what you have set up so far.  Then I can suggest what the problem might be.  Please also state what your objective is.

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



  • 16.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 06-01-2021 00:29
    Hi Mark and others,

    I have the same general problem:
    1. I am bulk importing a list of Materials which have an Origin field.
    2. I have an Origin table, the key is Origin, and Origin is a parent to Materials.
    3. I want to create new Origin records whenever there are new/updated materials with Origin's that don't exist yet.
    4. Doing what you suggest above (create parent if parent doesn't exist yet) with Automations or Actions works fine until I import multiple materials with the same Origin. When that happens I get an error saying I'm trying to add non-unique values for the Origin field.
    Is there a way you know to get around this?

    ------------------------------
    Matt NZ
    ------------------------------



  • 17.  RE: Automatically create a parent record when info saved in the child reference field

    Posted 06-01-2021 09:06
    Matt,

    I do not have a magic answer to that and I do get some of those error messages myself. If you use a pipeline, in part because the pipeline error messaging is not fully developed,  I don't get those error messages by email. So that is one solution. Use a pipeline. Some of the steps error out but who cares.

    I also feel more comfortable with an overnight process which will look for any orphans and trigger an update to the orphan in order to trigger the pipeline to create the parent.  The overnight process is a safety net in case for some reason the parents don't get created live when the child was created during the day.


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