Forum Discussion

JoshCollins's avatar
JoshCollins
Qrew Assistant Captain
5 years ago

Text Formula Field with Filtering Criteria

Hello, Can anyone provide some help with a formula for this?

In my tasks table, I have 3 lookup fields: , [Properties - Address], and [Projects - Address].

When creating a report of a user's tasks, I want to have one field called "Address" or "Absolute Address" where I can essentially grab the address from any of the 3 lookup fields that applies.

I'm assuming I need to use a Formula - Text field to get this info.

The formula would say:
If
is not blank, then ,
Otherwise, If
[Properties - Address] is not blank, then [Properties - Address],
Otherwise, If
[Projects - Address] is not blank, then [Projects - Address]

Maybe this isn't the best way to do it, but this is my understanding of how it should work. Can anyone help with a good formula or method for this?

Thanks!

Josh

------------------------------
Josh Collins
------------------------------
Formulas and functions
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years ago
    Josh,

    First make sure that the reference field for each parent reference field has the Blank box cleared.  





    Create a Formula Text field called Absolute Address

    If (
    IsNull([Related Transactions] )=false, ToText(),
    IsNull([Related Properties])=false, ToText( [Properties - Address]),
    IsNull( [Related Projects)]=false , ToText([Projects - Address]),  "Error, No Parent Transaction, Property or Project" )


    This sequentially goes through your Parent relationships,  checks if it exists and converts the Look Up Address to a Text Field.




    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------