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

[Jeff Louwerse]

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?

[Silviu Burcea · Accepted solution]

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

[Jeff Louwerse]

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 )