Discussions

Expand all | Collapse all

Edit Child Reference Fields When Parent's Text Key Field Changes (with API?)

  • 1.  Edit Child Reference Fields When Parent's Text Key Field Changes (with API?)

    Posted 19 days ago
    I have two tables: Projects and Service Appointments, and a project has many service appointments. The key field for the Projects table is the Project Name, a text field. This allows for an easier end-user experience when they're importing service appointments, so I'm not looking to use the Record ID# as the key field (even though I know this would solve my problem.)

    While rare, there are instances where the Project Name might change. When that happens, the service appointments are orphaned because they retain the old Project Name in their reference field.

    I want to automate the process to update the child records' Project Name reference field when the parent Project Name changes. I know this can be done easily with Automations or Pipelines, but I sometimes have several thousand service appointments for a single project which won't work for Automations and might eat through more Pipeline step runs than I'm comfortable with (unless the Pipeline can be set up to do a bulk update?)

    I've just started getting familiar with APIs and have implemented a couple Webhooks in my app recently using the PurgeRecords and ImportFromCSV APIs. I would like to see if I can accomplish what I'm looking for with APIs somehow. Because I'm still a novice when it comes to APIs, I was wanting to see if there's a way to query the orphaned child records, format them into a CSV, and use the ImportFromCSV to update the records. I really like how ImportFromCSV will update all the records at once, but if that can't be done, that's fine if records need to be updated one at a time.

    ------------------------------
    Brian
    ------------------------------


  • 2.  RE: Edit Child Reference Fields When Parent's Text Key Field Changes (with API?)

    Posted 19 days ago
    I would approach this with the following Automation Actions.

    Detect that a project name has changed (trigger)

    Update field in a single record table called automation focus with the all the project name and the new project name.

    (the relationship will have been set up to Look up the value of those two fields in Automation Focus into every child record via a relationship back to the single record table automation focus)

    Run a saved table to table import which will import child records into themselves, merging on record ID number, Filtered only for the children that have the old project name, and changing just the project name field from the old project name to the new project name.


    That is it. Fully automated and the saved table to table imports will run in a second or two even for thousands of records.

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