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.
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">
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>
