Share
Report
1,821Please 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
Report after modification
MAIN TABLE
SECONDARY TABLE
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,
*This post is locked for comments
All responses (
Answers (
