Programming the File Repository: /Documentation

Programming the File Repository

The table exp.Files is available for users to manage files included under the @files, @pipeline, and @filesets file roots. (Note that file attachments are not included.) You can add custom properties to the File Repository as described in the topic Files Web Part Administration. These custom properties will be added to the exp.Files table, which you can manage programatically using the LabKey APIs as described in this topic.

JavaScript API Examples
RLabKey Examples
Wiki Page Example

The table exp.Files is a filtered table over exp.Data which adds a number of columns that aren't available in exp.Data, namely, RelativeFolder and AbsoluteFilePath.

To allow access to the AbsoluteFilePath and DataFileUrl fields in exp.Files, assign the site-level role "See Absolute File Paths". For details see Configure Permissions.

JavaScript API Examples

The following example code snippets demonstrate how to use the APIs to control the file system using the exp.Files table.

// List all file records with default columns (including deleted files).
LABKEY.Query.selectRows({
   schemaName: 'exp',
   queryName: 'files',
   success: function(data)
   {
       var rows = data.rows;
       for (var i = 0; i < rows.length; i++)
       {
           var row = rows[i];
           console.log(row['Name']);
       }
   }
});

// List all files containing "LabKey" substring in filename, with specified columns, order by Name.
LABKEY.Query.selectRows({
   schemaName: 'exp',
   queryName: 'files',
   columns: ['Name', 'FileSize', 'FileExists', 'RelativeFolder', 'AbsoluteFilePath', 'DataFileUrl'], // Both 'AbsoluteFilePath' and 'DataFileUrl' requires SeeFilePath permission
   filterArray: [
       LABKEY.Filter.create('Name', 'LabKey', LABKEY.Filter.Types.CONTAINS)
   ],
   sort: 'Name',
   success: function(data)
   {
       var rows = data.rows;
       for (var i = 0; i < rows.length; i++)
       {
           var row = rows[i];
           console.log(row['Name']);
       }
   }
});

// Query files with custom property fields.
// Custom1 & Custom2 are the custom property fields for files.
LABKEY.Query.selectRows({
   schemaName: 'exp',
   queryName: 'files',
   columns: ['Name', 'FileSize', 'FileExists', 'RelativeFolder', 'Custom1', 'Custom2'],
   filterArray: [
       LABKEY.Filter.create('Custom1', 'Assay_Batch_1')
   ],
   sort: 'Custom1',
   success: function(data)
   {
       var rows = data.rows;
       for (var i = 0; i < rows.length; i++)
       {
           var row = rows[i];
           console.log(row['Name'] + ", " + row['Custom1']);
       }
   }
});

// Update custom property value for file records.
LABKEY.Query.updateRows({
   schemaName: 'exp',
   queryName: 'files',
   rows: [{
       RowId: 1, // update by rowId
       Custom1: 'Assay_Batch_2'
   },{
       DataFileUrl: 'file:/LabKey/Files/run1.xslx', // update by dataFileUrl
       Custom1: 'Assay_Batch_2'
   }],
   success: function (data)
   {
       var rows = data.rows;
       for (var i = 0; i < rows.length; i++)
       {
           var row = rows[i];
           console.log(row['RowId'] + ", " + row['Custom1']);
       }
   },
   failure: function(errorInfo, options, responseObj)
   {
       if (errorInfo && errorInfo.exception)
           alert("Failure: " + errorInfo.exception);
       else
           alert("Failure: " + responseObj.statusText);
   }
});

// Insert file records with a custom property.
LABKEY.Query.insertRows({
   schemaName: 'exp',
   queryName: 'files',
   rows: [{
       AbsoluteFilePath: '/Users/xing/Downloads/labs.txt',
       Custom1: 'Assay_Batch_3'
   }],
   success: function (data)
   {
       var rows = data.rows;
       for (var i = 0; i < rows.length; i++)
       {
           var row = rows[i];
           console.log(row['RowId'] + ", " + row['Custom1']);
       }
   },
   failure: function(errorInfo, options, responseObj)
   {
       if (errorInfo && errorInfo.exception)
           alert("Failure: " + errorInfo.exception);
       else
           alert("Failure: " + responseObj.statusText);
   }
});

// Delete file records by rowId.
LABKEY.Query.deleteRows({
   schemaName: 'exp',
   queryName: 'files',
   rows: [{
       RowId: 195
   }],
   success: function (data)
   {
       //
   },
   failure: function(errorInfo, options, responseObj)
   {
       if (errorInfo && errorInfo.exception)
           alert("Failure: " + errorInfo.exception);
       else
           alert("Failure: " + responseObj.statusText);
   }
});

RLabKey Examples

library(Rlabkey)
newfile <- data.frame(
AbsoluteFilePath='/Users/xing/Downloads/labs.txt',
Custom1='Assay_Batch_INSERTED'
)

# Insert a new file record.
insertedRow <- labkey.insertRows(
"http://localhost:8080/labkey",
folderPath="/StudyVerifyProject/My%20Study/",
schemaName="exp",
queryName="files",
toInsert=newfile
)

newRowId <- insertedRow$rows[[1]]$RowId

# Query for file record by rowId.
selectedRow<-labkey.selectRows(
"http://localhost:8080/labkey",
folderPath="/StudyVerifyProject/My%20Study/",
schemaName="exp",
queryName="files",
colFilter=makeFilter(c("RowId", "EQUALS", newRowId))
)
selectedRow

# Update the custom file property for a file record.
updateFile=data.frame(
RowId=newRowId,
Custom1='Assay_Batch_UPDATED'
)
updatedRow <- labkey.updateRows(
"http://localhost:8080/labkey",
folderPath="/StudyVerifyProject/My%20Study/",
schemaName="exp",
queryName="files",
toUpdate=updateFile
)

# Delete a file record.
deleteFile <- data.frame(RowId=newRowId)
result <- labkey.deleteRows(
"http://localhost:8080/labkey",
folderPath="/StudyVerifyProject/My%20Study/",
schemaName="exp",
queryName="files",
toDelete=deleteFile
)

Sample Wiki for Grouping by AssayId (Custom File Property)

<h3>View Assay Files</h3>
<div id="assayFiles"></div>
<script type="text/javascript" nonce="<%=scriptNonce%>">
   var onSuccess = function(data) {
       var rows = data.rows, html = "<ol>";
       for (var i = 0; i < rows.length; i++)
       {
           var row = rows[i];
           html += "<li><a href="query-executeQuery.view?schemaName=exp&query.queryName=Files&query.AssayId~eq=" + row.AssayId + "">" + row.AssayId + "</a></li>";
       }
       html += "</ol>";
       var targetDiv = document.getElementById("assayFiles");
       targetDiv.innerHTML = html;
       debugger;
   };

   LABKEY.Query.executeSql({
       schemaName: 'exp',
       sql: 'SELECT DISTINCT AssayId From Files Where AssayId Is Not Null',
       success: onSuccess
   });
</script>

LabKey Support

LabKey Support