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

This widget could not be displayed.

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
Community showcase
Posted Thursday in United States

Local Atlassian Research Workshop opportunity on Sep. 28th

We're looking for participants for another workshop at Atlassian! We need Jira admins who have interesting custom workflows, issue views, or boards. Think you have a story to sh...

42 views 0 0
View post

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you