Skip to main content
Solved

Text not printing in report


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img

Hey everyone. I’m working on the Change Order report, and I’ve taken the heading from our Invoice report and brought it over. All necessary tables are joined properly, but the text prints as empty when in the Change Order report despite containing text in the Invoice report.

 

Best answer by meganfriesen37

You’ll need to add the customer table.  Highlighted below in the picture.  It should be there, as it’s part of the core Acumatica functionality.
 

 

View original
Did this topic help you find an answer to your question?

19 replies

meganfriesen37
Captain II
Forum|alt.badge.img+11

I’m not sure what additional tables you may have added, but on the standard Change Order report (PM643000), there are no Contact/Address tables with the “BillingAddress” or ShippingAddress Alias.  You’ll likely need to add those additional tables and joins if you haven’t already.  You can get the Project Address by joining PMProject (Left) PMAddress on BillAddressID = AddressID.

You also may need to look at changing the ARInvoice.ProjectID field and try replacing it with the PMChangeOrder.ProjectID since there’s no ARInvoice record associated with a Change Order.


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 25, 2023

Here’s my relationships table. Each has the appropriate data field links as well, such as BillAddressID equal AddressID in BillingAddress.


meganfriesen37
Captain II
Forum|alt.badge.img+11

How are you linking the Change Order table to the AR Invoice Table?

Instead of using AR Invoice, perhaps go from PMChangeOrder to Customer, linking the CustomerID to BAccountID… and then link the Customer to the Address and Contact fields


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 25, 2023
meganfriesen37 wrote:

How are you linking the Change Order table to the AR Invoice Table?

Instead of using AR Invoice, perhaps go from PMChangeOrder to Customer, linking the CustomerID to BAccountID… and then link the Customer to the Address and Contact fields

This is how it’s linked.


meganfriesen37
Captain II
Forum|alt.badge.img+11

The RefNum on the Change Order is not linked to the RefNum on the Invoice.  There is no link from the Change Order to the Invoice as far as I know.

You will need to link from the Change Order to the Customer and then to the addresses on the Customer/Customer Location


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 25, 2023
meganfriesen37 wrote:

The RefNum on the Change Order is not linked to the RefNum on the Invoice.  There is no link from the Change Order to the Invoice as far as I know.

You will need to link from the Change Order to the Customer and then to the addresses on the Customer/Customer Location

How do I do this?


meganfriesen37
Captain II
Forum|alt.badge.img+11
Link from Change Order to Customer

 

Link from Customer to Address for Customer Billing Address
Link from Customer to Contact for Customer Contact Info

To Link to the Address / Contact on the Shipping Details tab of the Customer record, you’ll do 2 similar links above, but the parent field will be DefLocationAddressID and DefLocationContactID

To Link to the Main Contact/Address on the first tab of the Customer you’ll use DefAddressID and DefContactID.


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 25, 2023
meganfriesen37 wrote:
Link from Change Order to Customer

 

Link from Customer to Address for Customer Billing Address
Link from Customer to Contact for Customer Contact Info

To Link to the Address / Contact on the Shipping Details tab of the Customer record, you’ll do 2 similar links above, but the parent field will be DefLocationAddressID and DefLocationContactID

To Link to the Main Contact/Address on the first tab of the Customer you’ll use DefAddressID and DefContactID.

I don’t have the Customer table, and don’t see it in the site schema either


meganfriesen37
Captain II
Forum|alt.badge.img+11

You’ll need to add the customer table.  Highlighted below in the picture.  It should be there, as it’s part of the core Acumatica functionality.
 

 


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 26, 2023
meganfriesen37 wrote:

You’ll need to add the customer table.  Highlighted below in the picture.  It should be there, as it’s part of the core Acumatica functionality.
 

 

I did this and it worked for the Billing Contact, but how do I link up the Shipping Contact? 

 


meganfriesen37
Captain II
Forum|alt.badge.img+11

For the shipping side, assuming you want to use the details on the shipping tab of the customer, you’ll do the same thing you did to get the bill to address.

On the lines that you currently have, leave the Child Alias the same and update the tables and joins below:

Replace:

ARInvoice | Left | ARAddress | ShippingAddress

Change to:

Customer | Left | Address | ShippingAddress

and join on 
DefLocationAddressID equals AddressID

 

Replace

ARInvoice | Left | ARContact| ShippingContact

Change to:

