Circular Reference

[Bobbo_Buckley]

I consider myself a fairly adept user of Airtable, but I think this question is going to say otherwise.

My objective is to see this (the red dollar sign represents my Job Cost field):

So on a calendar view, I want to see a concatenation of the Company Name, Contact Name and the Job Cost. The Company and Contact are two separate linked tables, and work fine in the primary field using the following code: {Link to Companies}&" | "&{Link to Contacts}
But my Job Cost field is a Lookup from another tab called Proposals.

My modified code of:

works fine in any field/column other than the primary field/column:

When I add the same code to the Primary Field, I get this error:

Which makes no sense to me (novice apparently). Is there a way to do what I am attempting to do?

Here is a link to the base for your amusement and hopefully help:

[Jason]

Hi @Bobbo_Buckley,

Circular references can cause headaches for novices and experts alike! They’re also a bit difficult to explain at times, but here’s what I noticed from your base.

• Projects are linked to Proposals, and the primary field in Proposals inherits its name from the primary field in Projects.

• When trying to reference Job Cost Lookup in the primary field, it can’t function; the value of the primary field in Projects can’t be known without knowing the value of the primary field in Proposals, which in turn relies on knowing the value of the primary field in Projects.

After looking over your base, I’d recommend consolidating your projects and proposals tables into a single table. This will not only get you around the circular reference error, but will decrease the amount of duplicate data you have in your base.

To differentiate proposals from projects, you can take advantage of a single select field to give each record a status (project or proposal), and then use filtered views to separate out the two from one another within the same table.

Let me know if you have any follow-up questions about this!

[Bobbo_Buckley]

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.

[Jason · Accepted solution]

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!

[Bobbo_Buckley]

Jason,

That is awesome, and the second one works exactly like I need it to. I’m not sure I understand exactly why it works (not at all familiar with the AND option, but i’ll continue to study this until I get it), but I’m really good and monkey see, monkey do, so I’m saving this in my Notepad ++ file for further research and future reference.

[Jason]

Glad it helps! The AND function allows you to say:

• IF any of the conditions listed within AND are true (in your case, if there are field values for cabinet qty and job cost)
• THEN produce a result (in your case, whatever value is actually in the field)