Profields - Set fields from external data source based on existing field value?

[j v]

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?

[j v · Accepted solution]

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:

<th>Parent id</th><th>Id</th><th>Values</th>

BCP-10-Vincent	HiSeq	HiSeq

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.

[j v]

SOLVED:

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.

[Alvaro Aranda]

Hi,

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.

[carlos.fernandez]

Hi,

Yes, the external connection service replaces the word "value" with the string and adds the quotes before executing the query.

[carlos.fernandez]

It should be happen when you fill the parent field. Can you send me both queries? What database server are you using?

[j v]

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.

[carlos.fernandez]

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").

I.e:

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)"