Validation for duplicate entries in a grid
Is it possible to show some kind of validations if I try to enter the same value twice.
Ideally I just want to avoid duplicate entries in to anaplan
Answers
-
Yes, the exact approach will vary depending on the circumstances. However, in short, I do this by creating a line item that contains a hard-coded 1 (numeric). Then, I sum that number (1) by the values I'm entering that can't be duplicated... if any of the sums > 1, then I know I have a duplicate. Its a bit circular, but in a 2nd line item, we can pull the summed amount and, if > 1, we can apply some conditional formatting, or indicate an error message.
Here's an example that may help provide context:
Let's say we are managing a task list. Part of the process is to assign each task to an employee, but each employee can only have a single task at a time. We need a control to make sure we don't over-assign employees! To assign the task, we have a line item in the task list (module) named "Assigned To" and its formatted to the Employee List.
- The manager goes down the task list and assigned each task to a employee.
- Although there is a way to filter the employee list (so that it only includes unassigned employees) each time an employee is assigned, let's pretend there isn't... and we just want to highlight/alert the manager if he/she tries to assign an employee to two tasks (shame on that manager!... giving somebody two tasks..)
- In a 2nd module, we summarize our "1" line item by the Assigned Employee Line item... and then turn around and publish that summary back to the record where the assignements take place. If the amount > 1 then (take the next step and apply conditional formatting to highlight the Assigned Employee Line Item RED because the employee has been assigned more than once.
The above example is very simple and there will be nuances, but the approach is similar regardless.
To filter the list (item 2) each time an employee is assigned, we do the following (just in case you wanted to know)
- In the module where we are setting our selections, create the following line items:
- Assigned Employee
- Status (map this to a status list that contains two items "Assigned" and "Unassigned")...fix this value to "Unassigned"
- Actual Status (map to status list and include a formula that defaults to Unassigned, unless there is a selection in the "Assigned Employee" line item, then set it to Assigned)
- Count (set to "1", format as number)
- Create a 2nd module based on the employee list and create the following line items
- Total Count (sum of Count, summed by Assigned Employee
- Status: If count > 0 the status.assigned, else Status.unassigned
- Populate the status value from the 2nd module into the Employee list... now it can become a selection dependency in the Assigned Employee line item from the first module. ... see https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Dropdowns/Many-to-many_Filtered_Picklists.htm
Let me know if any of this helps.
paul
0 -
Hi Paul,
Thank you for your prompt reply.
Could you please apply your logic for data in the attached screenshot.
where I have an Employee Number and I created a list item with hard coded value 1, we have got duplicate value of EMP002(occuring twice in the data).
How will implement your logic over here.0 -
Hi,
See attached example. this is Dependent Formatting. Thanks for the question, I've actually never used this before!
One thing to note about (my example): If you are using versions, this method of dependent filtering can only apply to one version (typically the Current Version, but it can be a different one). Most of my work is with FP&A models, so this is something I have to think about. The point is that, while the filtering is helpful, it might be useful to put a validation (e.g. Conditional Format, message, etc) in place (using most of the same constructs in the attached Word example).
Paul
0 -
Thank you so much for your help paul 🙂
0
