Avoid duplicate values even when the form is in Edit mode

[Anonymous]

Hi All,

So, on the Report Name field, I have a label "Error 2- Report Name" whose Visible property is set to 
LookUp(Business_Requirement_Document, 'Report Name'='Value- Report Name'.Text, true) && (RH_SP_Form.Mode = FormMode.New)

What that does is- If the form is in new mode and if somebody tries to enter a report name that exists already, it shows an error which is controlled by the label's text property "Report name is already in use"

Now, what is happening is that if somebody goes to the form in edit mode by selecting any list item- and in the form changes the Report Name to a pre-existing value, it does not show an error because of the formula above having form mode set to "New". It then doesn't submit the form when we try to and shows the old values. How can I show the error here even while being in an edit mode.

If i am removing the condition (RH_SP_Form.Mode = FormMode.New) from the form- then it shows that error for every existing report even in view mode- which i don't want to see.

Kindly suggest!

@RandyHayes _ Can you please help resolve this?

[zmansuri]

Can you try adding the OR condition? like this:

RH_SP_Form.Mode = FormMode.New || RH_SP_Form.Mode = FormMode.Edit

[Pstork1]

Unlock the card that holds the report name and use the following to set the display mode of that card to View when the form is in Edit mode.

if(RH_SP_Form.Mode = FormMode.New,Parent.DisplayMode,DisplayMode.View)

That will keep users from editing existing Report names.

[Anonymous]

@zmansuri This did not work.

Because, when form is in edit mode- it is checking against itself and showing the error by default in edit mode

I should not check against itself but other values when in edit mode. What do you think?

[Anonymous]

@Pstork1 Great idea, you always have unconventional ways to help. I truly appreciate it.

I will pass on this idea to my lead. 

But I am expecting 'em to say that the report name should be editable in future even if it's once published and so the field should not be restricted to View only.

How should i proceed? I do not want to use the NOTIFY function on the form failure property as that would mean having 2 controls govern 1 error

[Pstork1]

You didn't mention what data source it is.  But if its SharePoint then you could do an error message and set the displaymode on the submit button to disable if you do a lookup on the report name and the ID of the list item in the lookup isn't null but doesn't match the ID of the record being edited.  That would indicate a duplicate report title.

[Anonymous]

I was not able to follow that unfortunately. Mind sharing the formula

[Pstork1]

Put the following in the error label visible property

With({Dup: LookUp(Business_Requirement_Document, 'Report Name'='Value- Report Name'.Text).ID},
If(
    Form1.Mode = FormMode.Edit &&  Text(Dup) <> DataCardValue2.Text ,
    true,
    false
))
#DataCardValue2 is the datacard bound to the item ID. YOu may need to add it, but it doesn't need to be visible.

If they change the Report Name to match any other entry then Dup will not match the ID for the current record, and it won't be null.  So when in Edit mode the label will show.  If they change it to be a unique value then Dup will be null and the error won't show.

[Anonymous]

Thanks!

Unfortunately, it did not work. With my earlier formula-
LookUp(Business_Requirement_Document, 'Report Name'='Value- Report Name'.Text, true) && (RH_SP_Form.Mode = FormMode.New)

It worked atleast when the form was in NEW mode, as described in my original question- now it does not work for both modes, NEW & EDIT

when I did this:

With({Dup: LookUp(Business_Requirement_Document, 'Report Name'='Value- Report Name'.Text).ID},
If(
RH_SP_Form.Mode = FormMode.Edit && Text(Dup) <> 'Value- Report Name'.Text ,
true,
false
))

With this formula, I do not see the error in green sectioned marked. Earlier, if upon hitting new somebody entered a Report name that already existed (as shown in orange)- even without hitting save the old formula was showing the error message in label that i have.

And, if I open an existing SP list item to edit it, it by defaults shows the error- seems like it is still checking the name against itself.

[RandyHayes · Accepted solution]

@Anonymous 
Change your visible formula to the following:

LookUp(Business_Requirement_Document, 'Report Name'='Value- Report Name'.Text, true) && ((RH_SP_Form.Mode = FormMode.New) || !(Parent.'Report Name' = 'Value- Report Name'.Text))

This will first lookup the record to determine if it exists.  If that is true, then it will be up to the form mode or the entered value not being the original value.

You do not need to duplicate true and false with an if condition for a true/false property.  It's just extra typing.

I hope this is helpful for you.