0

I have a SharePoint list which contains a column which displays a Custom Item ID created by concatenating the ID field and the last 2 digits of the year (like this 01-21).

In some circumstances it is necessary to create a duplicate of an existing item. in this case, when the new item is created, the user inputs the original items Custom Item ID into a field called OriginalID to maintain a link between the copy and the original.

I want to use a Power Automate flow update fields in the original item when the copy is created. To do this I have created a calculated field which should 'reverse-engineer' the ID column from the OriginalID column by removing the last 3 characters (the hyphen and year digits) from the OriginalID.

This is what my formula looks like:

=LEFT([OriginalID],LEN([OriginalID]-3))

It is set to return a text string as is the OriginalID field.

when all the necessary fields have correct data input into them the calculated column returns a #VALUE! Error.

Can anyone assist with this problem?

1

Use any of the below formula, it should work for you:

=LEFT([OriginalID], LEN([OriginalID])-3)

OR

=LEFT([OriginalID], INT(FIND("-", [OriginalID])-1))

Output:

enter image description here

Microsoft documentation:

  1. LEFT function
  2. LEN function
  3. FIND function
1

It seems the formula shall be like =LEFT([OriginalID],LEN([OriginalID])-3)

The parentheses are not in the right position

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.