Formula that Dynamically Parses a Value

[Keshara_Moore]

Hello all!

I am trying to compile a formula that dynamically spits out a value based on the values in 2 other static data fields, the first being an Additional Authors Count that counts the number of authors that exist on a record and the second field being a deliverable field. The end goal of this formula is to compute a number amount that a freelancer should get paid based on completely specific deliverables and taking into account the number of authors on said project they worked on. For example: If a freelancer works on a project deliverable of content calls and they do so on a project with an author count of 2 additional authors, I want the formula to be able to spit out a number based on those two external values and the static values that live in said formula.

I’ve been able to get this far, but now need to somehow add in the deliverable field condition so that if the deliverable is Content Calls and Additional Author is 1 then add 1000 to the existing multi author amount (which is a sperate stand alone field that also exist in the table) and that will be the value housed in this formula field at the end for 9 different instances (see conditions listed below). Right now I have the following:

ALT Text: IF({Additonal Author Count}=1, {Multi Author Amount}+1000, IF({Additonal Author Count}=2, {Multi Author Amount}+2000, IF({Additonal Author Count}=3, {Multi Author Amount}+3000)))

My end goal is something along the lines of the following, I just can’t quite figure out how to translate all that into an acceptable formula that the system will accept:

• If Additional Author Count = 1 & Deliverable is Content Calls then add 1000 to Multi Author Amount
• Otherwise, If Additional Author Count = 2 & Deliverable is Content Calls then add 2000 to Multi Author Amount
• Otherwise, If Additional Author Count = 3 & Deliverable is Content Calls then add 3000 to Multi Author Amount
• Otherwise, If Additional Author Count = 1 & Deliverable is North Star then add 1000 to Multi Author Amount
• Otherwise, If Additional Author Count = 2 & Deliverable is North Star then add 2000 to Multi Author Amount
• Otherwise, If Additional Author Count = 3 & Deliverable is North Star then add 3000 to Multi Author Amount
• Otherwise, If Additional Author Count = 1 & Deliverable is Revisions then add 1000 to Multi Author Amount
• Otherwise, If Additional Author Count = 2 & Deliverable is Revisions then add 2000 to Multi Author Amount
• Otherwise, If Additional Author Count = 3 & Deliverable is Revisions then add 3000 to Multi Author Amount

I would love for the above conditions/rules to all live in one single formula field and not have to split them up into multiple different formula fields, but I understand if this is not possible. I am assuming there is some IF function nesting needed here or maybe the use of AND or even SWITCH function, but I can’t figure out. I would greatly appreciate any help, even if it’s just how to get the deliverable field into the above formula I’ve already compiled so that it says "If additional author count = 1 & deliverable = Content Calls, then add 1000 to the multi author amount.

[Kamille_Parks · Accepted solution]

Your translation doesn’t appear to have the value of {Deliverable} affect the outcome, which seems to be just (1000 * {Additional Author Count}) + {Multi Author Amount}. So try that formula to start with.

If you do need to have the Deliverable field factored in there somehow, SWITCH Is definitely the way to go.

SWITCH(
  {Deliverable},
  "Content Call", SWITCH(
    {Additional Author Count},
    1, (1000 + {Multi Author Amount}),
    2, (2000 + {Multi Author Amount}),
    3, (3000 + {Multi Author Amount})
  ),
  "North Star", SWITCH(
    {Additional Author Count},
    1, (1000 + {Multi Author Amount}),
    2, (2000 + {Multi Author Amount}),
    3, (3000 + {Multi Author Amount})
  ),
  "Revisions", SWITCH(
    {Additional Author Count},
    1, (1000 + {Multi Author Amount}),
    2, (2000 + {Multi Author Amount}),
    3, (3000 + {Multi Author Amount})
  )
)

[Ben_Young1]

@Keshara_Moore - Kamille’s formula is definitely the most efficient way to tackle your requirements.

It might not be a concern or possibility, but for the sake of scaling, it’s worth calling out that if there is a world where you could see more than three additional authors, then you can tweak Kamille’s formula to a structure into something like this:

SWITCH(
  {Deliverable},
  "Content Call", IF(
    {Additional Author Count},
    ({Additional Author Count} * 1000) + {Multi Author Amount}
  ),
  "North Star", IF(
    {Additional Author Count},
    ({Additional Author Count} * 1000) + {Multi Author Amount}
  ),
  "Revisions", IF(
    {Additional Author Count},
    ({Additional Author Count} * 1000) + {Multi Author Amount}
  )
)

Typing this on mobile. I apologize if the formatting comes out weird!

[Alexey_Gusev]

1000*({Additional Author Count})*({Additional Author Count}<4)*(FIND(Deliverable,'North Star Revisions Content Calls')>0)+{Multi Author Amount}

just for fun and morning mind exercise, don’t apply in real life :slightly_smiling_face: , use readable solutions instead

[kuovonne]

Alexey_Gusev:

just for fun and morning mind exercise, don’t apply in real life :slightly_smiling_face: , use readable solutions instead

Awesome comment.

There are so many ways to do things in code.

Not only have you gotten a bit of fun from puzzling through this, you have also gifted other fellow puzzlers with your design so we can see how it works.

As you state, the solution to put into production should be the “readable” one, but we can still have fun with “clever” ones!

[Keshara_Moore]

@Kamille_Parks Thank you for that! I racked my brain all Friday afternoon and went through several iterations, but could not discern this. Greatly appreciated!

[Keshara_Moore]

@Ben.Young Thank you as well for providing a scalable iteration of the formula as well; that will be helpful given that we may need to expand the number of additional authors in the future. Cheers!

[Keshara_Moore]

@Alexey_Gusev Ah, do you mean writing the formula in long/spaced out format for easier review? If so, noted for the future!