Extract Attached Documents from Database for Parcel Service

[Tom_R]

Hello, 

We are trying to set up a package in Parcel Service that has documents attached that were loaded into the system as attachments through a dashboard.  Right now it appears the documents are saved in the background application database as System.Bytes[].  Is there a way to extract these files to a file explorer folder so the can be attached to a parcel service email?

Thanks,

Tom

[ChristianW · Accepted solution]

It isn't too complicated, this is how it works in c#:

XFFileInfo fileInfo = new XFFileInfo(FileSystemLocation.ApplicationDatabase, nameOfFile);
fileInfo.FolderFullName = "Documents/Public/YourFolder";

fileInfo.Extension = "xlsx";
fileInfo.Description = descriptionOfFile;
XFFile fileFile = new XFFile(fileInfo, "", bytesOfFile);

BRApi.FileSystem.InsertOrUpdateFile(si, fileFile);					

and Vb:

Dim fileInfo as XFFileInfo = new XFFileInfo(FileSystemLocation.ApplicationDatabase, nameOfFile)
fileInfo.FolderFullName = "Documents/Public/YourFolder"

fileInfo.Extension = "xlsx"
fileInfo.Description = descriptionOfFile
Dim fileFile as XFFile = new XFFile(fileInfo, "", bytesOfFile)

BRApi.FileSystem.InsertOrUpdateFile(si, fileFile)

with bytesOfFile being your Bytes[] (array of Bytes).

[ChristianW · Accepted solution]

There is a BrApi function for this purpose:

'This is how you get a list of attachments
Dim objDataAttachmentList As DataAttachmentList = BRApi.Finance.Data.GetDataAttachments(si, dataCellPk, includeFileBytes, startRowIndex, pageSize)

'And you can access the file bytes with it if includeFileBytes = true
objDataAttachmentList.Items(0).FileBytes

[NicolasArgente]

Hi Tom,
Short Answer : Yes you can, but you will always have to play with the Bytes.
That means that when you want to save it to a user folder, you will need to save it in Bytes. And if you need to export the file, then you need to "decode" the Bytes. 
The exception would be for the system foder, but I doubt that you want to do that.
Nic 

[ChristianW · Accepted solution]

It isn't too complicated, this is how it works in c#:

XFFileInfo fileInfo = new XFFileInfo(FileSystemLocation.ApplicationDatabase, nameOfFile);
fileInfo.FolderFullName = "Documents/Public/YourFolder";

fileInfo.Extension = "xlsx";
fileInfo.Description = descriptionOfFile;
XFFile fileFile = new XFFile(fileInfo, "", bytesOfFile);

BRApi.FileSystem.InsertOrUpdateFile(si, fileFile);					

and Vb:

Dim fileInfo as XFFileInfo = new XFFileInfo(FileSystemLocation.ApplicationDatabase, nameOfFile)
fileInfo.FolderFullName = "Documents/Public/YourFolder"

fileInfo.Extension = "xlsx"
fileInfo.Description = descriptionOfFile
Dim fileFile as XFFile = new XFFile(fileInfo, "", bytesOfFile)

BRApi.FileSystem.InsertOrUpdateFile(si, fileFile)

with bytesOfFile being your Bytes[] (array of Bytes).

[Tom_R]

Thank you for the code sample Christian, however I am not sure how to identify my array of bytes.  Could you please let me know where to identify that?

Thanks.

[ChristianW · Accepted solution]

There is a BrApi function for this purpose:

'This is how you get a list of attachments
Dim objDataAttachmentList As DataAttachmentList = BRApi.Finance.Data.GetDataAttachments(si, dataCellPk, includeFileBytes, startRowIndex, pageSize)

'And you can access the file bytes with it if includeFileBytes = true
objDataAttachmentList.Items(0).FileBytes

[BamaGirl]

Tom, Were you able to get this to work? I'm doing something similar but it's not working.

[Tom_R]

Hello,

I have not been able to get it to work yet.  I am struggling around the DataCellPk because I am trying to extract from a register and I don't know how to configure that.

[ChristianW]

Extracting from a register is a different exercise, can you tell me the name of the market place solution you use?

[Tom_R]

We are using the Amco Leasing Solution (AELS), an trying to email out the attached support documents loaded to the support register.

Thanks,

[ChristianW · Accepted solution]

Hi Tom

I don't have the Amco solution installed on my environment, but I assume, it stores the document in a relational table.

In reporting compliance the name of the table is: XFW_TCR_RegisterSupport and the column with the document is called FileBytes

you can access the information with a sql statement and then convert it as described in the sample code I posted earlier, or save it to the temp folder as shown in the following sample.

This is how you access the filebytes from a DataRow object:

Dim sql As New Text.StringBuilder

sql.Append("Select * ")
sql.Append("From XFW_TCR_RegisterSupport With (NOLOCK) ")
sql.Append("Where ")
sql.Append($"FKRegisterID = '{SqlStringHelper.EscapeSqlString(registerID)}' ")
sql.Append($"And WFProfileName = '{SqlStringHelper.EscapeSqlString(profileName)}' ")
sql.Append($"And WFScenarioName = '{SqlStringHelper.EscapeSqlString(scenarioName)}' ")
sql.Append($"And WFTimeName = '{SqlStringHelper.EscapeSqlString(timeName)}' ")

Dim fileName As Sting 
Dim fileBytes As Bytes()

Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	Using dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
		fileName = dt(0)("FileName")
		fileBytes = dt(0)("FileBytes")

	End Using
End Using

BRApi.Utilities.SaveFileBytesToUserTempFolder(si, si.UserName, fileName, fileBytes)

Sometimes, the bytes are stored compressed, then you need to do this:

fileBytes = CryptoManager.BytesDecompress(si, dt(0)("FileBytes"))

I wasn't able to test it, it might contain typos, but the concept should get clear.

Cheers

Christian

[Tom_R]

Thanks Christian, Sorry for the delayed response, it took a bit of tweeking, but it worked.  Thanks again.

[ChristianW]

Great to hear it works. Cheers