# How can I use relationships for this simple VLOOKUP?

• 0
• Question
• Updated 3 years ago

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.

• 20 Points

Posted 3 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
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
• 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.

QuickBaseCoach App Dev./Training, Champion

• 65,310 Points
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.