InfoPath Dev

 Hello, I have a bit of a unique situation that I'm trying to figure out and I hope someone might be able to help me.

 I am creating a list/form that will automatically calculate a value based on various column entries and render the results in a calculated column.  Each column in the form generally has two versions of it self, one for the data selection on the form, and one that runs a formula on the data selection numbers and pulls out only a specific character from the column selection via a MID formula.  The final calculated column then concatenates each of the MID columns into one long text string.  I need to on form submission to check the entered data fields against the calculated columns to ensure there are no duplicates.   

 For Example

 Column 1(Long string text name)

Column 2(MID formula that pulls in first two characters of column 2)

Column 3(Long string text name)

Column 4(MID formula that pulls in first two characters of column 3)

Column 5(Calculated column with Concatenate of Column 2 and 4)

 Column 1 & 3 are drop downs on the Form and when submitted column 5 calculates.

I'd like to add a step during form submission that compares what is about to be submitted to the current data in column 5 and returns an error if there is duplication, which will then prompt the user to populate a 6th column that will concatenate in column 5 to provide a unique value. (basically create a revision number to the original duplicate number)

 Any help would be appreciated!  

 

 

 

So in your list form, do users manage multiple items? That is, is the main data source data node repeating?

Yes, that is correct. There are multiple fields in the form, all from the same data source. I do have some secondary data sources to do some conditional filtering based on selections in specific fields.

I think that as part of your submit rules, after your value for column 5 is calculated, you'll need to query the list itself for any items w/ the same value in column 5. But if column 5 is an actual calculated column in SharePoint, I don't think you'll be able to query it with a normal list query - so I'd suggest that be a normal string column. Then in a secondary data connection (to the same list you are submitting to) you can set the query field for column 5 to the current item's value, query, and if any data is returned, not run the rest of the rules. Maybe set another field to display a message to the user....