Working with documents

Lime CRM saves files in the file system table, and uses the file field type to link a user entry with a file. This field has a type attribute indicating the type of file for which it is intended, and the various types are not processed in the same way in Lime Web Service. This section describes how files are processed and provides examples of how to retrieve and add files.

Three different file types can be specified in the file field. These types are “document”, “image” and “file”. There is no real difference between the “document” and “file” types except that the “document” type is processed slightly differently.

Assume that we have contracts table containing a file field for the actual contract document. We now want to display a list containing information about the contract such as the customer to which is is related, who is responsible, and so on. We also want to display a little information about the contract document, e.g. the file suffix. This information can only be retrieved along with the contract information if the type is “document”. For other types, we must retrieve, first the contract information and then the files, in order to obtain the file data.

Documents are the only type of files that can be retrieved along with other information from the table in which the file field is found. When a field of this kind is included in a query, metadata for the document file is automatically added to the results. The document file itself is not included, however - you must indicate specifically what you want to retrieve. A document's metadata is processed in a slightly different way, making use of the name of the table containing the file field, so the metadata attribute may look slightly strange, depending on the names of the table and file field. The metadata attribute has a prefix consisting of the name of the table, not the name of the file field. For more information, see the example of retrieving a document entry with metadata for the document file, below.

A table may contain multiple file fields but only one should be of the “document” type, because of the special way that its metadata is processed. If a table contains more than one file field of the “document” type, the field for which metadata and document file are retrieved is not defined.

The table lists the attributes for a document's metadata:

When a query includes the file field for a record, metadata for the document will automatically be added to the results, as in the example below:

<?xml version="1.0" encoding="utf-16"?>
<query distinct="0">
  <tables>
    <table>contract</table>
  </tables>
  <fields>
    <field>idcontract</field>
    <field>responsible</field>
    <field>customer</field>
    <field>file</field>
  </fields>
  <conditions>
    <condition operator="=">
      <exp type="field">idcontract</exp>
      <exp type="numeric">1001</exp>
    </condition>
  </conditions>
</query>

<?xml version="1.0" encoding="utf-16"?>
<data>
  <contract idcontract="1001" responsible="213001" responsible.descriptive="Sara Seller" customer="25001" customer.descriptive="Lundalogik AB" file="221001" contract__fileextension="docx" contract__size="2531" contract__lockedby="23001" &nbsp;contract__timestamp="2008-10-30T23:02:10.850"/>
 </data>

Unlike the document metadata, which is added automatically, you must specifically indicate if you want to include document data. In the example below, the contractdata field is included in the query; this means that the response now contains the contractdata attribute, the value of which is the document file in the form of a binary string.

<?xml version="1.0" encoding="utf-16"?>
<query distinct="0">
  <tables>
    <table>contract</table>
  </tables>
  <fields>
    <field>idcontract</field>
    <field>responsible</field>
    <field>customer</field>
    <field>file</field>
    <field>contract__data</field>
  </fields>
  <conditions>
    <condition operator="=">
      <exp type="field">idcontract</exp>
      <exp type="numeric">1001</exp>
    </condition>
  </conditions>
 </query>
 

 <?xml version="1.0" encoding="utf-16"?>
 <data>
   <contract idcontract="1001" responsible="213001" responsible.descriptive="Sara Seller" customer="25001" customer.descriptive="Lundalogik AB" file="221001" contract__fileextension="docx" contract__size="2531" contract__lockedby="23001" &nbsp;contract__timestamp="2008-10-30T23:02:10.850"/>
</data>

How the document file should be managed depends on what it will be used for and the platform used for retrieving information via Lime Web Service. The example below shows how you can use C# to save the file to a disk.

The following example runs the previous query and saves the file to the hard disk.

