I'd like to set several fields by looking up values form an external data source. These values all depend on one exisitingvalue. It seems the Parent Field is ideally suited to this. However, Parent Field box is always empty when trying to configure a field with an external data source.
To state the problem more simply, I would like to use the Project Name (a field we set manually) to look up and set several other fields from an external data source automatically. Once we set the Project Name field all other fields should be set without manual intervention. Is this possible?
I am using an external mysql database.
The parent field has a query like so:
pkey, pname from project
and the child field has a query to the external database like this:
SELECT value, value, name FROM semantic_data WHERE property = "Data_Type" AND name = "@value";
If I type in a real value in the @valuepreview box (under this SQL staement) it returns real data as I would expect, as an example:
When I edit the fields in a real project as soon as I set the parent field I can see the child field sort of blink like it's updating but it does not get a value. It is blank.
When entering the SQL for a field and rtying to use the Preview function one must put quotes around @value, like so:
SELECT myval FROM semantic_data WHERE property = "Data_Type" AND name = "@value";
If you do not the Preview fails with some error.
However, in order to get the value passed to the field upon a real query you must NOT have quotes around @value:
SELECT myval FROM semantic_data WHERE property = "Data_Type" AND name = @value;
This works. Leaving the quotes in causes extra quotes to be sent in the real query, which makes the select statement fail.
Hello, we have in the product backlog an idea to include global variables in the profields project tab that you can use inside your query to filter the results, like "projectKey".
Until we implement that and if I understand you, you can follow these steps:
- Create a list field.
- Make it as an external list.
- Leave the connection field empty (It means that you'll use Jira database to fill the field).
- In the query field, write "pkey, pname from project".
Then you can create the rest of fields. Don't forget to mark all of them as external list fields because a parent field should be as the same type as its children. In the "parent field" field, please select the field created previously.
Mark as multiselect and automatic.
In the query, you can use "@value" to filter the results with the first column of the parent field query (in this case "pkey").
If you have a database table with name "technology" with the columns "projectKey", "technologyId" and "technologyName" you should write in the query:
"technologyId, technologyName, projectKey from project where pkey in (@value)"
Thanks for the quick response. Following your instructions I can get this to work as expected while in the Field Manager with the Preview button. I created a parent field that retrieves the project name. This works fine, the project name gets filled in. Then I created a child (same list type) which uses th paent, added a SQL query following your details and hit the preview button. The proper values are returned in the preview screen.
When I try to set fields in a real project though, I am able to select the project name from a list (the parnet field) but once set and saved the child field never updates. When is this supposed to happen? That is, when a field has an external source and it is based on a parent field value, when will the query get run and set this child field value?
By the way, this little tidbit is very critical:
"a parent field should be as the same type as its children."
I would not have expected this. It shoudl go in the documenttion somewhere.
On the next Profields release, 4.2, you will be able to use this new variables:
@projectKey to get the key of the project where the project field is used.
@projectId to get the id of the project where the project field is used.
Hi Atlassian Community! This is Teresa from the Jira Service Desk team. On June 19th, I’ll be hosting a webinar “Asset Management: The Role of Context in ITSM” with Atlassian Partne...
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