locked
How to use the count related Lookup field in the calculations? RRS feed

  • Question

  • Hi,

    I have two list Excursion Event ( Parent) and List (Child).

    I am using a count related look up field on parent to calculate the total no of customers associated with any excursion event. The field name is "No of Customers"

    Based on some status field, I am using the SPD workflow to calculate the Valid Customer from the child list and then update it back to the parent list. The field name is "Valid Customers"

    Now I want to do some arithmetic on "No of Customers" and "Valid customer" field but seems like count related look up column is neither recognized in workflow nor in the calculated column. Every time I try to read the value of No of customer field, workflow through an error message.

    how can I use the count related look up field in the calculations?

    Fields on the Parent List:

    A=No of Customers (Look Up count related)

    B=valid customers (based on flag from the child list)

    C =% of Total customers-------> I want to calculate this field based on the above two field --> C= A-B

    Tuesday, December 15, 2015 3:36 AM

Answers

  • Hi parveenagg,

    As you have said that the record is counted in your parent list with the help of workflow.

    Then I suggest you set your workflows to start automatically when an item is changed.

    Or you can set another workflow to update the count in parent list when an item is changed.

    Hope it helps.

    Best Regards,

    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Friday, December 25, 2015 8:56 AM

All replies

  • Hi

    let me ask you

    A= No of Customers . is in parent list, type: number

    B =Valid Customers . in parent list also, calculated using  a workflow

    Right?

    please provide how you created the workflow and also the error message



    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Wednesday, December 16, 2015 7:49 AM
  • Hi Romeo,

    A= No of Customers . is in parent list, type: Look up (count related).

    B= Valid Customer, is in parent list and is number field. This field get updated by workflow from the child list. 

    Workflow to get field B (Valid Customer) value:

    This workflow is written on the child list that update the Valid Customer field on the parent list based on the status = completed in the child list. The logic is like below

    if current item; status equals completed  

    Calculate Excursion event: valid customers plus 1 (output to variable calc1)

    then update item in Excursion event----> update Valid Customer on Parent list with the value from variable calc1

    Field type for field A (No of Customers )

    Look Up and Count Related from the child list

    ****************************************************************

    Now I have a work flow on the parent list to do some arithmetic between A and B.

    I also try to see if I can update value from No of customer field to either local variables or some other field. But whenever I use any action on my look up field I see the below error message. If we can output the value from this look up field to some other field, it may solve my problem too.

    Error Message:

    RequestorId: fe8aabc1-1c83-d6f1-0000-000000000000. Details: An unhandled exception occurred during the execution of the workflow instance. Exception details: System.ApplicationException: HTTP 400 {"error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The query to field 'No_x0020_of_x0020_Customers_x0020\/Excursion_x0020_Id' is not valid."}}} {"Transfer-Encoding":["chunked"],"X-SharePointHealthScore":["0"],"SPClientServiceRequestDuration":["37"],"SPRequestGuid":["fe8aabc1-1c83-d6f1-bb1b-8f0acc9a4c43"],"request-id":["fe8aabc1-1c83-d6f1-bb1b-8f0acc9a4c43"],"X-FRAME-OPTIONS":["SAMEORIGIN"],"MicrosoftSharePointTeamServices":["15.0.0.4675"],"X-Content-Type-Options":["nosniff"],"X-MS-InvokeApp":["1; RequireReadOnly"],"Cache-Control":["max-age=0, private"],"Date":["Thu, 19 Nov 2015 04:01:37 GMT"],"Server":["Microsoft-IIS\/7.5"],"X-AspNet-Version":["4.0.30319"],"X-Powered-By":["ASP.NET"]}   at Microsoft.Activities.Hosting.Runtime.Subroutine.SubroutineChild.Execute(CodeActivityContext context)   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation 

    ​The query to field 'No_x0020_of_x0020_Customers_x0020\/Excursion_x0020_Id' is not valid-- No of Customer is my count related look up field in the master list.

    Wednesday, December 16, 2015 8:32 AM
  • Hi parveenagg,

    From your description, in your parent list,

    you have a look up column named "No of Customers".

    you have number column named "valid customers" which is set value from SharePoint designer.

    You want to calculate the "Total customers" with  "No of Customers"- "valid customers".

    As we know, we can't use look up column in calculate column, you can create another number column and create a workflow to set the number column's value to the "No of Customers" column's value.

    Then calculate the "Total customers" with  "number column"-"valid customers".

    For more detailed information, take a look at the following post:

    https://social.technet.microsoft.com/Forums/en-US/ae498fa0-5b2a-452e-9c0e-2ed297448d21/how-to-calculate-value-between-columns-in-two-different-listscolumn-1-in-list1-column2-in-list2?forum=sharepointgeneralprevious

    If there is any problem, feel free to reply back.

    Best Regards,

    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, December 16, 2015 8:57 AM
  • Hi Lisa,

    Thanks for your response and the good inputs.

    How do we handle the deleted records scenario with the help of workflow. For ex, if the record is already counted in my parent list (with the help of workflow) but some how this item get deleted from the list then how do we handle this scenario as we may need to minus out one count from the Total count in the parent list?

    Thursday, December 17, 2015 2:37 AM
  • Hi parveenagg,

    As you have said that the record is counted in your parent list with the help of workflow.

    Then I suggest you set your workflows to start automatically when an item is changed.

    Or you can set another workflow to update the count in parent list when an item is changed.

    Hope it helps.

    Best Regards,

    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Friday, December 25, 2015 8:56 AM