Get Values from another list

sangel40

• Created November 08, 2016 09:10

Query to retrieve all columns from a list based on a defined value  and  maximum date value

 

I am building an employee wages calculation form that I would like to pre-populate with the details from the previous year employee wage  form as a starting point so that I don't have to repeat the entry of all fields.

 

The List is called Employee Wage and the variable that I do know is the employee number. To get the latest copy of the employees wage form I would need to get the maximum effective date. Once I have identified the correct row, I then want to pull back all of the details to pre-populate. Some of the fields on the list (and there are about 40 of them) include gross wages per annum, net wages per annum, net wages per week, tax withheld, tax rate etc.

 

How would I extract all of this information from a skybow form, I am assuming it would be either the getvaluesforquery or the querylist. 

 

Any guidance on the confirmation that this is a method to fetch this information and how the query would be formatted given the information above. 

 

Thanks

Sandy

0

• 

  m.fostyak

  • November 09, 2016 17:43

  Hello Sandra

  You are right, to retrieve data from a list you should use

  [[@Web.GetFirstValueForQuery('ListName or relative Url', 'Caml query', 'FieldName')]]

  Also you have to specify correct parameters for this method:

  1. 'ListName or relative Url' - name of the list you want to get item from.
  2. 'Caml query' - query to filter all items in the list. See examples: here.
  3. 'FieldName' - internal name of the field, you want to retrieve.

   

  An example:

  [[@Web.GetFirstValueForQuery('Customers', '<Query><Where><Eq><FieldRef Name="ID" /><Value Type="Text">2</Value></Eq></Where></Query>','Title')]];

  This will return only the value of field "Title" of list "Customers" with ID equals 2.

   

  Also you can use

  [[@Web.QueryList('ListName or relative Url', 'Caml query', MaxRowNumber, 'View Fields', 'Viewattributes')]]

  Parameters for this method:

  1. 'ListName or relative Url' - name of the list you want to get item from.
  2. 'Caml query' - query to filter all items in the list. See examples: here.
  3. MaxRowNumber - number of rows to get.
  4. 'View Fields' - comma separated names of the fields you want to get.
  5. 'Viewattributes' - attributes, in your case can be left empty.

  I have added a simple example: 

  var x = [[@Web.QueryList('Customers', '<Query><Where><Eq><FieldRef Name="ID" /><Value Type="Text">2</Value></Eq></Where></Query>', 100, 'ID, Title, Address', '')]];
  var item = x[0].get_fieldValues();
  return item["Title"]; 

  This query will get the item with ID equals 2 from list "Customers". And sets field value as value of the "Title" of this item. But you can use item['Address'] or item['ID'] to get values of other fields specified in the 4th parameter.

   

  So for your case you can use GetFirstValueForQuery with corresponding parameters.

   

  Best Regards

  Markiyan Fostyak

  0

Please sign in to leave a comment.