Question

Looking for a way to grab the last/most recent file attachment on a document to display on a ReportDesigner report

  • 8 February 2024
  • 7 replies
  • 83 views
S
Rank

When printing the invoice for the Customer, I would like the POD image file attached to the ARInvoice document to display on the report. The way it currently works in ReportDesigner is using a PictureBox, with the Source set as Database. The value is [ARInvoice.NoteFiles].

 

This works fine some of the time, but if there are multiple attachments on the Invoice document it will pull the one first added. Is there any way to change this so it gets the image from the latest added attachment? 

 

I would like to avoid a subreport as this is generated extremely frequently and I’m worried that generating twice the reports to achieve this will take an unnecessary toll on performance. 

7 replies

F
Rank
Userlevel 3
Badge

@sconlon88  Could you attach the report then I can add it to my local to see what I can do

S
Rank

@frederickodendaal77 

Unfortunately, I am not able to attach the report due to security concerns. However, this issue is easily reproduceable with only one data table and a PictureBox report object. I’ve attached an image to show you what I mean. 

The NoteFiles field on the ARInvoice table (it also seems to exist on SOOrder, among other document tables), while listed in the DAC Schema Browser, is referenceable by the PictureBox object. However, if there is more than one attachment on a particular document, it will select the earliest attachment by date to display. You shouldn’t need more than this object in your report designer and an existing document with multiple image attachments. Let me know if you need any more information or if anything else is unclear, as I am still trying to find an efficient solution to this issue. 

 

Thanks!

jamesh
Rank
Userlevel 6
Badge +6

  @sconlon88 

Looking through my notes, I’ve seen this work with the following instructions for your specific scenario:

If you left join the document’s table (ARInvoice) on NoteID to NoteDoc.NoteID, you can inner join NoteDoc.FileID to UploadFile.FileID. In the relations of the NoteDoc to UploadFile inner join, put an AND operator, then on a new line the parent formula will be =INSTR(CSTR([UploadFile.Name]),’image/signature’), EQUALS, and the child formula will be =1. You will need to add this join for each different type of image, like the image or signature tags, because it apparently only pulls a single image. Then, in the PictureBox, you will need to select the source as Database as you already have and the data value as =[UploadFile.Name].

joins and formula to reference and isolate attachments to the document
James Harris - ERP Guy & Acumatica MVP - If I answered your question, please mark my response as "Best Answer" :)
S
Rank

@jamesh This is, word for word, the solution I’ve replied with to another user who was facing a very similar issue. Please don’t say that this came from “your notes”; the Acumatica community would be a great resource if people didn’t pass off solutions they find elsewhere as their own. 

However, like I stated in my original reply, this solution will not work for my situation due to the performance decrease of this report, which gets generated in large batches where performance optimizations are extremely necessary to balance system load. I am also avoiding using subreports for this reason. I can optimize this solution further, but it would still be an decrease in performance when compared to using only the magic datafield [ARInvoice.NoteFiles]. If there is any way to specify an index, descending sort, or something similar on this NoteFiles datafield that I may be missing, I would love to hear your solution. 

jamesh
Rank
Userlevel 6
Badge +6

  

@jamesh This is, word for word, the solution I’ve replied with to another user who was facing a very similar issue. Please don’t say that this came from “your notes”; the Acumatica community would be a great resource if people didn’t pass off solutions they find elsewhere as their own. 

However, like I stated in my original reply, this solution will not work for my situation due to the performance decrease of this report, which gets generated in large batches where performance optimizations are extremely necessary to balance system load. I am also avoiding using subreports for this reason. I can optimize this solution further, but it would still be an decrease in performance when compared to using only the magic datafield [ARInvoice.NoteFiles]. If there is any way to specify an index, descending sort, or something similar on this NoteFiles datafield that I may be missing, I would love to hear your solution. 

Well that explains why I had it in my notes but didn’t jot down your username!  haha - My apologies…  Wasn’t trying to steal your answer or take credit for anything.  I was literally reading both threads and copied it while I was researching as it is so closely related to this thread but forgot what for until I came back to this tab.  

As to the rest, the only other solutions I could find other than similar ones to this would be using image variables like in this post:

Disregarding displaying images horizontally of course, rather using variables and adjusting the formula to use more than a single image in a single picture box, and possibly an IIF statement for visibility to only display the most recent variable that exists.  This of course would depend on the complexity and number of attachments you expect to encounter.  If it’s 2-4 then I’d say this might work out.  But if there’s 20-50, this probably isn’t going to be the right idea. 

James Harris - ERP Guy & Acumatica MVP - If I answered your question, please mark my response as "Best Answer" :)
Chris Hackett
Rank
Userlevel 7
Badge

Hi @sconlon88 were you able to find a solution? Thank you!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
S
Rank

Hi @sconlon88 were you able to find a solution? Thank you!

Unfortunately this is still something I’m looking into

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved