ConfiForms: Aggregation of two Forms

[Jens Kempf]

Hello, 

I didn't find a way byself to solve my problem. So, now I will ask the community. :) 
The problem: 

I have two forms. Both forms have one field with the same configuration, like a grouping field - "ID":
form1 - metadata 
form2 - requirements 

For each entry in the form "metadata", there can be multiple entries in the form "requirements". 
Now i would like to merge the two forms by the field "ID".
The result should be look like: 

I had try many ways with the "TableView Merger" but I didn't find the right way. 

Can you help me? 

thank you. 

[Alex Medved _ConfiForms_ · Accepted solution]

Hi @Jens Kempf 

Little demo with 2 options I can suggest (might be more as always with ConfiForms..., but here are two of them)

(video)

https://wiki.vertuna.com/download/attachments/178782502/Community%20-%20ConfiForms-%20Aggregation%20of%20two%20Forms.mov?version=1&modificationDate=1733323228343&api=v2

Storage format for the reference

<ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="48bb4df4-1d6c-4645-a4bf-38178700da68"><ac:parameter ac:name="formName">metadata</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="9e5ed4a2-f631-4b3b-8f4e-31ebd0740f31"><ac:parameter ac:name="fieldName">projectID</ac:parameter><ac:parameter ac:name="fieldLabel">Project ID</ac:parameter><ac:parameter ac:name="values">CR###:</ac:parameter><ac:parameter ac:name="type">text</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="fd2f498c-a311-412e-a3ea-3275b35c0fe7"><ac:parameter ac:name="fieldName">Project</ac:parameter><ac:parameter ac:name="fieldLabel">Project</ac:parameter><ac:parameter ac:name="type">text</ac:parameter></ac:structured-macro></p><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="1401b115-cc3b-445c-97ec-f2dbae609a8c"><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p></ac:rich-text-body></ac:structured-macro><p>All in metadata</p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="f8044c9a-3603-47dc-b7bc-b530f71ca4b2"><ac:parameter ac:name="formName">metadata</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p><ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="0e197855-0c11-40c8-a867-c9eba50df25a"><ac:parameter ac:name="formName">requirements</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="448c4ca1-1f69-4cf5-8af8-148b4d31f258"><ac:parameter ac:name="fieldName">projectID</ac:parameter><ac:parameter ac:name="fieldLabel">Project ID</ac:parameter><ac:parameter ac:name="values">CR###:</ac:parameter><ac:parameter ac:name="type">text</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="e23ebde8-a65e-4978-9a24-9f80d179de6c"><ac:parameter ac:name="fieldName">Requirement</ac:parameter><ac:parameter ac:name="fieldLabel">Requirement</ac:parameter><ac:parameter ac:name="type">text</ac:parameter></ac:structured-macro></p><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="9baf5cb2-794f-440e-925f-be778f3c5256"><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p></ac:rich-text-body></ac:structured-macro><p>All in requirements</p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="67861b9a-482c-4881-9791-e5fbbdfe5387"><ac:parameter ac:name="formName">requirements</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p><p>TVM - TableViewMerger</p><ac:structured-macro ac:name="confiform-table-merger" ac:schema-version="1" ac:macro-id="a6c3cc2f-dd01-4395-a386-f50d4e91052f"><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="1d8591c5-9142-4ad6-a779-ee922959becc"><ac:parameter ac:name="fieldName">projectID</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="c20ab9ce-61bf-4752-adf3-9c2ed5a0999e"><ac:parameter ac:name="fieldName">Project:(&quot;[entry.Project]&quot;)</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="c3bcac2b-5324-4268-99e9-b21baa73a54b"><ac:parameter ac:name="fieldName">Requirement:(&quot;[entry.Requirement]&quot;)</ac:parameter></ac:structured-macro></p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="ee2c40ee-1a16-445a-8353-5611218a37af"><ac:parameter ac:name="formName">metadata</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="6232ccb7-8866-4ebf-a784-184b25e03c8d"><ac:parameter ac:name="formName">requirements</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p><p>Using queryAndRender function</p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="14d09ef7-9439-4d7a-8393-596413a02b84"><ac:parameter ac:name="formName">requirements</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="4d6967d2-f20e-4a22-8617-23f1a5805a23"><ac:parameter ac:name="fieldName">projectID</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="ef84e9e6-b902-43d8-96c9-a8d8db22a3b1"><ac:parameter ac:name="overrideLabel">Project</ac:parameter><ac:parameter ac:name="fieldName">id.queryAndRender(metadata:@self;projectID:[entry.projectID];Project;list)</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="641785e5-eba1-4b18-af4c-93e7674257c1"><ac:parameter ac:name="fieldName">Requirement</ac:parameter></ac:structured-macro></p></ac:rich-text-body></ac:structured-macro><p><br /></p>

Hope it helps

PS, it could have been so much easier of you use smart fields to link the forms (having a smart dropdown to metadata form from the requirement form, for example)

Alex 

[Jens Kempf]

Hi Alex, 

thanks alot for your quick answer. 

I would like to user the tableView for an export later. 
So I need every result in a own row of the table. 
The tableView Merger puts the results in one cell, thats unfortunately not usefull for my usecase.

The solution with virtual function "queryAndRender" works fine with my usecase - thank you! 

But, if I do an .xls-export of my tableView, the Headline of columns shows the completly  String of the "fieldName" -> "id.queryAndRender(metadata:@self;projectID:[entry.projectID];Project;list)". Is there a way to use own headlines for the columns? I'd already given an own Label for the field. 

