More fun with table toolbox macro in Confluence

[Maria Del Rosario Gonzalez]

This is similar to a previous question I submitted a few days ago on 2/20 and was quickly answered.

Here are the specifics on this use case:

I need to flag a condition where I have at least two (and up to five) instances of a specific set of text values (Pattern1, Pattern2, Pattern3, Pattern4, and Pattern5) appearing in a single Jira labels field. 

This field also contains many other text values that I need to ignore in this case. 

For example:

Jira Labels field = Pattern1 value1 value2 textA Pattern2 -> Flag as Error

Jira Labels field = Pattern1 value1 value2 textA Pattern2 Pattern3 -> Flag as Error

Jira Labels field = Pattern4 value1 value2 textA Pattern1 Pattern3 -> Flag as Error

Jira Labels field = Pattern1 value1 value2 textA -> This is fine, no error

Is there a way to count how many times the Pattern that I am looking for appears in the Jira Labels field and then flag as an error if greater than 1? 

Many thanks in advance for pointing me in the right direction!

[Stiltsoft support]

Hi @Maria Del Rosario Gonzalez ,

As I remember, your table is coming from the Jira Issues macro, so you may use the Spreadsheet from Table macro for the case.

Seems that the 

=IF((LEN(A4)-LEN(SUBSTITUTE(A4,"pattern","")))/LEN("pattern")="1", "ok", "error")

function does exactly what you've described.

[Maria Del Rosario Gonzalez]

Any way to solve without using the Spreadsheet from Table?  I have some internal restrictions with this macro that prevents me from using it unfortunately.

Ideally using the table transformer with a custom transformation would be ideal.

Thanks in advance for all your assistance!

[Stiltsoft support]

Oh, I see then - the macro just gives a more user-friendly and familiar UI with calculations, conditional formatting, etc.

So, with the Table Transformer we can suggest the following query:

SELECT *,
CASE WHEN
MATCH_REGEXP('Pattern', "([\s\S]*Pattern[\s\S]*Pattern[\s\S]*)") THEN "Error"
ELSE "OK"
END AS 'Status'
FROM T*

Hope it helps.

[Maria Del Rosario Gonzalez]

Apologies for delayed response but I was not able to get back to this last week.

I'm still struggling with the pattern selection unfortunately.  Here are the specifics of my use case.

Just to recap, my goal is to identify any Jira record with duplicate labels such as of "AB-xSmall", "AB-Small", "AB-Medium", "AB-Large", "AB-XLarge".  If more than one of these appear in the label field, then I need to throw out error.  Otherwise, I need to translate the single instance of one of those labels to a number. 

What I have below (starting with the case statement) does not correctly identify the duplicates in one of the test records:

CASE WHEN
MATCH_REGEXP(T1.'Labels', "([\s\S]*AB-xSmall[\s\S]*AB-Small[\s\S]*[\s\S]*AB-Medium[\s\S]*AB-Large[\s\S]*AB-XLarge[\s\S]*)")
THEN FORMATWIKI("{status:colour=Red|title=Duplicate labels found}")
else

case WHEN MATCH_REGEXP(T1.'Labels',"AB-xSmall") then 50

WHEN MATCH_REGEXP(T1.'Labels',"AB-Small") then 200

WHEN MATCH_REGEXP(T1.'Labels',"AB-Medium") then 400

WHEN MATCH_REGEXP(T1.'Labels',"AB-Large") then 800

WHEN MATCH_REGEXP(T1.'Labels',"AB-XLarge") then 1600

else 0

END
End

My test Records and results are:

Record1 -> Labels: AB-Small, AB-XLarge Result: 200 [Incorrect & should be Duplicate]

Record2-> Labels: AB-XLarge Result:1600 [Correct]

Thanks in advance for your help!