web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Left outer join in GP ...
Microsoft Dynamics GP (Archived)

Left outer join in GP report writer

(1) ShareShare
ReportReport
Posted on by P NOUSHAD 1,821

Hi,

Please find below screen-shot of delivery note by using GP report writer, it prints delivery note details. It print all the line items of sales invoice line items from main table AFT_DO_Report_PERM.  Report is working perfectly up to this point. No issue.

I added one more table called AFTSERNO which holds data of corresponding serial numbers of line items. I want to print these serial numbers also at bottom of each line items. So  I created a table relationship with main table as show below screen-shots. But once I created relationship with main table, reports print only the line items with serial numbers.  So its look like when create table relationship, it create INNER JOIN between tables, so reports contain only if records of two table are matching. Please find screen-shot of second report with serial numbers.  This reports does not print line items XL510AV and 226-25 since there is no serial numbers on these line items.

I added additional header and move the line items on this header and print serial number put on body of the report. 

Kindly help me how to solve this issue, we need line items without serial numbers also on this report

SER1.png

ser6.png

ser2.png

SER3.png

Report after modification

ser4.png

SER5.png

MAIN TABLE

SER7.png

SECONDARY TABLE

SER8.png

Microsoft Dynamics GP 2013

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: Left outer join in GP report writer

    Your problem is not one of Report Writer, but rather one of table design. If you add each expected serial number as a column, then when you add each field to the report, it will all print on a single line. Instead, it seems, your table should be defined as

    Delivery Number

    Item Number

    Line Item Sequence

    Serial Line

    User ID

    Serial Number

    Your key for this table, should probably be Delivery Number, Item Number, Line Item Sequence and Serial Number

  • P NOUSHAD Profile Picture
    P NOUSHAD 1,821 on at
    RE: Left outer join in GP report writer

    Hi Mariano Gomez,

    Please find last screen-shot of my previous mail, SECONDARY TABLE , its primary key is in this order only (Delivery Number, Item Number, Line Item Sequence, SERIAL line seq no, and User ID)  and this table has secondary key (Delivery Number, Item Number, Line Item Sequence and User ID) .  You can find  in table relationship definition , the secondary table linked to primary table .  Primary key of the FIRST table linked to secondary table of SECOND table.  

    In breif, the secondary table is used to store the serial numbers of item in six fields of each line like Delivery Number, Item Number, Line Item Sequence, SERIAL line seq no, ,User ID, SERIAL NO 1, SERIAL NO2, SERIAL NO3, SERIAL NO4, SERIAL NO5 and SERIAL NO6) .  If one item contains 12 serial numbers , the data will be store in 2 records in SECOND TABLE , first record contains first 6 serial numbers and second record contain from 7 to 12 serial numbers, for each record  SERIAL line seq no value will be 1,2, etc .  

    Primary table store all the invoice items including items without serial numbers also.   Now query is how to bring data on report, sales line item details should print first line then its corresponding serial numbers should print below.  If there is no serial number then nothing should print below the sales line item .   Here the issue is if there is no serial number of sales invoice item  then the sales line item also not printing  on report.

    I hope this clear to you and provide solution.  I select the records of both table in sql server, I could find all the records are available in first table including sales line items without serial number.  The second table contains the corresponding serial numbers of all sales line items.  If the item is not enabled for serial number then its data will not be stored in this table,

     

     

     

  • Suggested answer
    Justin Thorp Profile Picture
    Justin Thorp 2,265 on at
    RE: Left outer join in GP report writer

    Hi,

    On the "Report Defintion" window, look into the "Skip Blank Records" checkbox.  Hitting F1 to get the help available in GP:

    Skip Blank Records

    Includes only records for which there is corresponding data in the main and related tables. For example, you might want to select this option if your report uses the Account Master table as the main table, with the Year-to-Date table related to it using the account number field. If Skip Blank Records is checked, accounts for which there is no data in the Year-to-Date table will be excluded from the report. If this option isn't selected, each account in the Account Master table will be included in the report, regardless of whether there is any corresponding data in the Year-to-Date table.

    Justin

  • P NOUSHAD Profile Picture
    P NOUSHAD 1,821 on at
    RE: Left outer join in GP report writer

    Hi,

    Thanks for your reply,  here issue is how to bring all data on report.  in brief,  Main table contains all the records, secondary table contain only corresponding records with serial numbers.  There will not be any records, if item is not serial number enabled or item does not contain serial number.  Main table and secondary table are linked.  Data of main table print on additional header and corresponding serial number of item print on body of report.   If I linked Main table and secondary table then items without serial number will not be printed.  So I think when we link main and secondary table through table relationship, system creates INNER JOIN so it print if both table contain same records.  So I want to know if there any option to create LEFT OUTER JOIN from main table to secondary table, so it will print all records.  or Kindly provide a solution for this type or situation.  Please refer my first mail for details with screen-shots.

    Regards,

    Noushad

     

  • P NOUSHAD Profile Picture
    P NOUSHAD 1,821 on at
    RE: Left outer join in GP report writer

    Hi All,

    Any update on below issue.  Kindly help me to find a solution.

    Regards,

    Noushad

  • Justin Thorp Profile Picture
    Justin Thorp 2,265 on at
    RE: Left outer join in GP report writer

    Hi,

    Can you run your report once with the "Skip Blank Records" checkbox marked, and then run it again with the "Skip Blank Records" checkbox unmarked and compare results?  (and let me know your findings)

    The "Skip Blank Records" checkbox is the answer you are looking for.

    Justin

  • Suggested answer
    P NOUSHAD Profile Picture
    P NOUSHAD 1,821 on at
    RE: Left outer join in GP report writer

    Hi,

    Thanks for your replay and solution.  By using "Skip Blank Records", we can solve this issue.

    Noushad

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans