2

I have a child form and I want to get the total of the child forms to update the parent with the sum total. I am struggling with the CAML query to get the total back. The below returns zero records at present because (i suspect) the query isn't quite right.

        var query = '<Query><Where><Eq><FieldRef Name="Engagement" /><Value Type="Lookup">';
            query += ParentEngagementID + ";#" + ParentEngagementText + "</Value></Eq></Where></Query>";


        console.log("Query is " + query);
        //Checked in chrome this is: Query is <Query><Where><Eq><FieldRef Name="Engagement"></FieldRef><Value Type="Lookup">106;#ABC Ltd - 31/03/2016 - Year End</Value></Eq></Where></Query>

        var viewFields = '<ViewFields><FieldRef Name="Budget_x0020_hours" /><FieldRef Name="Actual_x0020_hours" />';
            viewFields += '</ViewFields>';

        var NewBudgetTotal = 0.00;
        var NewActualTotal = 0.00;
        $().SPServices({
            operation: "GetListItems",
            async: false,
            listName: "Work tracker - Products",
            webURL: "/sites/UKIAsuDataAnalytics",
            CAMLViewFields: viewFields,
            CAMLQuery: query,
            completefunc: function (xData, Status) {
                console.log("---------------------");
                console.log(status);
                console.log(xData.responseText);
                console.log("---------------------");
                $(xData.responseXML).SPFilterNode('z:row').each(function() {
                    console.log('Processing list of returned CAML items');
                    if($(this).attr("ows_Budget_x0020_hours")) {
                        NewBudgetTotal += Number(parseFloat($(this).attr("ows_Budget_x0020_hours").split(";#")[0],2).toFixed(2));
                    }
                    if($(this).attr("ows_Actual_x0020_hours")) {
                        NewActualTotal += Number(parseFloat($(this).attr("ows_Actual_x0020_hours").split(";#")[0],2).toFixed(2));

                    }
                });
            }
        });

If I comment out the query the XML responses coming back are like the below, and you can see the Engagement field should match:

    "<z:row ows_Budget_x0020_hours='3.00000000000000' ows_Actual_x0020_hours='0' ows_MetaInfo='80;#' ows__ModerationStatus='0' ows__Level='1' ows_ID='80' 
    ows_UniqueId='80;#{E91295D7-EF93-4F11-B486-E00F6A6AB7DF}' ows_owshiddenversion='16' ows_FSObjType='80;#0' ows_Created='2016-07-13 10:17:15' ows_PermMask='0x7fffffffffffffff' ows_Modified='2016-08-26 18:58:30' 
    ows_Engagement='106;#ABC Ltd - 31/03/2016 - Year End' ows_FileRef='80;#sites/MySite/Lists/Work Tracker  Tools/80_.000' />"

I have tried all sorts of changes to the query for the content of "Engagement" but nothing helps. Any thoughts?

(Please don't blame me on design here - I am trying to patch up some Sharepoint "work" done by a colleague. I am certainly not the best person to do the patchup, as much as they weren't the person do to the work.)

2

Lookup columns should be queried differently

By Value

<Query>
<Where>
    <Eq>
        <FieldRef Name='Engagement'  />
        <Value Type='Lookup'>" + ParentEngagementText + "</Value>
    </Eq>
</Where>
</Query>

By Id

<Query>
<Where>
    <Eq>
        <FieldRef Name='Engagement' LookupId='TRUE'  />
        <Value Type='Lookup'>" + ParentEngagementID + "</Value>
    </Eq>
</Where>
</Query>
| improve this answer | |

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.