How to use formula in Structure to compare text values of a single choice list field ?

[Nicolas R.]

Hi everybody,

I use two single choice list fields to track when the job is planned vs. expected.

Field 1 : Plan 

Field 2 : Expected

Values for those two fields are the same : Sprint 1, Sprint 2, Sprint 3 Sprint 4

I would like to use a formula for my structure that highlight when a story has a planned field that is different from expected. I would like to display this delay with a format like :

"Plan" - "Expected"

I tried to try IF sequences but I didn't find the way to do it. 

The person who can help me will be of great help to me !

[Stepan Kholodov _Tempo_ · Accepted solution]

Hello @Nicolas R. 

If you want to calculate the delay using the dates of Sprints -

for example: Plan has Sprint 1 defined and Executed has Sprint 2, and the delay would be the difference between the defined Start date of Sprint 1 and Sprint 2 - 

then it's not possible. Although the select field has Sprints as values, in the background they are just text values with no connection to actual Sprints and their attributes such as dates that exist on Agile boards. So, in this setup, the Formula column simply doesn't have access to the dates of Sprints to compare them to each other.

If you use different values - a pair of custom fields with the date when the job was planned and when it started, or add dates in the names of Plan/Expected fields' values ('01-01-2024 Sprint 1' instead of just 'Sprint 1'), then you can potentially make the comparison in the formula.

I hope this helps. If you need further assistance or if you have other questions about Structure, please reach out to us directly at our support portal.

Best regards,
Stepan
Tempo (the Structure app data)

[Nicolas R.]

Thank you Stepan for your answer. In my example, I use custom fields with text values. 
For example for Custom Field 1 called "Plan" the custom values are :

• "Sprint 1"
• "Sprint 2"
• "Sprint 3"

The custom field is a single choice list. 
So I would like to know the formula to compare the values for those 2 custom fields. 

[Stepan Kholodov _Tempo_]

If you only want to compare the values and see an indicator if they don't match, you can do it with a simple formula like this:

if plan != expected: "displayed text"

Best regards,
Stepan

[Nicolas R.]

That's where I have my issue. I tried this formula but the value returned is not good and I don't understand why.

Formula : 

IF Planning_Iteration!=Expected_Iteration:"diferent" else:"same"

[Stepan Kholodov _Tempo_]

Thank you for sharing more details. This should help:

if planning_iteration.value != expected_iteration.value: "different"

the values in both fields look the same, but they have different ids in the background, so the formula should consider only the values themselves.

Best regards,
Stepan

[Nicolas R.]

Okay ! Now I understand. Thank you a lot Stepan. It works now !