Discussions

Expand all | Collapse all

Need help on whether to use IF or Case

  • 1.  Need help on whether to use IF or Case

    Posted 10-12-2018 21:04
    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 ? 




  • 2.  RE: Need help on whether to use IF or Case

    Posted 10-12-2018 21:42
    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.


  • 3.  RE: Need help on whether to use IF or Case

    Posted 10-12-2018 22:03
    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. 


  • 4.  RE: Need help on whether to use IF or Case

    Posted 10-12-2018 22:10
    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?


  • 5.  RE: Need help on whether to use IF or Case

    Posted 10-12-2018 22:21
    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)