Thanks Jason, that was helpful. I just ended up adding a second number field that I fill in manually based on the Lookup field, and I’m able to use that in my formula, and keep my data sets (Tables) separated and fairly simple (If I added all the Proposal fields to the Projects Table, I would also need to do the same to Estimates, and I think that would make it harder for my users to utilize).
Here is the current working formula:
{Link to Contacts}&" | “&{Cabinet Qty}&” | "&{Job Cost}
Once I got that working, decided to add another pair of fields to do the same with Cabinet Quantity, which worked perfectly with one exception. Early in the process these two additional fields are empty, but I still get my Pipe characters, and I want to eliminate those. I’ve spent a little time attempting to come up with nested IF statements that would hide the pike characters when the fields are blank, but I just don’t understand the syntax well enough to get this working, so, can you help me out again?
Here is my poorly executed nested IF Statement:
{Link to Contacts}
IF(
{Cabinet Qty}="",
BLANK(),
IF(
{Cabinet Qty}!="",
&" | “&{Cabinet Qty},
IF(
{Job Cost}=”",
BLANK(),
IF(
{Job Cost}=!"",
&" | "&{Job Cost}
)
)
)
)
So I want the {Link to Contacts} to show all the time, but I want the Pipe characters to hide when the two additional fields are blank. The link above should still be active to visit the Base. I apologize for my lack of coding knowledge, and appreciate any help you can provide.
Happy to help!
Here’s a formula you can use to only display the pipes (and field values) if both {Cabinet Qty} and {Job Cost} have values.
{Link to Contacts}
&
IF(
AND(
{Cabinet Qty},
{Job Cost}
),
" | " & {Cabinet Qty} & " | " & {Job Cost}
)
That formula assumes that you only want to see the values when both fields have a value. The formula logic follows as:
- Start with link to contacts (this will always be shown)
- Then, if both cabinet quantity and job cost have values, show those values
- Otherwise, return nothing (except for contacts of course)
If you’d rather account for scenarios when one or the other fields has a value, and you’d like to see either one, here’s an adjusted formula you can use.
{Link to Contacts}
&
IF(
AND(
{Cabinet Qty},
{Job Cost}
),
" | " & {Cabinet Qty} & " | " & {Job Cost},
IF(
{Cabinet Qty},
" | " & {Cabinet Qty},
IF(
{Job Cost},
" | " & {Job Cost}
)
)
)
The logic for this formula is:
- Start with link to contacts again
- If both cabinet quantity and job cost have values, show those values
- If only cabinet quantity has a value, show that
- If only job cost has a value, show that
- Otherwise show nothing
Let me know if that makes sense!