public void SaveDocumentToFile(string query)
{
  tangelo.DataServiceClient client = new tangelo.DataServiceClient();
 
  XmlDocument xmlData = new XmlDocument();
  xmlData.LoadXml(client.GetXmlQueryData(query));
 
  // The actual document is saved as a binary string in
  // the XML document. We retrieve the string as a byte vector.
  byte[] data = Convert.FromBase64String(
    xmlData.SelectSingleNode(
            "/data/contract/@contract__data").Value);
 
  // Save to file and use the "customer descriptive" field as the file name
  string fileName = Path.Combine("C:\\Temp", string.Format("{0}.{1}",
    xmlData.SelectSingleNode(
       "/data/document/@customer.descriptive").Value),
     xmlData.SelectSingleNode(
       "/data/document/contract__fileextension").Value));
 
  using (FileStream file = new FileStream(fileName, FileMode.Create)) 
  {
    file.Write(data, 0, data.Length);
  }
}

As previously stated, and unlike documents, images and files cannot be retrieved in the normal way. Instead, we must use a special method with the service which allows us to retrieve all file types. You can retrieve one file at a time or several files at once.

Files are stored in a system table in the database, but the table cannot be accessed via the web service. This means that files retrieved must always be related to a table that is available via Lime Web Service. The result is XML in the form of a strong containing one node for each entry, with a subnode for the file. The entry node always has the same name as the table, and always contains the id{tablename} attribute, which is the entry ID. The file node always has the same name as the file field, and contains the size, timestamp and fileextension attributes. Note that the result only contains entries that are related to a file.

<?xml version="1.0" encoding="utf-16"?>
<query>
  <tables>
    <table>coworker</table>
   </tables>
   <fields>
     <field>portrait</field>
   </fields>
   <conditions>
     <condition operator="equal">
       <exp type="field">idcoworker</exp>
       <exp type="numeric">193001</exp>
     </condition>
   <condition operator="equal">
     <exp type="field">idcoworker</exp>
     <exp type="numeric">184001</exp>
   </condition>
  </conditions>
</query>

<?xml version="1.0" encoding="utf-16" ?>
<data>
  <coworker idcoworker="193001">
    <portrait timestamp="2011-01-25 13:36:34" fileextension="xml" size="127777" xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">{binary data base64 encoded}</portrait>
  </coworker>
  <coworker idcoworker="184001">
    <portrait timestamp="2011-01-26 17:42:58" fileextension="xls" size="12664" xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">{binary data base64 encoded}</portrait>
  </coworker>
</data>

The conditions for the following example are a database in which an image file field, “portrait”, has been added to the coworker table.

//
// The example shows how to retrieve an image for a specific employee
//        
public void SaveSingleCoworkerPortrait(int idcoworker)
{
  var client = new DataServiceClient();
 
  var xmlData = new XmlDocument();
  xmlData.LoadXml(client.GetFiles("coworker", "portrait", idcoworker));
 
  var = Convert.FromBase64String(
         xmlData.SelectSingleNode("/data/coworker/portrait/text()").Value);
 
  // Save to file with the employee ID as file name
  string fileName = Path.Combine("C:\\Temp", string.Format("{0}.{1}",
         idcoworker, xmlData.SelectSingleNode(
                 "/data/coworker/portrait/@fileextension").Value));
 
  using (FileStream file = new FileStream(fileName, FileMode.Create)) 
  {
         file.Write(data, 0, data.Length);
  }
}

