Forum Discussion

Kelly-AnnTainto's avatar
Kelly-AnnTainto
Qrew Trainee
4 years ago

Address Label Formula

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 Replies

  • 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
    ------------------------------