Skip to main content
Microsoft Dynamics NAV forum
Unanswered

NAV - Convert Rows of one table to Columns of another table

editSubscribe (0) ShareShare
ReportReport
Posted on by UG Leader

Here’s the scenario:

I have two tables, one is Salary Statement and another one is Salary Statement Details.

Salary Statement Table

Year

Month

Employee ID

Basic

Total Allowances

Total Bonus

Gross Pay

Total Deductions

Total TDS

Adjustments

Net Pay

2018

November

221344

40000

20000

5000

65000

6000

1000

0

58000

 

Salary Statement Details Table

Employee ID

Year

Month

Detail_Code

Detail_Type

Amount

221344

2018

November

Medical_Allowance

Allowance

5000

221344

2018

November

House_Rent

Allowance

15000

221344

2018

November

Festival_Bonus

Bonus

5000

221344

2018

November

Loan_Deduction

Deduction

6000

 

Total Allowances is a calculated column in Salary Statement table. Total Allowances column value comes from Salary Statement Details table. Salary Statement Details table have all the allowances as a row.

Now, I want to create a report in NAV to Show all the Salary Statement table’s fields and all the related rows from Salary Statement Details table as Column and ultimately it will look like:

 

Employee ID

Year

Month

Basic

House Rent

Medical Allowance

Festival Bonus

Gross Pay

Loan Deduction

TDS

Adjustments

Net Pay

221344

2018

November

40000

15000

5000

5000

65000

6000

1000

0

580000

 

Here the allowance, bonus and deduction fields are added as columns which were in rows of earlier table.

How can I do that? Thanks in advance.

  • RockwithNav Profile Picture
    RockwithNav 6,560 on at
    RE: NAV - Convert Rows of one table to Columns of another table

    May be you can make a Matrix Report for this. RDLC Report Matrix will be easier.

  • Hannes Holst Profile Picture
    Hannes Holst 5,763 on at
    RE: NAV - Convert Rows of one table to Columns of another table

    Hi,

    You could create FlowFields with the Method "Lookup".

    Have a look in Table 5065 "Interaction Log Entry", Field 39 "Contact Name"

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Sundar…

Industry experts answer burning questions directly from our amazing Community…

Enabling Copilot Case and Conversation…

Agents can easily recap an ongoing chat, transcribe a voice conversation…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 282,978 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,562 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans