Automation : Lookup table with a label field

[Joseph HANI]

Hello everyone ! 

I hope you are doing great ! 

I'm quite lost here on the update of my automation...

To give you more insight, we were using field to plan our estimate Delivery of an Epic for the team to commit on this deadline. Based on the value of this field, it updated 2 other dates field : Build Start / Build End 

Everything was working great here was the automation using lookup table, added some log to be sure 

But here is my problem : 

- Recently, we have switched our process and use a new add-on that have created a label field that is populated automatically. 
And now we have to plan / commit the Delivery and the Test of our Epic. 
--> Which mean that I will create 2 automation to will populate 4 fields 

- Delivery : Build Start / Build End 
- Test : Test Start / Test End 

Both labels value will be now in this field : "Program baseline sprint" and looks like this : 

- DATADE : Delivery

- DATAAN : Test

And here is the trick ... 
I kind of don't understand how I can do the matching of this labels inside the lookup table ... I tried to used regex to match "AN" or "DE"

And then even more disturbing for me is the edilt field where I tried to do the matching ... 

Build Start :
{{DeliveryTargetStart.get(issue.Program Baseline Sprint)}}

When I tried the automation, it shows success but nothing happen :( 

I'm open to any suggestion after many failed attempt 😭

Thank you very much for your help ! 

Have a nice day ! 

[Bill Sheboy]

Hi @Joseph HANI 

The keys for a Lookup Table are static text and although they could represent a regular expression, they would not behave that way in the lookup's get() function.

Instead, I recommend parsing out the unique part of your "Program Baseline Sprint" field to match distinct keys in the table, and use that for the get() call.

One more thing...if you are trying to iterate over the values of a field (like a list of labels) and then access the Lookup Table, that will not work due to scoping limitations of iterators.  Please let me know if that is what you are trying.

Kind regards,
Bill

[Joseph HANI]

Hello @Bill Sheboy 
Thank you very much for your answer that help a lot I have tried to match the exact value but still not getting how to iterate through the label field (dizzyface) 

One more thing...if you are trying to iterate over the values of a field (like a list of labels) and then access the Lookup Table, that will not work due to scoping limitations of iterators.  Please let me know if that is what you are trying.

Not sure, but it looks like that's what i'm trying to do ... 
This field "Program Baseline Sprint" is a label field that can have multiple value. Let say we have Data and Digital 
The labels for Data will be : DATADE-...
The labels for Digital will be : DIGIDE-... 

But the only interesting part for me is "DE-..." 

If in my lookup table I need to match the exact expression and then in the get() iterate through the labels to find the right one, how I can be able to say that's it's only the end part of the value that i want to check ? 

Does that mean that I need to create as much automation as "DE" I have, to match the exact value ? 

And another automation but with "DIGIDE" instead of "DATADE" ? 

Do you think it is possible to just say : 

And then do get() labels matching ? 
like this : 

{{DeliveryTargetStart.get(issue.fields['Program Baseline Sprint'].match("^.*DE-PI25Q1.*$").get(0))}}

Thank you very much for your help ! 

[Bill Sheboy]

Thanks for that additional information!  Let's try to clarify some things to learn how-configurable of a solution is needed...

#1) Is there a date formula which can be used to determine the Delivery Start Date and Delivery End Date?  Assumed Answer: no, and that is why the lookup tables are used.

#2) From what you show, I hypothesize that Lookup Table changes yearly.  Is that correct, or is it more frequently?  Assumed Answer: table changes yearly.

#3) Will that format for the DATA... labels remain stable, with only the year and quarter possible changing?  Assumed Answer: yes.

#4) How many results do you expect from the attempted lookups: 0 or 1, 1-and-only-1, more than 1, or don't know?  Assumed Answer: 1-and-only-1.

#5) Did you confirm that the smart value for the "Program Baseline Sprint" field is correct?  Assumed Answer: no, and so this how-to article will help find the correct smart value or custom field ID: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/ 

With those assumptions, we could break this problem down into parts: update the lookup tables, find any matching labels, and then use the (hopefully) only-one-match for the get() call in the table.

Update the lookup tables

Change the lookup table keys to only have the part you show without the DATA or DIGI prefix.

Find any matching labels, storing the result in a variable

• action: create variable (to match and extract the relevant part with substring)
  • name: varFoundLabel
  • smart value:

{{issue.yourSmartValueForProgramBaselineSprint.match("(.*DE\-PI.*)").substring(4,15).join(",")}}

Please replace the smart value and update the regular expression, as needed to match your labels.  I recommend testing with some writes to the audit log.

• smart value condition: to check that there is one-and-only-one
  • first value: {{varFoundLabel.split(",").size}}
  • condition: equals
  • second value: 1

Use the value to get() the match

• action: edit issue to set the fields with the lookup result
  • {{DeliveryTargetStart.get(varFoundLabel)}}
  • {{DeliveryTargetEnd.get(varFoundLabel)}}

Please review the above and let me know what you think.

[Atlass Monster]

Hi mate. Your question is well constructed but I still struggle to get two things:

-Do you set the date by hand in the automation for every delivery or you get it from somewhere via automation? 

- and why you need to match the value inside the table?

[Joseph HANI]

Hello Atlas ! 
Thank you for your answer ! Here is some more information

1. We set up the date value manually inside the lookup table since we know for the year our sprint date 

2. Since the field I'm checking is a label field, and we can have multiple labels inside this field. Here in the example it is DATADE / DATAAN, but we also have many more and the structure is mostly the same, they all end with "AN" or "DE" and the PI number + iterations

And I also want to be sure that anything before "AN" or "DE" is not check because it will mean that I will need to create multiple automation for the exact same needs and then admin those multiple automations every time there is a change or new dates sprints