Is there a script to extract ProForma data into a regular or custom Jira field to query it in JQL

[Paul Romanus]

Hi - can ProForma data be scraped into a Jira field to allow it to be queried?

[John Funk · Accepted solution]

Hey Paul,

You can map/link a ProForma field to a Jira field, against which you can then run queries. I don't think you can scrape existing form data that hasn't been previously linked though. 

[Paul Romanus]

Thanks for the response John, we had seen that but had hoped to be able to avoid creating one to one mappings between ProForma fields and custom fields, instead noted that Proforma stored it's state in the issue's meta data, and hopted there was an example of accessing that in a post function ? or any other cunning way to populate an indexed field ...

[John Funk]

Maybe @andrewd can help answer that. 

[andrewd]

I'm Andrew, Support Engineer for ThinkTilt's ProForma product.

ProForma stores its data directly in Jira, without using any external database. The data is stored in Jira entity properties on the issues and projects in your Jira instance. ProForma accesses the data by using Jira's APIs. The forms and form templates are stored as JSON data structures. As yet, we don’t have a public API but it is on the roadmap.

What exactly is your business use-case for accessing the form data? Are you willing to share that? I see you mention Post Functions? We have had other’s who have extracted data and we have supported them to access their data via Scriptrunner etc.

Additional to John's response of linking ProForma form fields to Jira fields, there are some limitations with which in-built Jira fields you can link to. See more info about that -- as well as our suggested workarounds for it -- in our documentation linked here .

Nevertheless, you can create custom Jira fields and link to those fields in your forms for reporting purposes too. It should be noted that any Jira fields linked to a ProForma field will be searchable via JQL.

Currently in terms of data exporting, I thought I would also inform you of the ability to download form data in one of two ways:

1. Per form in an excel spreadsheet format
2. Download form data from all instances of a given form type in an excel spreadsheet format
   (It is important to note that with this second option, the tool is currently being refined to handle larger form volume capacities which is due for release soon.)
   
   We have customers who have used the spreadsheet download as the basis and exported that to another tool for processing.
3. We are also working on other methods for data exporting (such as an Answers Array) which are currently in development phase only and will come at a later stage. This will open up form data for access and usage in Automation

Does any of this info help?

Let me know how you go.

Regards,

Andrew.

[Paul Romanus]

wow, thanks for the reply Andrew - our use case is to implement a business impact analysis where there are 5 global regions - each with an high/medium/low impact, further split into the region's countries again with a high/medium/low impact and for all impacts to be reportable via JQL only. This could result in 80+ single select drop down ProForma fields with each having to be mapped to a Jira Custom field. If we had an option of a multi-select field to allow selection of highly/medium/lowly impacted countries this would reduce the number of custom fields mapped dramatically - however given Profields current functionality of storing a document's state in the entity.properties as you describe, we had hoped to be able to extract the individual countries' impact values into a set of Jira Custom 'multi-select' or at least indexed fields within a Post Function with Scriptrunner or similar, any complete/partial snippets of such code would be great headstart for us if you believe that route is appropriate. 

[andrewd]

Hi @Paul Romanus 

Thanks for providing the business context use-case - we're always interested to know how ProForma is trying to be used and applied, and whether we can adapt to satisfy particular business use-cases.

You're in luck - I should inform you that multi-select choice fields is actually an upcoming item that is currently in development and is aimed to be included in our next release, which sounds to be something you would find benefit in. What do you think - would that help?

Regards,

Andrew.

[Paul Romanus]

:) aimed :) ... yep understood, in good faith ... yes the multi-select choice list certainly appears to solve our issue. However we have to justify the Add-on now to our management/procurement.

We face two potentially major issues we hope the multi-select would solve:

1/ Our forms without it are unwieldy - even with e.g. conditional show/hide of sections

2/ The system performance impact that that number of Custom fields (80++) we would need to create and subsequently map to make the data queryable by JQL.

We could hopefully convince management/procurement to live with point 1/ until the new control is available. However the performance impact of having the additional custom fields could be a deal breaker for us, and once the Users approve it they will expect it in our Production instance - it's currently in a Dev/UAT  instance - in short order .. so .. back to the original workaround options until the multi-select control is available  .. can you shed any light on how to access the entity.properties data and scrape it into a single/fewer custom fields .. or even if that's a possibility or other automatable/scriptable options to expose the Form data? We do understand we've a somewhat square peg for a round hole for this use case and Jira. Thanks for your consideration

[Paul Romanus]

... longer term, could ProForms flag values embedded in the entity properties as per below:

https://developer.atlassian.com/cloud/jira/platform/storing-data-with-entity-properties/

The products have access to your properties.
This means that you can write JQL queries based on issue entity properties, enabling users to enjoy the power of JQL on search data that you have defined.

[andrewd]

Hi Paul,

In response to your question

"longer term, could ProForms flag values embedded in the entity properties"

Proforma's form data is already stored in Jira entity properties on the issues and projects in your Jira instance. If you have the Entity Properties app installed alongside ProForma, you'll be able to see the form data, structure, questions answers 

e.g. {{issue.properties."proforma.forms.i1".state.answers.2.text}}

where i1 is the first form attached, 2 represents question 2

However usage of this is limited as it does not work well with accessing answers to choice list questions, and hence why we have upcoming work on building an Answers Array to better access all form data fields and values.

That being said, I know it's not helpful right now knowing there are features on the way but not yet released (as you said you need to know what data access solutions ProForma has available in the market currently), but we also have some data download APIs on the way very soon that will provide features such as integration with JQL to allow a better search experience for users, and providing form data in JSON format as well as spreadsheet format if that helps.

We will endeavour to keep you updated when these features are released.

Regards,

Andrew.

[Paul Romanus]

Thank you very much for your time and detailed response Andrew, in itself that recommends Proforma/ThinkTilt. All info appreciated and noted. Regards

[Jeff Pittman]

@andrewd   Following up on this open feature, as I have business teams using JSM and they don't want to build & link Jira CFs for many items such as bank account information, or tax information.  just write it to the form and to the subsequent PDF.

However, like most Svc Teams the updates and requests they receive from client's have a target system such as a CRM, or a ERP that can accept bulk imports.  But doing 1 off XLS export, or in bulk then filtering the requests they need isn't very scalable.

What we need is the ability to bulk export from the Form Fields into a custom .xls or into a JSON - OR, ultimately have the ability for Form data/text to be reachable via API.

Can we at least get to the form-only (unlinked) fields via JQL now?

Cheers,

JP 

[Paul Romanus]

Hi Jeff, I just closed my ticket with Atlassian asking the same (access form only fields using jql) ... their answer was ... nope.

I'd seen in the dev api documentation that jira can be configured to index specific issue properties as in my post above (hopefully for us the form data), and subsequently query on them .. but Atlassian checked with the devs again, and ... no. 

In the end I used similar scripts to Jennifer Choban's answer, to dump all given form's data to a single custom field (one per form) in a csv format e.g. <question-key>:<answer>- from there we can use jql (or sql on a table in confluence - better) to support some query functionality. 

script: 

public static String updateIssueFieldWithFormAnswers(Issue issue, String formNameString, String targetFieldNameString, String fieldSeparatorChar, String recordSeparatorChar){
String baseurl = ComponentAccessor.getApplicationProperties().getString("jira.baseurl")
RESTClient jiraRESTClient = new RESTClient(baseurl)
String infoString = getInfo(ETBOT)
MutableIssue mutableIssue = (MutableIssue)issue
// Find the Form id that matches the formNameString parameter from the entity properties for the issue
// https://developer.atlassian.com/server/jira/platform/entity-properties/
String getIssuePropertiesUrlString = "/rest/api/2/issue/${mutableIssue.key}/properties/"

Map requestMap = [
contentType: ContentType.JSON,
requestContentType: ContentType.JSON,
headers: ['Authorization':"Basic ${infoString}"]]

try {
requestMap.put('path', getIssuePropertiesUrlString)
HttpResponseDecorator issuePropertiesResponse = (HttpResponseDecorator)jiraRESTClient.get(requestMap)
def forms = issuePropertiesResponse.data['keys'].find { key -> key['key'] == 'proforma.forms'}

if (forms) {
// log.warn(forms['self'])
requestMap.put('path', (String)forms['self'])
HttpResponseDecorator issueFormsPropertyResponse = (HttpResponseDecorator)jiraRESTClient.get(requestMap)
//log.warn(issueFormsPropertyResponse.data)
def form = issueFormsPropertyResponse.data['value']['forms'].find { key -> key['name'] == formNameString}

if (form) {
String formAnswersUrlString = "/rest/proforma/1/issue/${mutableIssue.key}/form/${form['id']}/answers"
requestMap.put('path', formAnswersUrlString)
HttpResponseDecorator issueFormsAnswersResponse = (HttpResponseDecorator)jiraRESTClient.get(requestMap)
log.warn(issueFormsAnswersResponse.data)
String serializedString = ""

issueFormsAnswersResponse.data.each{ answer ->
serializedString += (String)answer['questionKey'] + fieldSeparatorChar + (String)answer['answer'] + (String) recordSeparatorChar
}

ApplicationUser user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()
CustomField targetFieldCustomField = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName(targetFieldNameString)
mutableIssue.setCustomFieldValue(targetFieldCustomField, serializedString)
ComponentAccessor.getIssueManager().updateIssue(user, mutableIssue, EventDispatchOption.ISSUE_UPDATED, false)
}

}

} catch (Exception e) {
log.error(e)

}
}

[Mauricio Heberle]

We are facing the exact same problem here. If only there was a table where I could join the issue table with forms in my database, that would help already

[Pana Florin Ionut]

Hey, maybe I am 2 years late, but  I found this: 

Data Dump - ProForma Developer Documentation - Confluence (atlassian.net)

[John Funk]

Yes, I believe that is the same link that Jennifer listed above. 

[Robert Cook]

Were you actually able to get the console instructions to work? Following the documentation I just keep getting Null values.

[Jennifer Choban]

Hi everyone,

I'm with ProForma and I wanted to let you know that we've added some scripts to our documentation for dumping data from multiple ProForma fields into the Jira description field. You can find the Cloud script here and the Server script here.

[Bard Wang]

Hello @andrewd，

  Could  you or others please tell me the meaning of {{issue.properties."proforma.forms.i1".state.visibility}}

and

{{issue.properties."proforma.forms.i1".state.status}}

And how to change these values?

[Armand]

Is there any update on this issue ?

Thanks!