It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

when setting up the query in DBCF, is there a variable that holds the current selected value?

I am trying to set up my dbcf select list such that I can archive values so they no longer show up in the list, but if an issue is edited and it references one of these archived values, it still will allow it in the drop down. 

What I am wondering is if there is a variable I can use for this? Something like

Select PK, DisplayValue from table where archived is null
union
Select PK, DisplayValue from table where archived is not null and PK = ${currentvalue}

or am I going to have to develop the query such that it looks up the existing values based on the issueid?

1 answer

1 accepted

0 votes
Answer accepted

Hi Jeff.

 

We don't have a pre-defined variable for current value, but you can use the custom field id in the configuration query.

Example:

In the CF with id customfield_10000(your id is probably different), the script will be:

Select PK, DisplayValue from table where archived is null
union
Select PK, DisplayValue from table where archived is not null and PK = ${customfield_10000}

Beware! This can lead to unexpected errors!

Example:

Let's say that I have a table with 10 records, with their PKs between 1 and 10 and I'm using the following script:

select PK from table where PK != {customfield_10000}

If you select the value 10, the list of possible options become 1-9 and 10 is not a part of this list. If you're ok with that, feel free to get and use the current value using the CF id. We may add the currentvalue variable at a later time after we analyze the possible risks.

 

Best regards,

Silviu

Thanks Silviu.  Using the customfield Id doesn't seem to work as it is only set it if the value is in the list.  My problem is that I don't want the value in the list, unless it was previously saved before the option was set to archived.   

I did manage to hack up some SQL that will work but it gets messy fast.  This doesn't account for "what if the user selects a new parent id' yet..  I am thinking that this is more trouble than it is worth.

 

SELECT PK, FEILDVALUE from TABLE Where ARCHIVED IS NULL
UNION
SELECT TO_NUMBER(SUBSTR (TEXTVALUE,0,INSTR (UPPER (TEXTVALUE), '_PK_',1,1)-1)) as PK  
              , SUBSTR (TEXTVALUE,INSTR (UPPER (TEXTVALUE), '_PK_',1,1)+4, LENGTH(TEXTVALUE)+1  ) as FIELDVALUE
FROM
    (SELECT regexp_substr(TEXTVALUE,'[^###]+', 1, level) as TEXTVALUE
    FROM
           ( SELECT  CFV.TEXTVALUE
             FROM  ( select dbms_lob.substr( TEXTVALUE, 3900, 1 ) as TEXTVALUE, ISSUE, CUSTOMFIELD
                    FROM CUSTOMFIELDVALUE
                    WHERE CUSTOMFIELD = 20210) CFV,
                  JIRAISSUE JI
            WHERE JI.ID = CFV.ISSUE and JI.PKEY = {key} ) CFV
    connect by regexp_substr(TEXTVALUE, '[^###]+', 1, level) is not null )

 

 

 

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Align

Best Practices in Jira Align

Hello Jira Align customers! In order to better serve you on your journey with Jira Align, the Community team has worked with the Jira Align team to curate the content in this collection to better s...

66 views 0 2
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you