Need help on whether to use IF or Case

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
I have a list of locations that i am trying to make a formula derived from a current field. 
but naturally i have about 100 locations that then have corresponding regions , so i would have 3 statements with lists of locations. Basically trying to do a vlookup and use a temp table from a  list , 

IF([location] = ("DFW","HOU","SAT"), Central) ,("DEN", "PHX","SEA"), West) , ("MIA", "TPA", "JAX"), East)


CASE([locaiton] = ("DFW","HOU","SAT"), Central) ,("DEN", "PHX","SEA"), West) , ("MIA", "TPA", "JAX"), East)

or should i just create a new table with master list and then try and link the two tables ? 


Photo of Ben Quackenbush

Ben Quackenbush

  • 90 Points 75 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
I'd probably create a Locations table, yeah. And then whatever table has Location in it, look up the related Region from the Locations table onto that table. Much easier to manage.
Photo of Ben Quackenbush

Ben Quackenbush

  • 90 Points 75 badge 2x thumb
I created the table with Locations and regions, i am having trouble linking the two tables now. my parent table uses a company # as primary reference , and each company has 1 location. i then try to relate the tables on location to pull the region to the master , but it returns nothing. 
If I'm reading you correctly (never a sure thing), you'll need to create either A) relationships between the Locations table and any tables where you have a Location and want the related Region to show up, or B) a Region field in the intermediate table that looks up to the Location table, which field you'll then look up from there to your parent table.

So I think you have
PARENT table with [Related Company] as a field
COMPANY table with [Location]
LOCATION table

So you either need a relationship between PARENT - LOCATION so that your lookup field in PARENT can 'see' the LOCATION table, or you need to look up [Region] onto the COMPANY table via [Location] and then look up the Region again from COMPANY to PARENT. Which way I guess depends on the nature of your PARENT table. Does that make sense?
Photo of Ben Quackenbush

Ben Quackenbush

  • 90 Points 75 badge 2x thumb
yes so i think best way is to just export it into excel and the Vlookup , add the new column to parent table , and call it done. Didn't think outside the box(quickbase)