Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

(csv, xls, xlsx) as replacement for 'Confiforms Rules for Field Definition

Edited

Hi everyone,

I have a question that I'd really appreciate any help with as I have Googled intensively to no avail: 

 

Here is where I got stuck (and desperate for a solution after): I 'hardcoded' different 'Confiforms Rules for Field Definition' macros for different responses. 

 

is there a way that I can upload this excel file with each row to be used in place of my Confiforms Rules for Field Definition macros? Thus, the excel file becomes a source for the rules or Confiform filters.

 

 

Best Regards,

Jasper 

 

 

1 answer

1 accepted

I am sorry to hear that - 37 rules!? wow!

I believe what you really need is just ONE rule that uses "lookup and set" action

And you need to define your answers in a separate form that you can then "look up" based on the conditions.

And yes, you can even skip the "lookup form" and use the excel directly https://wiki.vertuna.com/display/CONFIFORMS/Using+Excel+files+as+sources+for+ConfiForms+fields and use "lookup and set from web service (WS)" action

 

Alex

Hi Alex,

 

Thank you so much for your reply - you're like everywhere, helping everyone (like Superman, haha)

 

I have tried the lookup and set action but after inputing the 5 fields individually to get a unique Response (each unique response is in a row), my lookup brings up all the results of the source form (so All rows of responses) in the output field of the tool form. I have a feeling it's because my values to set is "Output=[entry.Response]" which might be giving me all the Responses in the source form. Either that or my condition (!field1a:[empty] AND !field2a:[empty] AND !field3a:[empty] AND !field4a:[empty] AND !field5a:[empty]) is wrong. 

 

Wish there was more documentation online, but i'm trying to figure this out. Thank you for the ideas you shared earlier

So, your condition for the rule should only do the lookup when all the options are set (not empty) and the lookup should return just one result (one match)

And you need to have a "filter to apply" on the dataset used for lookup. Do you have that?

Screenshot 2020-12-16 at 11.21.41.png

Hi Alex,

 

for those 3 fields, i've set them as follows: (any field with an 'a' following it is from the output page, and any without like those with 'entry.' preceeding it is from the source page. I've changed the condition from being not empty to the output page matching the source page.

 

Condition: 

field1a:[entry.field1] AND field2a:[entry.field2] AND field3a:[entry.field3] AND field4a:[entry.field4] AND field5a:[entry.field5]

 

Values to set: Output=[entry.Response]

 

Lookup Filter: 

field1a:[entry.field1] AND field2a:[entry.field2] AND field3a:[entry.field3] AND field4a:[entry.field4] AND field5a:[entry.field5]

 

but now the Output field just displays all 'Responses' in my source page.

 

Would you be able to provide any greatly appreciated advice as to what I am inputting wrong? can't seem to find anything on Google and YouTube

Have a look at this demo http://recordit.co/H87IhBeGte 

 

And the storage format for forms from the demo

 

<ac:structured-macro ac:macro-id="751d129c-7415-46bc-8a25-5e02378ed66e" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">myform</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="911798f0-b37b-4ea0-9890-d68ff4e7e362" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field1</ac:parameter>
<ac:parameter ac:name="fieldLabel">field1</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="ee448934-27b3-4744-9a86-dd8655e5005b" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field2</ac:parameter>
<ac:parameter ac:name="fieldLabel">field2</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="2163981e-d16f-460f-8cc2-2c9905078d8d" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field3</ac:parameter>
<ac:parameter ac:name="fieldLabel">field3</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="e50a224e-ec18-44fb-b48f-4352f2d00c07" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field4</ac:parameter>
<ac:parameter ac:name="fieldLabel">field4</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="4c6cf6cb-baad-40a0-82a1-fe37724b7960" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field5</ac:parameter>
<ac:parameter ac:name="fieldLabel">field5</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="d991f083-fe39-4a42-ade4-db43467ca984" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">lookupResult</ac:parameter>
<ac:parameter ac:name="fieldLabel">Lookup Result</ac:parameter>
<ac:parameter ac:name="type">read_only</ac:parameter>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<ac:structured-macro ac:macro-id="8c0e418e-3ec4-4ff9-b782-9d991a7f6452" ac:name="confiform-field-definition-rules" ac:schema-version="1">
<ac:parameter ac:name="condition">!field1:[empty] AND !field2:[empty] AND !field3:[empty] AND !field4:[empty] AND !field5:[empty]</ac:parameter>
<ac:parameter ac:name="fieldName">lookupResult=[entry.result]</ac:parameter>
<ac:parameter ac:name="values">field1a:[entry.field1] AND field2a:[entry.field2] AND field3a:[entry.field3] AND field4a:[entry.field4] AND field5a:[entry.field5]</ac:parameter>
<ac:parameter ac:name="action">Lookup and set value</ac:parameter>
<ac:parameter ac:name="actionFieldName">field1,field2,field3,field4,field5</ac:parameter>
<ac:parameter ac:name="values2">sources</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="6d64c8f4-cdc7-4d94-91a1-05410d0b2662" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:parameter ac:name="formName">myform</ac:parameter>
<ac:parameter ac:name="type">Embedded</ac:parameter>
<ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
</ac:rich-text-body>
</ac:structured-macro>
<ac:structured-macro ac:macro-id="839d9113-3121-4746-b488-25db17f6ae12" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">sources</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="02db84ee-3370-4877-8be3-428973a00ff1" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field1a</ac:parameter>
<ac:parameter ac:name="fieldLabel">field1a</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="bd941154-2015-4c47-ac69-cdf5ab960a82" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field2a</ac:parameter>
<ac:parameter ac:name="fieldLabel">field2a</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="e5072376-d7f3-4fce-94cd-16d3b243d45e" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field3a</ac:parameter>
<ac:parameter ac:name="fieldLabel">field3a</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="d5b63528-25ed-4e5f-902a-3ec54b530f72" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field4a</ac:parameter>
<ac:parameter ac:name="fieldLabel">field4a</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="db05969d-3a89-43f9-ae2a-3492d16f951c" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">field5a</ac:parameter>
<ac:parameter ac:name="fieldLabel">field5a</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="02169f93-498d-4424-8e8b-7df31f40d29c" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">result</ac:parameter>
<ac:parameter ac:name="fieldLabel">Result</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="4badb508-f17b-465e-af6d-92ae9aec6f95" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:parameter ac:name="formName">sources</ac:parameter>
<ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
</ac:rich-text-body>
</ac:structured-macro>
<p>Options in the "sources" form</p>
<ac:structured-macro ac:macro-id="8a9dfcc8-6bb4-4a0a-b3d8-bb5275962c71" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="formName">sources</ac:parameter>
<ac:rich-text-body>
<ac:structured-macro ac:macro-id="7c296e24-44ec-4d6b-af2f-ddf382f3dc62" ac:name="confiform-entry-edit" ac:schema-version="1">
<ac:parameter ac:name="compactMode">true</ac:parameter>
<ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<br/>
</p> 

