Hi there,
I'm not sure where this goes, or even if it's appropriate for this forum.
Currently, I'm trying to develop a script for a Sharepoint workflow to compare an entry in a sharepoint list (list a) to multiple fields in another list (list B), ideally without adding another field on list A. We're trying to provide a means of entering our agent identification numbers in our help desk system, then cross referencing those numbers with another list to obtain an email address associated with that number. The trick is that we're comparing one field in list A with other fields in list B. I'm trying to provide a fairly straightforward way to do this, and it's been suggested that I use regular expressions.

I'm willing to propose using a regular expression to capture MOST entries, filtering out the ones that don't fit for manual entry, since we'll have to do this in another part of the workflow. I'm fairly unfamiliar with regular expressions, though.

List B's fields for comparison are two:
  • Agent Number: An ascending numerical field.
  • Branch Code: A one-to-many association with Agent Number. Always contains an "M" for each Agent Number, but can contain records "1", "2", "3", etc as high as necessary for that agent's organization.


We've entered data into the single field in list A (which more closely represents our company's standard) inconsistently. Some examples are as follows:
000649-01
00064901
64901
649-01
649-M
649M
649
?
649: ABCD description

Generally, if there is no -M, -01, or M at the end (i.e. 649) then it's assumed to be M.

As you can see, there are a number of different formats. I'm particularly concerned about how we're going to determine if something like "64901" is an agent number - it might be, and it might not be. If it's not, that will be easy enough to handle - add it to a list for manual entry. If it is, then is it the right one? Is it a branch?

I'm thinking we may need to change the way we enter the numbers, but since I'm not sure what the limitations are around regular expressions, I'm not sure what change we'll need to make or how, in Sharepoint, we can validate that the "standard" way of entry is functional.