NAV070 - Add Customer Name to to the report  [SOLVED]

Questions and Answers asked by users to users, about how others use Jet Reports.

For additional "how to" information, please visit our online Knowledge Base https://jetsupport.jetreports.com/hc/en ... ofessional
Forum rules
The Jet Reports Community forums are not a guaranteed support medium. Official support for Jet Reports is available only through the support desk at http://support.jetreports.com. Staff responses to posts on the forum are not guaranteed.
leen789
Posts: 4
Joined: Tue May 23, 2017 12:38 am

NAV070 - Add Customer Name to to the report

Postby leen789 » Tue May 23, 2017 2:52 am

Hi,

I'm new to Jet Reports and I'm trying to add the Customer Name to the existing NAV070 - Sales Orders by Saleperson report. After getting past the inital error of the report looking in the wrong place for the Salesperson Code I am trying to add the Customer Code and Name to the report.

I easily added the Bill-To Customer No. field to the report but I'm now trying to use this field to return the Customer Name from the Customers table using this formula: =NL("First","Customer","Name","No.",$L16). The Bill-To Customer No is in column L. Column M where I have the formula looks blank but when I click on the cell it shows the formula. If I change the formula to include and incorrect field it changes to #VALUE! so I presume I have the formula correct. I was wondering if I need to include the Customers table in the Link section? I have tried to add a link but unsuccessfully.
NAV070 - Sales Orders by Salesperson.xlsx
(109.43 KiB) Downloaded 12 times

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 441
Joined: Wed May 26, 2010 3:40 pm

Re: NAV070 - Add Customer Name to to the report

Postby HPDeskJet » Tue May 23, 2017 8:01 am

The NAV070 report is an NL("Table") report {created using the Table Builder}.

You are correct... you need to add a linked table (the Customer table) in order to get the Customer name.

While this can be done by modifying the report structure by hand or by using the Jet Function Wizard (Jfx), the easiest way to do this is to update the NL(Table) function using the Table Builder.

To do this, you need the template (an XML file) for that report. The templates for the Sample Reports are available (as a zip file) from this article in the Jet Reports online knowledgebase.

Once you've downloaded the ZIP file and extracted the files, you can Edit the Table using the Table Builder.

Additional information about the NL(Table) function and the Jet Table Builder is available.
-HP
_______________________
Jet 2015 Certified Trainer

leen789
Posts: 4
Joined: Tue May 23, 2017 12:38 am

Re: NAV070 - Add Customer Name to to the report

Postby leen789 » Tue May 23, 2017 8:42 am

I have downloaded the templates but when I go to open the template for NAV070 I get an error - Invalid Field 'Salesperson Code' - and the template won't open.

This is the same error I got when I first started looking at this report and that it is looking for the Salesperson Code in the Sales Invoice Line table rather than the Sales Invoice Header.

krd1996
Posts: 16
Joined: Mon Jan 05, 2015 10:57 am

Re: NAV070 - Add Customer Name to to the report

Postby krd1996 » Tue May 23, 2017 2:47 pm

Referencing the salesperson code from the Sales Invoice Line table does not seem standard. I have modified NAV070 to reference the salesperson code field from the Sales Invoice Header table. I will update the download with this change. I've also updated the Table Builder template to reflect this change. I've attached the updated files for your convenience. Hope these will work better for you.

Note: the Table Builder template is compressed in the .zip file.

Regards,
krdjet
Attachments
NAV070 - Sales Orders by Salesperson.zip
(855 Bytes) Downloaded 10 times
NAV070 - Sales Orders by Salesperson.xlsx
(243.97 KiB) Downloaded 13 times

leen789
Posts: 4
Joined: Tue May 23, 2017 12:38 am

Re: NAV070 - Add Customer Name to to the report

Postby leen789 » Thu May 25, 2017 1:43 am

Thanks for the template krd1996. I was able to use this to link in the customer table and get the customer name. I then tried copy and pasting the Customer link and the field into my existing report but I get the following error:

The field name 'LinkField([Customer],[Name])' is qualified with a table name that is not a link table of the function.

Is it not possible to copy and paste links and fields between reports or is there something else wrong with my report? It was the standard report downloaded from Jet and the only thing that I had changed is the getting the Salesperson Code from the Sales Invoice Header rather than the line, it also took me a while to resolve that issue!

I have attached the latest version of the report.
NAV070 - Sales Orders by Salesperson.xlsx
(110.33 KiB) Downloaded 9 times

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 441
Joined: Wed May 26, 2010 3:40 pm

Re: NAV070 - Add Customer Name to to the report

Postby HPDeskJet » Thu May 25, 2017 8:18 am

Hi -

Did you use the Table Builder and the Template (xml) file that KRD provided to make your change?
1 - TblBldr Open.png
1 - TblBldr Open.png (5.67 KiB) Viewed 232 times
2 - TblBldr template.png
3 - TblBldr Add Table.png
3 - TblBldr Add Table.png (17.09 KiB) Viewed 232 times
4 - TblBldr add field.png
5 - TblBldr result.png
-HP
_______________________
Jet 2015 Certified Trainer

krd1996
Posts: 16
Joined: Mon Jan 05, 2015 10:57 am

Re: NAV070 - Add Customer Name to to the report  [SOLVED]

Postby krd1996 » Thu May 25, 2017 9:11 am

When you link a table in table builder, it adds "InclusiveLink=" and "IncludeDuplicates=" to the NL Table function, such as it did for Sales Invoice Line:
eg: =NL("Table","Sales Invoice Line",$E$15:$O$15,"Headers=",$E$14:$O$14,"TableName=","SalesInvoiceLine","Filters=",$C$5:$D$6,"InclusiveLink=Sales Invoice Line",$E$13,"IncludeDuplicates=","True")

Note: the function above still needs the link statements to the Customer table.

The easiest way to do this may be just copying the new NL Table function to your existing report as well, being sure that all cell references are intact. When you copy, you also want to make sure that the "TableName=" statement uses the same table name as specified in your old function ("SalesInvoiceLine" from the example above). This is what the Excel pivot table is referencing.

Regards,
krd

leen789
Posts: 4
Joined: Tue May 23, 2017 12:38 am

Re: NAV070 - Add Customer Name to to the report

Postby leen789 » Fri May 26, 2017 1:33 am

Hi krd,

It was the link to the Customer table that was causing the problem. As soon as I added it in the report worked.

Thanks for all your help and HP too!

Regards
Lee


Return to “How do I?”

Who is online

Users browsing this forum: Bing [Bot] and 1 guest