Extracting values from other lists

Discussion created by sangel40 on Oct 16, 2017
Latest reply on Oct 23, 2017 by AndriiK

I have a form that I have created that has a primary list and a sublist. From the information entered in the sublist I need to determine which executive group and approver are required to approve this form. In the sublist I have multiple lines with -ve and +ve values, the lines are only ever spread across one giving executive group and one receiving executive group, so I thought I could get the group of the lowest value amount  (giving) and the group of the highest value amount (receiving) based on the business unit associated with the two lines, however the CAML query I have created is not working and I don't get a value returned.


The relevant columns in the sublist NPFRDetails are as follows:


Management Unit -  lookup to Management Unit Ref list

Executive Group - related executive group is also available in this list


This is the CAML query as follows:

window.givingGroup = [[@Web.GetFirstValueForQuery('NPFRDETAILS','<Query><Where><Eq><FieldRef Name="NFRID" /><Value Type="Text">[[ID]]</Value></Where></Eq><OrderBy><FieldRef Name="Amount" Ascending=True /><Value Type="Number">[[@SubLists.NPFRDetails.Amount]]</Value></OrderBy></Query>', 'Management_x0020_Unit_x003a_Exec')]]; 


Once I get the Executive Group, I then need to query another list to get the approver for the Executive Group, so using the string value I get returned from above, I then need to query a Delegate list to get the first active approver for the Executive Group and update the person / group field on the primary list.   


CAML query is as follows:

window.givingApprover = [[@Web.GetFirstValueForQuery('Delegations','<Query><Where><And><And><And><Eq><FieldRef Name="Group" /><Value Type="Text">window.givingGroup</Value></Eq></And><Geq><FieldRef Name="EffectiveFrom" /><Value Type="Date">today</Value></Geq></And><Leq><FieldRef Name="EffectiveTo" /><Value Type="Date">today</Value></Leq></And></Where></Query>', 'Delegate')]]; 


Any assistance provided would be appreciated.