I think the implementation of the solution above is more complicated than use smarts fields in the requirement-form... :) 

[Alex Medved _ConfiForms_]

You can have own labels for column headers

[Jens Kempf]

Oh, this is an easy way to set own headers.. I could have thought of it myself - Thank you. 

Sorry, but I still have one more question:
The solution with smart fields and "Lookup and set value" works fine on new entries in my form "requirments". 

But how can I update existing entries? I think I should us an IFTTT:

Event: "on modified"

Choose Action to perform: "Update ConfiForms Entries by filter". 

Parameters to set: Project=[entry.id.queryAndSet(metadata:<PAGEID>;projectID:[entry.projectID];Project)]

It dosn't work. Did you have an idea for the right syntax?

Thank you. 

[Alex Medved _ConfiForms_]

Sorry, what do you want to update? 

I dont quite understand what is this for

Project=[entry.id.queryAndSet(metadata:<PAGEID>;projectID:[entry.projectID];Project)]

[Jens Kempf]

Now I had set smart field in the form "requirements" that reference to the form "metadata". That's working fine when I create new entries in the requirment-form". 

But, how can I update the existing entries in my form "requirements"? 

<ac:structured-macro ac:macro-id="48bb4df4-1d6c-4645-a4bf-38178700da68" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">metadata</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="9e5ed4a2-f631-4b3b-8f4e-31ebd0740f31" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">projectID</ac:parameter>
<ac:parameter ac:name="fieldLabel">Project ID</ac:parameter>
<ac:parameter ac:name="values">CR###:</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="fd2f498c-a311-412e-a3ea-3275b35c0fe7" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Project</ac:parameter>
<ac:parameter ac:name="fieldLabel">Project</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="1401b115-cc3b-445c-97ec-f2dbae609a8c" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>All in metadata</p>
<ac:structured-macro ac:macro-id="f8044c9a-3603-47dc-b7bc-b530f71ca4b2" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="formName">metadata</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<ac:structured-macro ac:macro-id="0e197855-0c11-40c8-a867-c9eba50df25a" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">requirements</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="448c4ca1-1f69-4cf5-8af8-148b4d31f258" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">projectID</ac:parameter>
<ac:parameter ac:name="fieldLabel">Project ID</ac:parameter>
<ac:parameter ac:name="values">CR###:</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="385dc948-234d-4628-a3fb-4a0ac07d9014" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Project</ac:parameter>
<ac:parameter ac:name="fieldLabel">Project</ac:parameter>
<ac:parameter ac:name="values">[523220888|metadata|Project|true||]</ac:parameter>
<ac:parameter ac:name="type">smartselect2</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="2aa363cc-435b-40de-b5cc-58893d0dbb97" ac:name="confiform-field-definition-rules" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Project=[entry.id]</ac:parameter>
<ac:parameter ac:name="values">projectID:[entry.projectID]</ac:parameter>
<ac:parameter ac:name="action">Lookup and set value</ac:parameter>
<ac:parameter ac:name="actionFieldName">projectID</ac:parameter>
<ac:parameter ac:name="values2">metadata:523220888</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="e23ebde8-a65e-4978-9a24-9f80d179de6c" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Requirement</ac:parameter>
<ac:parameter ac:name="fieldLabel">Requirement</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="9baf5cb2-794f-440e-925f-be778f3c5256" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>All in requirements</p>
<ac:structured-macro ac:macro-id="67861b9a-482c-4881-9791-e5fbbdfe5387" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="formName">requirements</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<br/>
</p>
<p>TVM - TableViewMerger</p>
<ac:structured-macro ac:macro-id="a6c3cc2f-dd01-4395-a386-f50d4e91052f" ac:name="confiform-table-merger" ac:schema-version="1">
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="1d8591c5-9142-4ad6-a779-ee922959becc" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">projectID</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="c20ab9ce-61bf-4752-adf3-9c2ed5a0999e" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Project:("[entry.Project]")</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="c3bcac2b-5324-4268-99e9-b21baa73a54b" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Requirement:("[entry.Requirement]")</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="ee2c40ee-1a16-445a-8353-5611218a37af" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="formName">metadata</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<ac:structured-macro ac:macro-id="6232ccb7-8866-4ebf-a784-184b25e03c8d" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="formName">requirements</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>Using queryAndRender function</p>
<ac:structured-macro ac:macro-id="14d09ef7-9439-4d7a-8393-596413a02b84" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="formName">requirements</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="4d6967d2-f20e-4a22-8617-23f1a5805a23" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">projectID</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="ef84e9e6-b902-43d8-96c9-a8d8db22a3b1" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="overrideLabel">Project</ac:parameter>
<ac:parameter ac:name="fieldName">id.queryAndRender(metadata:@self;projectID:[entry.projectID];Project;list)</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="641785e5-eba1-4b18-af4c-93e7674257c1" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Requirement</ac:parameter>
</ac:structured-macro>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>

[Jens Kempf]

Hey, 
I have found the solution to update the existing entries. 

I need to use an IFTTT within the metadata-form to update the entries in the requirments-form. 

Thank you for your help! 

[Alex Medved _ConfiForms_]

I dont think using an IFTTT to do something like this is appropriate

It is usually used to set up a form that behaves in a certain way... not to do the "one-time" job

For one-time jobs you might be good to use the REST APIs

PS, do you know that with the smart fields you do not need to do the queryAndRender/queryAndSet anymore? You can access data from a referenced form directly

Alex

[John Funk]

Hi Jens - Welcome to the Atlassian Community!

Moving this to the App Central group for help with Apps.