Geek Answers Handbook

How to create a calculated column with data from another list

I have the following situation: List A has two columns (name, sum) and in list B (name). I want to add a calculated column, which should be the sum of all the entries in list A that have the same name as list B. Example:

List A:

NAME Amount L0011 100 L0011 50 L0020 234

So, in list B, I want the computed column to show:

 NAME Amount L0011 150 L0020 234

How can I do that? Workflow (as soon as I add / change an entry in list A, update list B) or something else? thanks

+4
source share
3 answers

This cannot be done using computed columns, because computed columns can only be used for columns in a single list.

Using the SharePoint Designer workflows, you can simply use the Create List Item and Update List Item actions so that whenever a user adds a value for L0011, the amount will be added to another column of the list that already contains the previous amounts.

Let me know if you need a more detailed answer for the SharePoint approach, and I will give you step-by-step instructions on what to do.

+5
source

Lem.mallari's answer is a huge pain if you cannot assume that the amounts on List A never change, since they don't keep track of whether an item has already been added to the amount. It is not possible to process Workflow through a SharePoint list, which means that there is no easy way to calculate the sum or average number of list items.

The correct way to implement this will require some development. Training for SharePoint developers ( 2010 , 2013 ) will actually provide you with most of the path: the event receiver should fire when items are added or changed in lists A and B, which use the SharePoint API to navigate through list A and average values ​​by name, and then update everything (or just touched) elements in list B. Alternatively, you can use JavaScript to display the sum of all the entries in list A that have the same name as the element in list B while all the data is displayed on your page. If you are comfortable using XPath and InfoPath, you can add List A as a secondary data source to List B and select only the applicable items in List A for summarization.

But if we are talking about Workflows, then only the "workflow only" method is used here. This was verified and successfully completed in 2010. Create your own C list with the following columns:

  • Title (string, mandatory, forced implementation of unique values)
  • TotalItems (integer, required, default value 0)
  • Amount (number, decimal places, but you want, mandatory, default 0)
  • Medium (calculated, = IF (TotalItems = 0.0, Sum / TotalItems)) (optional)

Replace the Name columns in lists A and B with the search columns pointing to list C. Delete the Amount column in list B, instead include the Sum column as an additional column. Add the following columns to List A and make sure that users cannot modify them directly. This can be limited by creating InfoPath forms or by creating alternative viewing and editing forms.

  • Amount Archive (identical to quantity, default 0)
  • Amount HasBeenSubmitted (yes / no, default no)

Create a workflow to run every time an item is created or changed in list A. Use these commands (I use the list for readability, it became ugly when formatted as code):

  • If current item: amount is not equal to current item: AmountArchive
    • Set Variable: Item Count to (Data source: List C; Field from source: TotalItems; Search for list item: Field name; Value: Current item: Name (return field as: Search value (as text))
    • Calculate the variable: ItemCount plus 1 (output for the variable: ItemCount)
    • Calculate List C: Sum (similar settings as above, be sure to use the Lookup Value (as text), not String!) Minus Current element: AmountArchive (output for variable: SumWithoutValue)
    • Compute Variable: SumWithoutValue plus Current Element: Sum (Output to Variable: NewSum)
    • If current item: AmountHasBeenSubmitted is None
      • Set AmountHasBeenSubmitted for Yes
      • Refresh the item in the list C (Set TotalItems to the variable: ItemCount; Set the amount to the variable: NewSum; Find the list item in the same field: Name; Value: Current item: Name (return field as: Search value (as text))
    • Else
      • Update an item in the C list (do nothing with TotalItems; use the same logic to set Sum to Variable: NewSum)
    • Set value to current item: AmountArchive
+7
source

How about using DSum? https://support.office.com/en-us/article/DSum-Function-08F8450E-3BF6-45E2-936F-386056E61A32

List B

 NAME Amount L0011 =DSum("[Amount]","List A","[NAME]=''" & [NAME] & "'") L0020 =DSum("[Amount]","List A","[NAME]=''" & [NAME] & "'")
0
source

More articles:


All Articles