How can I use relationships for this simple VLOOKUP?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm currently stumped by what, I think, is a very easy problem. Ultimately, this is a simple VLOOKUP:

Table 1: Orders

Field: City   (non-unique tex, repeatedt: Atlanta, Baltimore, Chicago )

Order Number (unique): 123, 234, 345

Table 2: Collection Agents

City (unique text)

Agent (non-unique user, each agent may have multiple cities)

It seems I'd need a table to table relationship of one collection agent to many orders. City in Table 2 being a key field.

Now, I want to pull in the Agent from Table 2 into Table 1 automatically.

But, I'm blanking on how to make this happen using city as the linkage.

Your help is much appreciated.

Photo of Larbi

Larbi

  • 20 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
You will need to make a table of unique cities.  

Set the Key field to be the city name

Then a relationship where 1 agent has many cities based on the reference feld on the right being the city name.  Lookup the agent down to the city.

Then a relationship where 1 city has many orders based on the refence field on the right being the city name.

Lookup the agent down to the order


.. And y'ur done
Photo of Larbi

Larbi

  • 20 Points
So, let me get this straight:

Table 1: Orders
   Field 1: Order Number (unique, key field)
   Field 2: Order Number City (non-unique)

Table 2: Agents
   Field 3: Agent Name (unique, key field)

Table 3: Cities
   Field 4: City Name (unique, key field)

Relationships:

Orders < Agents (reference: related order, lookup Field 1)
Agents < Cities (reference: related agent, lookup Field 3)
Cities < Orders (reference: related city, lookup Field 4)

But I'm struggling to see how we use the non-unique city in table 1 (orders) to pull in the agent name.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
You will not have that first relationsip.  The Agent information, say Agent name,  lives in the Agent Table and will be a lookup field down to City.  Then 1 City has Many Orders and then lookup the lookup of the Agent name down from City down to the Orders.