Customer | Left | Contact| ShippingContact

and join on 
DefLocationContactID equals ContactID

 

If you want to link to the Main Contact/Address on the first tab of the Customer you’ll use DefAddressID and DefContactID (instead of DefLocationAddressID / DefLocationContactID)


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 26, 2023
meganfriesen37 wrote:

For the shipping side, assuming you want to use the details on the shipping tab of the customer, you’ll do the same thing you did to get the bill to address.

On the lines that you currently have, leave the Child Alias the same and update the tables and joins below:

Replace:

ARInvoice | Left | ARAddress | ShippingAddress

Change to:

Customer | Left | Address | ShippingAddress

and join on 
DefLocationAddressID equals AddressID

 

Replace

ARInvoice | Left | ARContact| ShippingContact

Change to:

Customer | Left | Contact| ShippingContact

and join on 
DefLocationContactID equals ContactID

 

If you want to link to the Main Contact/Address on the first tab of the Customer you’ll use DefAddressID and DefContactID (instead of DefLocationAddressID / DefLocationContactID)

I don’t have DefLocationAddressID or DefLocationContactID, and using DefAddressID and DefContactID breaks the report and it won’t run.


meganfriesen37
Captain II
Forum|alt.badge.img+11

If you want the project address and contact, you can get the Project Address by joining PMProject (Left) PMAddress on BillAddressID = AddressID.

On the Customer DAC you can see how the different tables are linked and what field they link on (this is from 22R2, Sales Demo)

  • Because this is referencing the “location” it looks like you may need to join to the Location table to get the Customer’s default location and then do the address/contact link.
  • Either link to the Customer’s default location using the references below:
  • Or you could look at linking to the Customer’s location from the project (but then, you might as well using the Project Address field referenced above, as that’s probably easier)
    • PM Project  to Location:

       

  • If you add the Location table to your report (same as you added Customer), then link on the basis outlined below from Location to Address and Contact.  Both Address and Contact will need to have the ShippingAddress/ShippingContact Alias

     


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 26, 2023
meganfriesen37 wrote:

If you want the project address and contact, you can get the Project Address by joining PMProject (Left) PMAddress on BillAddressID = AddressID.

On the Customer DAC you can see how the different tables are linked and what field they link on (this is from 22R2, Sales Demo)

  • Because this is referencing the “location” it looks like you may need to join to the Location table to get the Customer’s default location and then do the address/contact link.
  • Either link to the Customer’s default location using the references below:
  • Or you could look at linking to the Customer’s location from the project (but then, you might as well using the Project Address field referenced above, as that’s probably easier)
    • PM Project  to Location:

       

  • If you add the Location table to your report (same as you added Customer), then link on the basis outlined below from Location to Address and Contact.  Both Address and Contact will need to have the ShippingAddress/ShippingContact Alias

     

I have the join you mentioned and still don’t have the fields you said, the DefLocationAddressID and the other.


meganfriesen37
Captain II
Forum|alt.badge.img+11

From Location to Address/Contact, use the key fields from the last screenshot in my previous message.


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
  • Author
  • Freshman II
  • 67 replies
  • April 27, 2023
meganfriesen37 wrote:

From Location to Address/Contact, use the key fields from the last screenshot in my previous message.

I don’t understand how key fields work or how to join them? Where do I even go to get there? This is all very confusing


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img
ALEXSLUSSER15 wrote:
meganfriesen37 wrote:

From Location to Address/Contact, use the key fields from the last screenshot in my previous message.

I don’t understand how key fields work or how to join them? Where do I even go to get there? This is all very confusing

Anyone able to help simplify this for me?


ALEXSLUSSER15
Freshman II
Forum|alt.badge.img

@meganfriesen37 

ALEXSLUSSER15 wrote:
ALEXSLUSSER15 wrote:
meganfriesen37 wrote:

From Location to Address/Contact, use the key fields from the last screenshot in my previous message.

I don’t understand how key fields work or how to join them? Where do I even go to get there? This is all very confusing

Anyone able to help simplify this for me?

 


meganfriesen37
Captain II
Forum|alt.badge.img+11

Hi @ALEXSLUSSER15 

If you haven’t taken the Report Designer course through Open University, I think it would be helpful to you in terms of learning how to join the various tables and use the DAC Schema Browser: https://openuni.acumatica.com/courses/reporting/s150-reporting-report-designer/ 
 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings