Solved

ARTran.NoteText in Invoice Report

  • 15 February 2023
  • 25 replies
  • 231 views

Userlevel 2
Badge

Hey all,

I’m working on the Invoice report and want to integrate the ARTran.NoteText. I’m not sure how to join it in the schema to get it working. I’ve tried a few random solutions but it always prints blank. ARTran is already joined and other items from ARTran print fine- but ARTran.NoteText doesn’t.

icon

Best answer by BenjaminCrisman 16 February 2023, 16:39

View original

25 replies

Userlevel 7
Badge +4

Hi @ALEXSLUSSER15! For this issue you’ll just need to join ARTran to Note on NoteID = NoteID, then display the Note.NoteText in the report.

There is an example of this already working on the default AR Invoice/Memo printed form (AR641000):

 

Userlevel 2
Badge

Hi @ALEXSLUSSER15! For this issue you’ll just need to join ARTran to Note on NoteID = NoteID, then display the Note.NoteText in the report.

There is an example of this already working on the default AR Invoice/Memo printed form (AR641000):

 

@BenjaminCrisman I’ve done this. It doesn’t work. 

 

Userlevel 7
Badge +4

@ALEXSLUSSER15 In the original post are you using ARTran.NoteText, make sure to use Note.NoteText.

You may also need to refresh the schema of the report maybe, shouldn’t necessarily need to, but typically if there is data missing from a field you know it’s there, refresh the schema and usually that works.

Otherwise you can add the .zip RPX file and I can see if it is working for my site with the same configuration

Userlevel 2
Badge

@ALEXSLUSSER15 In the original post are you using ARTran.NoteText, make sure to use Note.NoteText.

You may also need to refresh the schema of the report maybe, shouldn’t necessarily need to, but typically if there is data missing from a field you know it’s there, refresh the schema and usually that works.

Otherwise you can add the .zip RPX file and I can see if it is working for my site with the same configuration

@BenjaminCrisman Here’s the ZIP.

Userlevel 7
Badge +4

@ALEXSLUSSER15 The uploaded zip file is a .rps

Open the report in Report Designer > File > Save As… and then zip the .RPX file

Userlevel 2
Badge

@ALEXSLUSSER15 The uploaded zip file is a .rps

Open the report in Report Designer > File > Save As… and then zip the .RPX file

@BenjaminCrisman 

Userlevel 7
Badge +4

@ALEXSLUSSER15 This worked for me when I added Note.NoteText to the report:

This is the note on the grid of the invoice right, not the note form in the header?

Userlevel 2
Badge

@ALEXSLUSSER15 This worked for me when I added Note.NoteText to the report:

This is the note on the grid of the invoice right, not the note form in the header?

@BenjaminCrisman The note text isn’t standalone. It’s in the third text box under description, at the end of the If statements. When it is in that statement it doesn’t print, and it needs to be in that statement.

Userlevel 7
Badge +4

@ALEXSLUSSER15 I would double check the output of each Iif statement then because it still works for me (after switching to Attributes I had locally):

=IIf([InventoryItem.COLOR_Attributes]=null,'','FSC Claim: ')
+ IIf([InventoryItem.COLOR_Attributes]<>null,[InventoryItem.COLOR_Attributes]+'{br}','') + [Note.NoteText]

Userlevel 2
Badge

@ALEXSLUSSER15 I would double check the output of each Iif statement then because it still works for me (after switching to Attributes I had locally):

=IIf([InventoryItem.COLOR_Attributes]=null,'','FSC Claim: ')
+ IIf([InventoryItem.COLOR_Attributes]<>null,[InventoryItem.COLOR_Attributes]+'{br}','') + [Note.NoteText]

It prints properly one I remove Note.NoteText. Do you know why it would be breaking?

Userlevel 2
Badge

@ALEXSLUSSER15 I would double check the output of each Iif statement then because it still works for me (after switching to Attributes I had locally):

=IIf([InventoryItem.COLOR_Attributes]=null,'','FSC Claim: ')
+ IIf([InventoryItem.COLOR_Attributes]<>null,[InventoryItem.COLOR_Attributes]+'{br}','') + [Note.NoteText]

It prints properly one I remove Note.NoteText. Do you know why it would be breaking?

@BenjaminCrisman As a simple test, I put Note.NoteText in it’s own textbox below the one with the If statements, and the one with the if statements printed while the Note.NoteText box came out empty. There SHOULD be text in the Note.NoteText box, as we have notes attached to those line items.

Userlevel 7
Badge +4

@ALEXSLUSSER15 I would expect only one of the note fields to populate, if you put the same field in the same section I think it will only print it once, so as it renders it will skip the subsequent same fields.

You could put it as a standalone field in another section and I think it should print like that

Userlevel 2
Badge

@ALEXSLUSSER15 I would expect only one of the note fields to populate, if you put the same field in the same section I think it will only print it once, so as it renders it will skip the subsequent same fields.

You could put it as a standalone field in another section and I think it should print like that

@BenjaminCrisman I don’t know what this means. If you are referring to putting it in a different detailSection, I really don’t want to do that. It needs to print with each line, as each line item may have different notes. I just don’t get why it won’t print in my report but prints fine in yours.

Userlevel 7
Badge +4

@ALEXSLUSSER15 I think I misunderstood what you were saying in the prior message then, I thought you were saying that the note text would print in the IIF statement, but not as a standalone field, and I was thinking that it might not print more than once, but then right after I click send I realize it worked for me 🤡, sorry lol.

I actually was reviewing a different case in the Pioneer site and I checked real quick a few invoice but none of them had any notes on the grid.

I checked the Sales Orders and I did see that there are lots of line level notes on the Sales Orders but I don’t see these making it to the invoice, so there are no notes on ARTran

Userlevel 2
Badge

@ALEXSLUSSER15 I think I misunderstood what you were saying in the prior message then, I thought you were saying that the note text would print in the IIF statement, but not as a standalone field, and I was thinking that it might not print more than once, but then right after I click send I realize it worked for me 🤡, sorry lol.

I actually was reviewing a different case in the Pioneer site and I checked real quick a few invoice but none of them had any notes on the grid.

I checked the Sales Orders and I did see that there are lots of line level notes on the Sales Orders but I don’t see these making it to the invoice, so there are no notes on ARTran

@BenjaminCrisman So to clarify- you’re saying the reason nothing shows up is because the notes aren’t on ARTran?
Are they on SOLine? If so, how do I attach SOLine to the report and print the note from that?

Userlevel 2
Badge

@ALEXSLUSSER15 I think I misunderstood what you were saying in the prior message then, I thought you were saying that the note text would print in the IIF statement, but not as a standalone field, and I was thinking that it might not print more than once, but then right after I click send I realize it worked for me 🤡, sorry lol.

I actually was reviewing a different case in the Pioneer site and I checked real quick a few invoice but none of them had any notes on the grid.

I checked the Sales Orders and I did see that there are lots of line level notes on the Sales Orders but I don’t see these making it to the invoice, so there are no notes on ARTran

@BenjaminCrisman So to clarify- you’re saying the reason nothing shows up is because the notes aren’t on ARTran?
Are they on SOLine? If so, how do I attach SOLine to the report and print the note from that?

@BenjaminCrisman Figured it out on my own! SOOrder (left) SOLine, with OrderType = OrderType and OrderNbr = OrderNbr, then SOLine (left) Note, with NoteID = NoteID, with child alias OrdNote. Then changed the textbox to OrdNote.NoteText.

Userlevel 2
Badge

@ALEXSLUSSER15 I think I misunderstood what you were saying in the prior message then, I thought you were saying that the note text would print in the IIF statement, but not as a standalone field, and I was thinking that it might not print more than once, but then right after I click send I realize it worked for me 🤡, sorry lol.

I actually was reviewing a different case in the Pioneer site and I checked real quick a few invoice but none of them had any notes on the grid.

I checked the Sales Orders and I did see that there are lots of line level notes on the Sales Orders but I don’t see these making it to the invoice, so there are no notes on ARTran

@BenjaminCrisman So to clarify- you’re saying the reason nothing shows up is because the notes aren’t on ARTran?
Are they on SOLine? If so, how do I attach SOLine to the report and print the note from that?

@BenjaminCrisman Figured it out on my own! SOOrder (left) SOLine, with OrderType = OrderType and OrderNbr = OrderNbr, then SOLine (left) Note, with NoteID = NoteID, with child alias OrdNote. Then changed the textbox to OrdNote.NoteText.

@BenjaminCrisman Well… I thought I’d fixed it. But now I am having an entirely different issue- repeating line items. Each item is repeating exactly 7 times. Do you have any idea why this would be, or how to resolve it?

Userlevel 7
Badge +4

@ALEXSLUSSER15 The data from ARTran and SOLine will be similar for sales orders which have an invoice created off them, so there are a couple one-to-many relations which are bringing in the duplicates.

I think you’ll need to use a subreport to display the note if you can’t remove some of the tables to stop the duplicates.

I would recommend to make a subreport and place it in the detail section and make the subreport based on SOLine to Note and send the SOOrder OrderNbr and OrderType to the subreport via the collection parameters.

Typically if you’re seeing duplicates because of some tables added, but there’s no other way to get the data without them, then a subreport is needed to make sure it looks clean

Userlevel 2
Badge

@ALEXSLUSSER15 The data from ARTran and SOLine will be similar for sales orders which have an invoice created off them, so there are a couple one-to-many relations which are bringing in the duplicates.

I think you’ll need to use a subreport to display the note if you can’t remove some of the tables to stop the duplicates.

I would recommend to make a subreport and place it in the detail section and make the subreport based on SOLine to Note and send the SOOrder OrderNbr and OrderType to the subreport via the collection parameters.

Typically if you’re seeing duplicates because of some tables added, but there’s no other way to get the data without them, then a subreport is needed to make sure it looks clean

@BenjaminCrisman I’ve never worked with a sub-report before. I’m not sure how they work or how to set it up. Any tips?

Userlevel 2
Badge

@ALEXSLUSSER15 The data from ARTran and SOLine will be similar for sales orders which have an invoice created off them, so there are a couple one-to-many relations which are bringing in the duplicates.

I think you’ll need to use a subreport to display the note if you can’t remove some of the tables to stop the duplicates.

I would recommend to make a subreport and place it in the detail section and make the subreport based on SOLine to Note and send the SOOrder OrderNbr and OrderType to the subreport via the collection parameters.

Typically if you’re seeing duplicates because of some tables added, but there’s no other way to get the data without them, then a subreport is needed to make sure it looks clean

@BenjaminCrisman I’ve never worked with a sub-report before. I’m not sure how they work or how to set it up. Any tips?

@BenjaminCrisman I’ve set up a subreport with the necessary tables and text and such, but nothing shows up when I run the larger report. It’s saved to the server, the subreport in the Invoice report set to the right subreport. Not sure what the issue is or how to fix it.

Userlevel 7
Badge +4

@ALEXSLUSSER15 I’ve attached a copy of the report and subreport I created and I added a note to a Sales Order which the invoice was based off and it displays the note as expected:

 

Userlevel 2
Badge

@ALEXSLUSSER15 I’ve attached a copy of the report and subreport I created and I added a note to a Sales Order which the invoice was based off and it displays the note as expected:

 

@BenjaminCrisman Thanks! Is there any way to make the text smaller? Our notes are generally a bit long and it’s making what was once able to fit in one or two pages take up 5-6.

Userlevel 7
Badge +4

@ALEXSLUSSER15  You can open the subreport and adjust the font in there, I don’t think applying the styles from the main report will work.

Open the subreport > click the text box > Expand the Style section and set the font to 10px or whatever works best, I tested and it works:

 

Userlevel 2
Badge

@ALEXSLUSSER15  You can open the subreport and adjust the font in there, I don’t think applying the styles from the main report will work.

Open the subreport > click the text box > Expand the Style section and set the font to 10px or whatever works best, I tested and it works:

 

@BenjaminCrisman I did this, and started getting this weird artifacting. Is it possible to integrate the IIf([InventoryItem.FSCCLAIM_Attributes]=null,'','FSC Claim: ')
+IIf([InventoryItem.FSCCLAIM_Attributes]<>null,[InventoryItem.FSCCLAIM_Attributes]+'{br}','') into the sub-report to save room?

 

Userlevel 7
Badge +4

@ALEXSLUSSER15 Yes you can mess around with the subreport and try adding the expression in there, but you’ll probably need to add in the InventoryItem table into the subreport so you have access to the fields needed for the expression.

Reply


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