Nested If/and statements
Hello! I recently started with San Francisco Opera who uses QB to maintain their artistic database. I'm hoping someone can help me with the following formula.
BACKGROUND:
I am trying to edit an existing formula (created by a former employee) that injects clauses into our contracts if certain parameters are met. Specifically, if an Artist is casted for more than one role assignment within a given contract, it should inject clauses reflecting their multiple assigned roles and respective fees. I believe the If statement is stopping after the first formula block because it is true, therefore not injecting the clause(s) for the remaining roles.
FORMULA:
If(([Fee Type]="Per Performance" and [Sing/Cover 1]="COVER"),(List("","Should PRINCIPAL SING a performance in role of ", Trim(Part(Upper([Character Name 1]), 1, "()")),", PRINCIPAL will receive a Per-Performance fee of $", ToText([If Sing Fee 1])," in lieu of the cover fee.")),
// the above if statement is TRUE, therefore stopping after the first code block
([Fee Type]="Per Performance" and [Sing/Cover 2]="COVER"),(List("","Should PRINCIPAL SING a performance in role of ", Trim(Part(Upper([Character Name 2]), 1, "(")),", PRINCIPAL will receive a Per-Performance fee of $", ToText([If Sing Fee 2])," in lieu of the cover fee.")),...
The formula continues for other use cases but for clarity, I've just included just a snippet.
RESULT:
(In this case, the contract/artist in question has two assigned roles, but only injects one clause)
Should PRINCIPAL SING a performance in role of Role 1, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera.
DESIRED RESULT:
Should PRINCIPAL SING a performance in role of Role 1, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera. Should PRINCIPAL SING a performance in role of Role 2, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera.
QUESTION:
How would I alter this formula so that it includes all [Sing/Cover] fields if they exist for the given contract? Thank you for your help!
------------------------------
Devin Hinzo
------------------------------
BACKGROUND:
I am trying to edit an existing formula (created by a former employee) that injects clauses into our contracts if certain parameters are met. Specifically, if an Artist is casted for more than one role assignment within a given contract, it should inject clauses reflecting their multiple assigned roles and respective fees. I believe the If statement is stopping after the first formula block because it is true, therefore not injecting the clause(s) for the remaining roles.
FORMULA:
If(([Fee Type]="Per Performance" and [Sing/Cover 1]="COVER"),(List("","Should PRINCIPAL SING a performance in role of ", Trim(Part(Upper([Character Name 1]), 1, "()")),", PRINCIPAL will receive a Per-Performance fee of $", ToText([If Sing Fee 1])," in lieu of the cover fee.")),
// the above if statement is TRUE, therefore stopping after the first code block
([Fee Type]="Per Performance" and [Sing/Cover 2]="COVER"),(List("","Should PRINCIPAL SING a performance in role of ", Trim(Part(Upper([Character Name 2]), 1, "(")),", PRINCIPAL will receive a Per-Performance fee of $", ToText([If Sing Fee 2])," in lieu of the cover fee.")),...
The formula continues for other use cases but for clarity, I've just included just a snippet.
RESULT:
(In this case, the contract/artist in question has two assigned roles, but only injects one clause)
Should PRINCIPAL SING a performance in role of Role 1, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera.
DESIRED RESULT:
Should PRINCIPAL SING a performance in role of Role 1, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera. Should PRINCIPAL SING a performance in role of Role 2, PRINCIPAL will receive a Per-Performance fee of $xxx in lieu of the cover fee. Please see the attached memo detailing how compensation is broken down at San Francisco Opera.
QUESTION:
How would I alter this formula so that it includes all [Sing/Cover] fields if they exist for the given contract? Thank you for your help!
------------------------------
Devin Hinzo
------------------------------
