04-10-2023
10:20 AM
- last edited on
05-24-2023
05:21 AM
by
JackLacava
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
Solved! Go to Solution.
04-11-2023 04:16 AM
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).
04-11-2023 10:35 AM
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
05-16-2023 06:03 AM
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
04-11-2023 02:45 AM
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
04-11-2023 04:16 AM
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).
04-11-2023 09:27 AM
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.
04-11-2023 10:35 AM
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
05-04-2023 07:48 PM
Tom, Were you able to get this to work? I'm doing something similar but it's not working.
05-05-2023 12:03 PM
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.
05-14-2023 03:39 AM
Extracting from a register is a different exercise, can you tell me the name of the market place solution you use?
05-15-2023 02:25 PM
We are using the Amco Leasing Solution (AELS), an trying to email out the attached support documents loaded to the support register.
Thanks,
05-16-2023 06:03 AM
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
06-09-2023 04:19 PM
Thanks Christian, Sorry for the delayed response, it took a bit of tweeking, but it worked. Thanks again.
06-12-2023 03:19 AM
Great to hear it works. Cheers