0

I have a list of users that contains a lookup column for licenses. Each user can have multiple licenses so this column allows multiple selections.

The licenses list has a name and cost column. I would like to show the total costs of all licenses selected in the user list in a separate column. From what I understand, I can't reference the values of the lookup column in a calculated field.

I'd like to be able to update the license costs in the license list and reflect the updated cost in the "total license cost" column in the user list.

I'm reading that I can't reference a lookup column in a calculated field. Is this possible? Do I have to use a flow to populate and update the total license cost column or can this be done natively in lists alone?

1 Answer 1

1

You can't do this natively. Some alternatives:

  • A Flow that runs when an item is updated, and writes the cost to a separate field
  • A custom dashboard in a web part (spfx or .net) that does the math and writes the result to the screen (not saving it back to SharePoint)
  • An event receiver that performs the calculations and writes to a field.

If it were me, I'd go the web part route. Keeping data in sync in multiple places is bound to fail at some point and give you headaches. Also if the list get really big, say, more than a few hundred items, you're going to have performance problems, and your Flows might start failing. Then your data will be wrong. At least if your web part fails, you can show the user an error message.

SharePoint lists are really not good at supporting relational data once you get beyond very basic use cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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