Displaying the records from 2 tables

(0) Share

Report

Posted on by Community Member

Hello,

I want to write a codeunit where for a particular customer no. the records has to be displayed from 2 different tables i.e Sales header & Sales Invoice Header.

Can anybody help me in displaying the data from these 2 tables ?

When the particular customer no. is filtered then the data from both the tables has to combined and displayed.

Please help me its an urgent requirement.

Thanks in advance.....

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (2)

Suggested answer

Binesh 7,885 on at

Like (0)

Report

RE: Displaying the records from 2 tables

Hello Pushpa,

Declare Record Variable of sales Header and Sales Invoice Header.

and write code something like:

SalesHeader.RESET;
SalesHeader.SETRANGE("Sell-to Customer No.",Cust."No.");
IF SalesHeader.FINDSET THEN
REPEAT

MESSAGE(SalesHeader."No.");
//Here you get all filtered record
// for displaying Records you have to use some container
// So your data will be displayed.
UNTIL SalesHeader.NEXT = 0 ;

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Displaying the records from 2 tables

This is only for sales header and similar if i write for invoice header then in two different forms i will get data.

I have to display the data in a single tablular form from both the tables one after another.

Was this reply helpful? Yes No
Verified answer

Binesh 7,885 on at

Like (0)

Report

RE: Displaying the records from 2 tables

You have to create 2 forms for each, otherwise if you need only selected fields then store in temporary table, and that temporary table show in form

SalesHeader.RESET;
SalesHeader.SETRANGE("Sell-to Customer No.",'10000');
IF SalesHeader.FINDSET THEN
REPEAT
TempSH.INIT;
TempSH."No." := SalesHeader."No.";
TempSH."Sell-to Address" := SalesHeader."Sell-to Address";
TempSH.INSERT;
UNTIL SalesHeader.NEXT = 0 ;

SalesInvoiceHeader.RESET;
SalesInvoiceHeader.SETRANGE("Sell-to Customer No.",'10000');
IF SalesInvoiceHeader.FINDSET THEN
REPEAT
TempSH.FINDLAST;
TempSH."No." := SalesInvoiceHeader."No.";
TempSH."Sell-to Address" := SalesInvoiceHeader."Sell-to Address";
TempSH.INSERT;
TempSH.NEXT; // for move next record
UNTIL SalesInvoiceHeader.NEXT = 0 ;

Note:

Here TempSH is a Temporary table of [Sales Header or Sales Invoice Header], or you can also create new Table and it has fields from both table based on your requirement.

Now you can see your data after inserting data in TempSH table, you can call a form, that form has Source table is TempSH.

Was this reply helpful? Yes No
Verified answer

Ashwini Tripathi 4,624 on at

Like (1)

Report

RE: Displaying the records from 2 tables

Using temp table will be right choice, create a structure as desired. As most of the fields in above example tables are common so you can use existing any of both table as temp and use transfer fields and insert statement to add your records to temp record variable post applying filters. Write a function to manipulate the records and call it onvalidate trigger of customer no filter field.

You will write code itself on Page, think of approach and design as per your requirement before you start designing.

Make sure you specify temporary property of variable also delete all records from table before you add new set of records post changing filters and retrieving new set of records. This is common mistakes done by developers in such situation.

If using higher version Query will be good approach.

If you just want to show records you can use report too.

Was this reply helpful? Yes No
Suggested answer

Amol Salvi 18,698 on at

Like (0)

Report

RE: Displaying the records from 2 tables

Use filter as customer no to filter the record from both the table. You will desired result

Was this reply helpful? Yes No
Suggested answer

Roberto Stefanetti 12,998 on at

Like (1)

Report

RE: Displaying the records from 2 tables

hi,

look at these pages of NAV online help on MSDN, may be useful.

Essential C/AL Functions

msdn.microsoft.com/.../dd355257(v=nav.90).aspx

GET, FIND, and NEXT Functions

msdn.microsoft.com/.../dd338616(v=nav.90).aspx

Using Codeunits

msdn.microsoft.com/.../dd301069(v=nav.90).aspx

Was this reply helpful? Yes No
mmv 11,471 on at

Like (0)

Report

RE: Displaying the records from 2 tables

Hi,

You may use a temporary table to insert the data from both the tables and use it as source for your page or form.

Was this reply helpful? Yes No
Suggested answer

Rabin 2,976 on at

Like (0)

Report

RE: Displaying the records from 2 tables

True, Since "Customer No" is common in both the table, use this to filter both the tables....

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,096 on at

Like (0)

Report

RE: Displaying the records from 2 tables

You can't use one page for two tables, you would need to create a temporary table, fill it with needed data and then display via needed page: msdn.microsoft.com/.../dd355211.aspx

Was this reply helpful? Yes No
Suggested answer

Tharanga Chandrasekara 23,118 on at

Like (0)

Report

RE: Displaying the records from 2 tables

You do not have the option of linking two tables to one page. In Microsoft Dynamics NAV, it is a one to one relationship with tables and pages. As for a solution for your requirement, best option is to create an table and then populate the data to it.

If you only need these for a display purpose, then the best option is to populate the data to the newly created table at the run time (As temp table). By doing this you do not have to worry about data integrity of the sales header and new table, because it only create the table data at the time of running the function.

If you need any help regarding the C/AL coding please do get back to us.

Was this reply helpful? Yes No