Enforce Unique Values for 3 columns in a list

  • 19 September 2017
  • 7 replies
  • 133 views
ArohShukla
Badge +9

Hi All,

 

I have designed a solution which has 4 lists as follows:

Users will fill in this HCM list as follows:



The requirements are that users should NOT be able Period the same period if that period has already existed in the list so I configured period column as “Enforce unique values” as shown:


This is sample of Nintex form that user will submit.

 

Region list single column as shown


Users start testing and found that the period field is okay, but when they choose the different region and different subsidiary for the same period like 2nd Half FY2017, where this PERIOD always exists, they get this message:





So I configured the Region column as “Enforce unique values” as shown:





and when I click OK button I get this message

I have not configured  subsidiary list yet for the “Enforce unique values”.

Q: How to achieve “enforce unique values” for 3 columns in a list or there is any other way to users to enforce users only when Region, Subsidiary and Period are NOT the same.

I found something related to enforce unique values at TechNet.

Any suggestion I could do to solve user’s issues.
Thanks, in advance.

Rhia Wieclawek‌  Lakshmi C‌  Tomasz Poszytek‌

7 replies

rhia
Userlevel 6
Badge +15

Hi there ‌

So I want to make sure I understand -- it seems like your desired goal is that if someone has already submitted another entry for (for example) Malaysia for 1st H1 FY2015, that someone else can't come along and also submit something for Malaysia for 1st H1 FY2015 - but they could submit for India for 1st H1 FY2015, or for Malaysia for 2nd H1 FY2015. Is that correct?

Does this also mean that subsidiary is controlled in the same way? Ie, could - if Malaysia 1st H1 FY2015 Subsidiary "A" has been submitted - someone else submit Malaysia 1st H1 FY2015 Subsidiary "B"? I assume yes.

L
Badge +9

Initially you set 'Enforce Unique Values' on 'Period' column alone, not for all 3 columns. After, When you try to set 'Enforce Unique Values' on 'Region' column by the time duplicates were already present in the 'Regions' list so only it throws error. In order to set 'Enforce Unique Values' first try to delete duplicate values exists in 'HCM' list.

If you want to allow values in 'HCM' list (Unique 'Period' values for different 'Regions' and for different 'Subsidiaries') like below you want to use 'JavaScript' coding (to check the combination of values exists in 'HCM' list or not).

RegionSubsidiaryPeriod
MalaysiaA1st H1 FY2015
IndiaA1st H1 FY2015
SingaporeA1st H1 FY2015
ArohShukla
Badge +9

Exactly Rhia Wieclawek This is the scenario ..

Does Nintex have any configuration that meets this above requirement? OR

else I need to write JavaScript that has suggested?


ArohShukla
Badge +9

‌ ..Thanks for your feedback ...

ArohShukla
Badge +9

Rhia Wieclawek‌ Lakshmi C‌ Tomasz Poszytek‌

I am able to use the JavaScript and REST to check if a user has filled the SAME value of Region, Subsidiary and Period  in my SAVE/Submit button in the Nintex Form as follows:

However,  if user fills the values exists in the list either for Region or Subsidiary or Period, a JavaScript message should be shown as, “The Region / Subsidiary / Period value exists in the form. Please adjust the values accordingly” and form Should not submit till user has not filled the appropriate values in the form.  

I have to tap something kind of event in the alert(“Exists”) as highlighted above,  so that user is NOT able to submit the form.

Q: How can I ‘cancel’ the Submit if a user has filled a duplicate value in the list?

Any suggestions would be greatly appreciated.    

L
Badge +9

Try to put all your validation code in a function 'fnValidation()' and invoke it using a validation rule. It will make the submit will triggered only after your validation succeeded.

E
Userlevel 5
Badge +14

it could as well be achieved following way with no javascript code

- create calculated list field that concatenates values of all the fields to be unique into single value (eg.  'Malaysia_IPOH_1FY2016')

- create calculated value control on the form that will concatenate form control values just the same way

- create validation rule with formula like:

not(IsNullOrEmpty(lookup('HCM','CalcListField',CalcControl,'ID')))‍‍

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings