Using a web service call worked for me, but you will at least need VSTA (Visual Studio Tools for Applications) in order to run some code behind the form. You should be guided to download it if you click on any code editing button associated to a form' control.
Once there you'll need a reference to the "lists" web service on your SP site (http://spsiteserver/spsitepath/_vti_bin/lists.asmx?WSDL), then the code to query your list and retrieve the "Approval" column: some (hopefully helpful) clarifications:
- "listsWS" is the web reference name to the "lists" web service;
- I'm querying the list using a "projectID" and "GateNumber" keys (select the list item where projectID = X and GateNumber = Y) because this makes sense in my scenario, you'll probably need to define a different criteria to identify the "current" form;
- You will need the GUID of both the library and the view you want to query; in my case I had an additional requirement to query one library or another, so the code here down decides which one to query based on the value of the "location" form field;
- to retrieve GUID for your list and view, just google the question, there are many different ways (but consider that if you have separate development, test and production environments, those GUIDS are likely to change among them);
- the final "decodeApprovalStatus" call just maps integers to strings with the mapping above in this thread
In the form' onLoad method I then call this gatherFormStatus and store the output into a hidden form field; based on that value I then show an "Approved" (or "Rejected", and so on) string/image/whatever you prefer in the form.
Hope it helps,
private string gatherFormStatus()
{
listsWS.Lists lists = new listsWS.Lists();
lists.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
XmlDocument doc = new System.Xml.XmlDocument();
string projectID = thisXDocument.DOM.selectSingleNode("/my:myFields/my:ProjectSummaryInformation/my:projectID").text;
doc.LoadXml("<Document><Query><Where><And><Eq><FieldRef Name=\"Project_x0020_ID\" /><Value Type=\"Text\">" + projectID + "</Value></Eq>" +
"<Eq><FieldRef Name=\"Gate_x0020_Number\" /><Value Type=\"Text\">3</Value></Eq></And></Where></Query><ViewFields/><QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions></Document>");
XmlNode listQuery = doc.SelectSingleNode("//Query");
XmlNode listViewFields = doc.SelectSingleNode("//ViewFields");
XmlNode listQueryOptions = doc.SelectSingleNode("//QueryOptions");
XmlNode res;
// this method is only invoked when the form is NOT new (i.e. user is opening an already submitted form); hence we do have the
// location and can use to decide if should query the NdG library or the "regular" library:
string formLocation = thisXDocument.DOM.selectSingleNode("/my:myFields/my:ProjectSummaryInformation/my:GeneralInformation/my:location").text;
string library = ("NdG".Equals(formLocation) ? NdGlibraryID : libraryID);
string viewID = ("NdG".Equals(formLocation) ? NdGsystemViewID : systemViewID);
try
{
res = lists.GetListItems(
library,
viewID,
listQuery,
listViewFields,
null,
listQueryOptions,
null);
// Method signature:
// lists.GetListItems(String ListName, String viewName, XmlNode query, XmlNode viewFields, string rowLimit, XmlNode queryOptions, String webID);
}
catch (Exception e)
{
thisXDocument.UI.Alert("There has been an error during data exchange with Sharepoint: \n" + e.Message);
return "Approved"; // Approved; if status cannot be retrieved, lock the form
}
XmlDocument resultsDoc = new XmlDocument();
XmlNamespaceManager ns = new XmlNamespaceManager(resultsDoc.NameTable);
ns.AddNamespace("z", "#RowsetSchema");
XmlNodeList rows = res.SelectNodes("//z:row", ns);
string statusCode = "0";
if (rows.Item(0).Attributes["ows_Approval"] != null)
{
statusCode = rows.Item(0).Attributes["ows_Approval"].Value;
}
return decodeApprovalStatus(statusCode);
}