//
// The example shows how to retrieve images of all employees 
// who match a query
//
public void SaveCoworkerPortraits(string query)
{
  var client = new tangelo.DataServiceClient();
 
  XmlDocument xmlData = new XmlDocument();
  xmlData.LoadXml(client.GetFiles("coworker", "portrait", query));
 
  // Manage all entries with a related image
  foreach (XmlNode node in xmlData.SelectNodes("/data/coworker/portrait")) 
  {
    var data = Convert.FromBase64String(node.InnerText);
 
    // Save to file with the employee ID as file name
    string fileName = Path.Combine("C:\\Temp", string.Format("{0}.{1}",
           node.SelectSingleNode("parent::*/@idcoworker").Value,
           node.SelectSingleNode("./@fileextension").Value));
 
    using (FileStream file = new FileStream(fileName, FileMode.Create)) 
    {
      file.Write(data, 0, data.Length
    }
  }
}

To add a new document, create an XML document that describes the new document entry. Just as with other entries, you create an element with the same name as the document table and add fields and values as attributes. To send a file, an element with the same name as the document field must be added under the entry element. You also specify the file suffix here as a normal attribute, and add the file as a binary large object, as in the example below.

All types of files are added via an XML document containing an entry node and a file node for each file.

Add a new file by generating XML according to a specific structure. This XML must contain an entry node for each document, which must contain a file node for the actual file. The entry node must have the same name as the table containing the file field, while the file node must have the same name as the file field. You can also add multiple files at once.

In the file node, specify the file suffix and then add the file itself as a binary string. Note that the binary string should be specified as a value, not as an attribute of the file node. The following is an example of what XML for adding files should look like. In the example, the document table is called document, the file field is also called document and we also update the document properties comment (text field), type (option list) and company (relation field).

<?xml version="1.0" encoding="utf-16"?>
<records>
  <document iddocument="-1" comment="Drawing" type="40801" company="1001">
    <document fileextension="jpg" xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">{binary string}</document>
  </document>
</records>

The following example generates XML according to the above example in order to create a new document with a name (comment), document type (type), relation to customer (company) and image (document).

private void AddDocument(string documentName, string documentFile, int typeId, int customerId)
{
  FileInfo fileInfo = new FileInfo(documentFile);
 
  // Retrieve file suffix but delete the introductory full stop
  string extension = fileInfo.Extension.Substring(1);
  byte[] data = null;
 
  // Load the file to a byte array
  using (FileStream file = 
    new FileStream(fileInfo.FullName, FileMode.Open, FileAccess.Read)) 
  {
    data = new byte[file.Length];
    int bytesToRead = (int)file.Length;
    int bytesRead = 0;
 
    while (bytesToRead > 0) 
    {
      int i = file.Read(data, bytesRead, bytesToRead);
 
      if (0 == i)
             break;
 
      bytesRead += i;
      bytesToRead -= i;
    }
  }
 
  // Generate XML document
  StringWriter stringWriter = new StringWriter();
 
  using (XmlTextWriter xmlWriter = new XmlTextWriter(stringWriter)) 
  {
    xmlWriter.WriteStartDocument();
    xmlWriter.WriteStartElement("records");
 
    // Create element for document
    xmlWriter.WriteStartElement("document");
 
    // This is an new entry, so we use a negative ID
    // but you can update an existing document by
    // specifying an ID for them.
    xmlWriter.WriteAttributeString("iddocument", "-1");
 
    // Add document name
    xmlWriter.WriteAttributeString("comment", documentName);
 
    // Add document type
    xmlWriter.WriteAttributeString("type", typeId);
 
    // Add document's relation to customer
    xmlWriter.WriteAttributeString("comment", customerId);
 
    // Create element for document and the element created has
    // same name as document field
    xmlWriter.WriteStartElement("document");
    xmlWriter.WriteAttributeString("fileextension", extension);
 
    // Add the actual file
    xmlWriter.WriteAttributeString(
           "xmlns:dt", "urn:schemas-microsoft-com:datatypes");
    xmlWriter.WriteAttributeString("dt:dt", "bin.base64");
    xmlWriter.WriteBase64(data, 0, data.Length);
 
    // Close open tags
    xmlWriter.WriteEndElement();
    xmlWriter.WriteEndElement();
    xmlWriter.WriteEndElement();
  }
 
  var client = new tangelo.DataServiceClient();
 
  XmlDocument xmlData = new XmlDocument();
  client.UpdateData(stringWriter.ToString());
}

You can update existing documents via the web service, but this is not something that we recommend, at least not at the moment. Nevertheless, here is a description of how to add this support in your application.

When you open a document to edit it, a document lock should be requested so that nobody else can start editing at the same time. When you have finished editing, the document can be updated and unlocked in the same operation. A document is locked with the help of a normal data update; a request may look as follows:

<?xml version="1.0" encoding="utf-16"?>
<records>
  <document iddocument="1001">
    <file locked="1"/>
  </document>
</records>

When you have finished editing, you can set the “locked” attribute to 0 when you submit the document. The problem associated with updating an existing document is that you can end up with a range of locked documents that are not updated because the users just chose to close the document rather than save it, etc. This means that your application must keep an eye on what a user is doing with document that has been checked out.