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

[Jasper]

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 

[Alex Medved _ConfiForms_ · Accepted solution]

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

[Jasper]

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

[Alex Medved _ConfiForms_]

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?

[Jasper]

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

[Alex Medved _ConfiForms_]

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> 

[Jasper]

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. 

[Alex Medved _ConfiForms_]

You are welcome!

[Jasper]

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?

[Alex Medved _ConfiForms_]

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

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

[Jasper]

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? 

[Alex Medved _ConfiForms_]

I don't understand this comment, sorry