Something is wrong with my formula but I don't know what...

[Berenger_Anceaux]

Hello,

I’m building a contact management system in Coda to track my professional relationships. Using the ‘Last Interaction’ date and ‘Interaction Frequency’ categories (such as ‘Close (every month)’, ‘Every 3 Months’, ‘Every 6 Months’, and ‘Indifferent’), I created a formula that evaluates whether my contacts are ‘Active’, ‘To Contact Again’, or ‘Inactive’ based on the time elapsed since our last interaction. The goal is to ensure I maintain regular communication with important contacts while managing those I may not need to follow up with as frequently.

I try to make a formula but I don’t know what’s wrong with it. It says “Missing arguments in formula”.

Can you help me with it ? Thank you so much.

If(
  thisRow.[Last Interaction].IsBlank() OR thisRow.[Last Interaction] > Today(),
  "",
  If(
    thisRow.[Interaction Frequency] = Indifferent,
    If(
      Today() - thisRow.[Last Interaction] <= 180, 
      "Active", // 6 months
      "Inactive"
    ),
    If(
      thisRow.[Interaction Frequency] = Close,
      If(
        Today() - thisRow.[Last Interaction] <= 30, 
        "Active", // 1 month
        If(
          Today() - thisRow.[Last Interaction] <= 365, 
          "To Contact Again", // less than a year
          "Inactive" // More than a year
        )
      ),
      If(
        thisRow.[Interaction Frequency] = [Every 3 Months],
        If(
          Today() - thisRow.[Last Interaction] <= 90, 
          "Active", // 3 months
          If(
            Today() - thisRow.[Last Interaction] <= 365, 
            "To Contact Again", // less than a year
            "Inactive" // More than a year
          )
        ),
        If(
          thisRow.[Interaction Frequency] = [Every 6 Months],
          If(
            Today() - thisRow.[Last Interaction] <= 180, 
            "Active", // 6 months
            If(
              Today() - thisRow.[Last Interaction] <= 365, 
              "To Contact Again", // less than a year
              "Inactive" // More than a year
            )
          )
        )
      )
    )
  )
)

[Felipe_Arnhold]

Hello @Berenger_Anceaux

Welcome to the Coda community.

I have set up a doc—please review it to see if it behaves as you desire. You can copy the doc to examine the formula. Rather than using nested if statements, I implemented the switchIf formula. Additionally, I utilized the WithName formula to store the time since the last interaction in a “variable,” as it is frequently used in the formula.

Furthermore, I created a table that lists possible categories/frequencies for a contact. I added a column to this table to specify the periodicity in days for each category. Therefore, in the status formula, instead of testing each frequency individually, you can directly retrieve the periodicity from the Frequency column and set the status, regardless of the selected frequency. This approach is scalable; if you ever need to change the periodicity or add more categories, you simply need to update the frequency table and not the formula.

Playable link: Contact management · Demos

Best regards,
Arnhold

[Agile_Dynamics]

@Felipe_Arnhold,
thank you for providing such a detailed response to this request.

may i ask you to provide a screen-shot of the formula you proposed as the solution.
this will help others to see how you solved the issue.

the document you provided is not available to everyone, so your solution is not available to anyone else who might benefit from seeing it.

ⒶⓍ

[Berenger_Anceaux]

Thank you so much @Felipe_Arnhold !

[Felipe_Arnhold]

Thank you @Agile_Dynamics

Here is a screenshot of the formula.
I also shared the doc to everyone with the link can comment. Please check again if you can access now.

Best regards,
Arnhold