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.

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

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.

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?

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.

 

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

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.
 

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? 

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.

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

  

   

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.

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 

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/