Forum Discussion

RickPutnam's avatar
RickPutnam
Qrew Cadet
4 years ago

Mapping Connected Table Records to Related Table

Greetings,

I have a connected table in my app that pulls customer records including address data from an internal system. My app needs to map sales rep territories which are defined by state to the customers. The Customers table is related to the States table which is related to the Territories table which is related to the Sales Rep table. I cannot figure out how to set the Related State field on the Customer's table when those records are added or updated as part of the bulk update process.

Connected data includes a text field with State name. Those names match the name in the states table but I can't figure out how to map them via automation.

Any help or guidance would be very much appreciated.

------------------------------
Rick Putnam
------------------------------
  • If you make the State field the Key field for the Sales Territories table, then you can simply make a relationship to a mirror field for the Customer state. 

    By mirror field I mean just a formula field equal to

    [Address: State/Region]

    If you have the problem of a mismatch in terminology for state abbreviations vs full state names, i have some formuals to convert this.

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

2 Replies

  • If you make the State field the Key field for the Sales Territories table, then you can simply make a relationship to a mirror field for the Customer state. 

    By mirror field I mean just a formula field equal to

    [Address: State/Region]

    If you have the problem of a mismatch in terminology for state abbreviations vs full state names, i have some formuals to convert this.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • RickPutnam's avatar
      RickPutnam
      Qrew Cadet
      Thank you, Mark. That is exactly what I needed. Keying off of State Name rather than Record ID let's the import update the relationship seamlessly.

      ------------------------------
      Rick Putnam
      ------------------------------