How to combine dates from 2 different tables.

(0) Share

Report

Posted on by mjcjyk

I am creating a report using the Sales Header and Sales Invoice Header table. Both tables have Order Date as a field. How can I combine the Order Date from both the Sales Header and Sales Invoice Header tables into one? For example I have order date of 1/1/16 in Sales Header and I have order date of 1/1/16 in Sales Invoice Header, I would like to be able to combine both to show 1/1/16 on the report.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

Suggested answer

Alexander Ermakov 28,096 on at

Like (0)

Report

Perhaps, Walkthrough: Designing a Report from Multiple Tables can be helpful: msdn.microsoft.com/.../jj551758(v=nav.70).aspx

Was this reply helpful? Yes No
Suggested answer

Amol Salvi 18,698 on at

Like (0)

Report

You need to use either dataitemlink property or you need to write a program and using SETRANGE you can achieve the same

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,247 Super User 2025 Season 2 on at

Like (0)

Report

The order date from the Sales Header table is transferred to the Sales Invoice Header during posting, so mostly it is the same value. Unless, if you do partial invoicing and change the order date for each invoice which I am not sure why we do because it is a order date. So what do you mean by combining

Was this reply helpful? Yes No
Suggested answer

Yogesh Kulkarni_ 1,762 on at

Like (1)

Report

Exactly, as suggested by Suresh Sir, Order Date gets transferred from Sales Header to Sales Inv. Header and will be the same until and unless you are not invoicing partially.

What is your exact requirement?

Do you need to see the Orders and invoices on the same date?

If yes, then you can filter on the records in Sales Inv. Header as per the date from Sales Header using SETRANGE or SETFILTER.

-Yogesh Kulkarni
Please verify, if you find answer helpful.

Was this reply helpful? Yes No
ManishS 86 on at

Like (0)

Report

You can compare the dates also to check if there is any difference.

Was this reply helpful? Yes No
Suggested answer

RockwithNav 8,658 Super User 2025 Season 2 on at

Like (0)

Report

What do you mean by

For example I have order date of 1/1/16 in Sales Header and I have order date of 1/1/16 in Sales Invoice Header, I would like to be able to combine both to show 1/1/16 on the report.

Can you please elaborate it it's not yet clear to me?? Obviously both columns will have same values what exactly you trying to achieve?? OR if there is some other case where you have two different dates and you want to do some sort of date manipulations????

Was this reply helpful? Yes No
mjcjyk 35 on at

Like (0)

Report

This is what I am trying to do. I have created a report that shows Sales by Month for each sales person. I am using a Matrix to create this report. The rows are the sales people and the column is the month.

In the circled part that is where the date information is going. I have the following formula there: =MonthName(Month(Fields!OrderDate_SalesHeader.Value),False) & MonthName(Month(Fields!OrderDate_SalesInvoiceHeader.Value),False)

But when I run the report it comes out like this:

I would like it to show only the month once. So it should just be January and February showing. Hope this explains what I am looking for.

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,096 on at

Like (0)

Report

Sorry for asking, but I'm a bit confused of the purpose of this report. If you would like to show actual Sales by Month, why do you ever need unposted sales invoices to be taken into consideration?

Was this reply helpful? Yes No
mjcjyk 35 on at

Like (0)

Report

Because I want to see actual sales that were booked and the only way I can see that is by using Order Date in both the Sales Header and Sales Invoice Header tables.

Was this reply helpful? Yes No
Verified answer

Rajasekhar@MS 5,569 on at

Like (0)

Report

Hi

Try by using Date as your first dataitem indent Header and Invoice Data Items and in your column grouping use Date as expression.

Was this reply helpful? Yes No