Hi Alex,

I tried this the first thing when i woke up ; it works like a charm. Thank you so much for the video/gif, and especially the documentation. I found out exactly where I went wrong - for my sources equivalent, i imported my excel file as an API instead of creating a table within Confluence. I recreated your demo, then did the latter table creation, and it works. 

You're a Godsend. I really appreciate your help tremendously. 

Like Jasper likes this

Hi Alex,

It has been a while since I last reached out to you here for help. Sincerely hope that you're having a good year, and that you're well so far.

 

Alex, the 5 fields and their respective Lookup result are working well, but my stakeholders have asked me if I can generate Multiple lookup results in the output field (Lookup result in the Demo). I wanted to try it with one field first (field 1) and tried multiple field types like 'checkbox group'. The following simplified example is something that I tried:

 

The Options in the 'sources' form:

field1 field2 field3 field4 field5 Result

a            a       a         a         a         Hello.

b            a       a         a         a          Bye.

 

User-inputted 5 fields:

Field1 (checkbox group): a AND b

field2: a

field3:a

field4:a

field5:a

 

Targeted Lookup Result:

Hello. Bye. 

 

When i tried something like the aforementioned, the lookup result was completely blank. When I unchecked 'b' from field1, leaving 'a', the correct 'Hello' result was presented. Would this be because of my 'Lookup filter' (field1a:[entry.field1] AND field2a:[entry.field2] AND field3a:[entry.field3] AND field4a:[entry.field4] AND field5a:[entry.field5]) or my 'Values to set' (lookupResult=[entry.result]) ? I've tried defining the fields and/or options in the source page as identical as i could to the tool page but to no avail.

 

One of my stakeholders was imagining something like a 'For Loop' to keep searching for options in the sources form and presenting them in the output field until there are no matches left. Could this be a viable solution for Confiforms?

It is possible to use "join" function and construct the filter dynamically.

Here is what I mean: http://recordit.co/Rq5Ln1jDD0 

Like Jasper likes this

Hi Alex,

 

Sorry to get back to you so late. The join function worked amazingly, and i am now at the final step of this tool that I am trying to create for my stakeholders. They would like the Lookup Result with multiple results to additionally reflect any combination of the 5 fields that are not found in the sources form, as a default 'Approved'. In hindsight, i realised that this is possible the most important feature as there are possibly millions (calculate by a senior stakeholder) of other possibilities that a user could input, other than the 37 unique 'options in the Source form'. 

 

I did this previously (with the single-input of each field version of this tool) through 'Set value if empty' and 'LookupResult=Approved.' using the Rules for Field Definition, but that will clearly not work now. Do you think this can be done on Confiforms, Alex? 

I don't understand this comment, sorry

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Apps & Integrations

🍻🍂Apptoberfest Update: Upcoming Virtual Events 🎉

Hello Community! I hope you've been enjoying the 🍂Apptoberfestivities🍂 (I know I have!) The event is heating up next week with a series of virtual events that we're calling the 🍻🍂Partner App ...

97 views 1 6
Read article

Community Events

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

Events near you