Extract Attached Documents from Database for Parcel Service

Tom_R
New Contributor II

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

2 ACCEPTED SOLUTIONS

ChristianW
Valued Contributor

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

View solution in original post

ChristianW
Valued Contributor

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

 

View solution in original post

9 REPLIES 9

NicolasArgente
Contributor III

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 

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

ChristianW
Valued Contributor

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
New Contributor II

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
Valued Contributor

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
New Contributor

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

 

Tom_R
New Contributor II

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
Valued Contributor

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

Tom_R
New Contributor II

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

Thanks,

ChristianW
Valued Contributor

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

ChristianW_0-1684229838007.png

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

Please sign in! Tom_R