SOLVED

Sharepoint library metadata adding a calculated field based on another lookup field

Copper Contributor

Hello all,

 

I have been trying to do this forever since is giving me a lot of issues, but I am not able to figure out how to implement.

I am storing in sharepoint all the invoices and managing the data for reporting through metadata.

I have a lot of validations but I would like to create one field that validate if an invoice is a duplicate.

The only to see that is if invoices are duplicated, the invoice number [Invoice Number] AND the supplier ID [Supplier Number]are the same. I wanted to create a field where I concatenate [Invoice Number]+[Supplier Number] so I can then find the duplicates.  Unfortunately the Supplier ID is a lookup field so I am not able to create a calculated field.

I have tried to create an additional column and try to get the data with JSON schema:

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "div",

"txtContent": "=[$Supplier Number.lookupId] + '-' + [$Invoice Number]"

}

 

Bit it doesn't work :(

What did I do wrong?

Thanks!

Marg

4 Replies
Column formatting with JSON will not work in your case. Since lookup column is not supported by design to be used in Calculated column, you cannot go that route either. Do you need validation in place to stop users from putting in duplicates or just need to know when a duplicate invoice is created? If it is later, then you can use a Power Automate to generate your unique token, compare it and then raise a flag when a duplicate is found. If you want to stop users from creating duplicates, you may need a custom solution.
Hello Bharath,

Thank you for your feedback on this. I think it would be just ok to know when a duplicate one is create so that we can action as soon as it is uploaded. Would you be able to help with the Power Automate I should be using for that? I have also tried that route, but I have not been able to build that either :D.
best response confirmed by MarM79 (Copper Contributor)
Solution

@MarM79 Not sure what are you trying to do with JSON, but you can use lookup columns in JSON without any problem. JSON formatting works with internal name of the columns. So, make sure you are using correct internal names for your columns. Follow this article to get internal name of your SharePoint column: Find the Internal name of columns in SharePoint Online 

 

Also, you would like to use the lookValue instead of lookupId to get the actual value like: 

 

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=[$SupplierNumber.lookupValue] + '-' + [$InvoiceNumber]"
}

 

DocumentationUse column formatting in SharePoint  


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Thank you so much ganeshsanap! It worked!!!
1 best response

Accepted Solutions
best response confirmed by MarM79 (Copper Contributor)
Solution

@MarM79 Not sure what are you trying to do with JSON, but you can use lookup columns in JSON without any problem. JSON formatting works with internal name of the columns. So, make sure you are using correct internal names for your columns. Follow this article to get internal name of your SharePoint column: Find the Internal name of columns in SharePoint Online 

 

Also, you would like to use the lookValue instead of lookupId to get the actual value like: 

 

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=[$SupplierNumber.lookupValue] + '-' + [$InvoiceNumber]"
}

 

DocumentationUse column formatting in SharePoint  


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

View solution in original post