How: Use value of one field as a formula in another field

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
Use Case:


Application Alpha: A reference table in App "Alpha"  of Country Codes, with a TEXT field describing what the address label would look like for that specific country,

[Field: Alpha_Addy_Format = [Supplier Address]  & "  \n" & [Supplier City] & ", " & [Supplier State or Province] & "   " & [Supplier Zip or Postal Code] & " \n" & [Country Code (db) - Country Name]


Application Bravo: A relationship that looksup and brings over Alpha_Addy_Format.   I want to have a new FORMULA field "Bravo_Address" that use the value in Alpha_Addy_Format as a FORMULA for the record so the FORMULA is the text contained in Alpha_Addy_Format.   

Objective: Store a formatted street address formatted specifically for the country a supplier is in using the already existing country reference table.

Benefit: No massive case statement, full flexibility.

Can this be done?  It's very similar to the INDIRECT function in Excel.
Photo of Vashi

Vashi

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Adrian

Adrian

  • 0 Points
Hey Joe, 

If I am understanding your needs correctly you should be able to achieve this by carrying the country code table over to the other app, and building a relationship where each country code has many suppliers. You can then create a lookup field within the supplier table for country codes, and a new formula field where you paste the copied Alpha_Addy_Format. 

If this doesn't achieve the desired result I would suggest opening a case with Customer Care under the help "?". Someone will be able to understand your goal in greater detail and give you a more specific answer. 
Photo of Vashi

Vashi

  • 0 Points
Adrian:  Thanks. That's not exactly what I'm trying to do. I'll looking for something a bit more dynamic. I'll go the ticket route. Thanks.
Photo of Vashi

Vashi

  • 0 Points
Went through QB Support to see about a method to do this.

Best was to use a CASE statement to format national addresses differently.   

My Comment: Short of an external program using API, a CASE statement requires significantly more maintenance to do this way when countries are added and a mountain of work upfront. But every system has it's limits, even QB.

General outline...

CASE Country Code
    GERM
      address another way
    HUN
      address another way
    JP
      address yet another way
    ELSE
      address in USA format
END