Discussions

Expand all | Collapse all

Address Label Formula

  • 1.  Address Label Formula

    Posted 15 days ago
    I'm trying to create a formula text field [Landlord Address Label] that combines several fields in a list form (like a mailing label) with a few If-then-that issues.   I'm still learning formulas so I'm not sure how to go about adding them to formula below or if it's something I need to split up through additional formula fields or check boxes etc first and then add those new formula fields to this field.   Any help that can be offered would be wonderful!

    I have the following fields:

    [Landlord Name]
    [In care of:]
    [Address: Street 1]
    [Address: Street 2]
    [Address: City]
    [Address: State]
    [Address: Postal Code]

    This is the formula I currently have in the formula text field: 

    List("\n", [Landlord Name], "c/o" &" "& [In Care of:], "",
    List(", ", [Address: Street 1], [Address: Street 2]), "",
    List(", ", [Address: City], [Address: State/Region]), [Address: Postal Code])

    However, I have the following issues that I need figure out if I can add to the formula somehow:

    1. Landlord Name - this can be a company or an individual.  If it's an individual then we put a coma followed by Individual next to the name (Ex  Joe Smith, Individual).  I need to figure out a way to remove the " , Individual".   I thought Left([Landlord Name], ",") would work since I want to remove after the coma, but I need it to apply only when it is ", Individual" since it will also take off ", LLC" or ", Inc." on the companies.   Is there a way to do this by just a formula or do I need to create a check box  to "check" if it's an individual.  Then add to the formula If{checkbox}=True, Left([Landlord Name], ","), [Landlord Name] or something similar?

    2. I've added "c/o" &" "& to formula above, however, similar to #1 I need it to apply only when the [In Care of] field is not blank.  Otherwise it shows up as  

    Joe's Automotive
    c/o
    123 Anywhere St
    Boston, MA 02110

    Thank you,
    Kelly 






    ------------------------------
    Kelly-Ann Taintor
    ------------------------------


  • 2.  RE: Address Label Formula

    Posted 14 days ago
    I wanted to follow up and say I got it working.   I did some more searching and found out about variables.  This is what ended up working:

    var Text Landlord = If(Contains([Landlord Name], "Individual"), Left([Landlord Name],","), [Landlord Name]);
    var Text Care = If(Trim([In Care of:])<>"","c/o" &" "& [In Care of:], [In Care of:]);

    List("\n", $Landlord, $Care, "",
    List(", ", [Address: Street 1], [Address: Street 2]), "",
    List(", ", [Address: City], [Address: State/Region]), [Address: Postal Code])

    ------------------------------
    Kelly-Ann Taintor
    ------------------------------



  • 3.  RE: Address Label Formula

    Posted 12 days ago
    nicely done!

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------