Solved

Turn Three Fields Mandatory if one of the three is NOT empty - in a repeating section

I
Userlevel 1
Badge +6

I have three fields, one choice field (drop down), one lookup field, and one multiline field in a repeating section. If anyone of the three fields are NOT empty, the other two fields need to also not be empty.


I have used IsNullorempty(FieldA) && !isNullorEmpty(FieldB)  || !isNullorEmpty(FieldC)

I have used not(isNullOrEmpty(Field A), not(isNullOrEmpty(FieldB), and not(isNullOrEmpty(FieldC) for each of the the fields

 

Although the rules appear to initially work, once I correct the fields (by entering the required data), I cannot get the errors to go away and am unable to submit the form.

 

Any assistance would greatly be appreciated.

icon

Best answer by Garrett 17 August 2022, 20:45

View original

20 replies

Garrett
Userlevel 6
Badge +16

Hi @isabellamai 


 


The Logic is either ALL is Filled or ALL is Empty

Garrett
Userlevel 6
Badge +16

Hi @isabellamai 


 



 


Use 2 Calculated Fields.


Field "AND" -> 



Field "OR" ->


 


 


When both the "AND" and "OR" are the same (either ALL FILLED or EMPTY), then its ok to submit form. When both the "AND" and "OR" are Different, invalidate the Form.


 


Cheers

I
Userlevel 1
Badge +6

@Garrett Thank you for the quick response.


The two calculated fields would be for each field, correct? Therefore, I would have 6 total validation rules, correct? Meaning, highlight Field A and add the below two rules, and repeat after selecting Field B and Field C?

Garrett
Userlevel 6
Badge +16

Hi @isabellamai 



You should have just 1 validation rules (AND == OR) and apply it to the 3 fields A,B,C 

I
Userlevel 1
Badge +6

So, I have to create two calculated fields:  1) AND 2) OR


- What is the formula for those two calculated fields? I do not often use calculated fields...sorry for the extra ask


- Will it matter if this is in a repeating cell section? I guess I will find out when I run the WF


 


Thank you in advance for your assistance.

Garrett
Userlevel 6
Badge +16

Refer above



 

I
Userlevel 1
Badge +6

@Garrett - still having issues, can you see what I may be doing wrong?


1, Created 2 calculated fields - note these fields are not connected to anything on the list:


  - 1st field has this formula isNullOrEmpty(FieldA-lookup field) && isNullOrEmpty(FieldB - choice field) && isNullOrEmpty(FieldC-mutliple line)


  - 2nd calculated field has this formula  isNullOrEmpty(FieldA-lookup field) || isNullOrEmpty(FieldB - choice field) || isNullOrEmpty(FieldC-mutliple line)


2. My nintex validation rule had to use the Item Property vice NamedControl used: ({ItemProperty:AND} == {ItemProperty:OR}) 


3. Form does highlight the required fields when one field is filled, and the other ones are not; however, once the corrections are made - the three fields are still highlighted in red and it will not let me save my form


 


Note, I did create 2 single line text fields on a sharepoint list -one for AND and one for OR - but was not able to connect the calculated fields with these columns. Wondering if it is because these fields are in a repeating section...this may be causing the issue? The true/false appears correctly - but still cannot save once those three fields are no longer empty - and the red box still appears on those three non-empty fields.


 


What am I doing wrong? Is there another solution for a repeating section?


 


Can you see if you can replicate this issue on your end?


 


 

Garrett
Userlevel 6
Badge +16

Hi @isabellamai 


Works fine. When True-True or False-False, allow to Submit


When True-False or False-True, will show Invalidate message



Calculated field AND



Calculated field OR



FieldA, FieldB, FieldC - Validation Rules



 


The Calculated Fields are not connected to SP List.


 

I
Userlevel 1
Badge +6

@Garrett - finally resolved. Thank you thank you Garrett.  I had to additionally name the calculated values and changed the rule to {AND != OR).  Summarizing it for others:


 


 - Add 2 calculated fields onto the form


 - Insert formula into 1st calculated field; however, also name this Calculated Value field as AND in the field control setting: isNullOrEmpty(FieldA-lookup field) && isNullOrEmpty(FieldB - choice field) -&& isNullOrEmpty(FieldC-mutliple line)


  - Insert this formula into 2nd calculated field; however, also name this Calculated Value field as OR in the control field setting: isNullOrEmpty(FieldA-lookup field) || isNullOrEmpty(FieldB - choice field) || isNullOrEmpty(FieldC-mutliple line)


- Use validation rule: (AND!=OR) ---- remember to use the Named Controls


 


 

I
Userlevel 1
Badge +6
Thank you again for your patience. I had overlooked a key item. I had not named my calculated value fields - so could not find them in the list of the Named Controls...lessons learned on calculated values.
Garrett
Userlevel 6
Badge +16

Great to hear that you manage to resolve the issue. 


You should always name your Form controls. 


 


Well done!

I
Userlevel 1
Badge +6

@Garrett Did you have to create a collection variable for the AND/OR calculated values and include it in the query XML step?  My WF didn't run like it should have after this change...thanks.

Garrett
Userlevel 6
Badge +16

@isabellamai the AND/OR Calc Field are simple temp controls to assist with the Rules validation.


However since they are part of a Repeating Section, their values are saved into a SP Multiline column.


 


When you add controls into a Repeating Section, this will change the XML structure.


So, you will need to relook at the saved XML data and your Query XML actions.


 

I
Userlevel 1
Badge +6

@Garrett Do I treat the "temp controls" like I treat the other fields in the repeating section e.g, Query XML and Return results into a collection and then use a Collection Operation to store result in a variable? 


If not, what do I need to do?


 


Should I start a new HELP subject?

I
Userlevel 1
Badge +6
Or is there an alternative nintex form rule that you can recommend that won't require a calculated field?
Garrett
Userlevel 6
Badge +16

@isabellamai , are you ok with Query XML and XPath Query?


If YES, return results into a collection and then use a Collection Operation to store result in a variable.


If NO, and you need help with query XML then open a new question.  


 


I would fill and submit 1-2 forms with repeating section. 


Then I would extract the XML from the SP column.


then, Use a XML Formatter or XML Beautifier to structure the XML. 


Once I have the structured XML, its easy to build the XPath Query.


 


Hope that helps


 


 

I
Userlevel 1
Badge +6

@Garrett My workflow used Query XML and the Collection Operation prior to the calculated value change. It ran fine. I added those two additional fields in the above steps (and treated it like it was a normal sharepoint list field).


First action - Query XML:


Output field: //Items/Item/AND - store results in cAND (collection variable)


Output field:  //Items/Item/OR - store results in cOR (collection variable)


 


then add the Collection Operation:


Target collection: cAND


Get


Index: vIndex


Store result in: vAND (variable)


 


Target collection: cOR


Get


Index: vIndex


Store result in: vOR (variable)


 


The WF history, shows that the workflow ran fine; however, the last step of my workflow is to update the title field with several variables e.g., vOrg- vCreatedByDisplayName  The title appears for the original/first item, but does not appear for any additional items added in the repeating section. 


WF history shows it completed but I get an "Error Occurred. Unable to preview workflow". I can see the detailed view but not the preview workflow.

Garrett
Userlevel 6
Badge +16

Hi @isabellamai 


As I said, you need to check all your Query XML actions. 


Use Log to History to output the values of the variables.


 


Use Log to History to display all the variables used to build the Title.

I
Userlevel 1
Badge +6

Thank you - I got it to finally work.

Garrett
Userlevel 6
Badge +16

Hey @isabellamai , well done. You are an accomplished  developer as you manage to tame advance Nintex Workflow subject such as Collections and Querying XML.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings