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.
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.
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.
|Malaysia||A||1st H1 FY2015|
|India||A||1st H1 FY2015|
|Singapore||A||1st H1 FY2015|
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.
- 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: