Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Joe Gorman]

?Hello All,
Is there a report (NAV2015) that you can find all the purchase orders created in a given month; e.g. all PO’s with a create – document date of 9/1..9/30?  If I look at the PO list and filter for the date I will miss any that have been fully rec’d and invoiced.  I don’t think I can trust Archive list.
The posted lists will miss the orders not rec’d and invoiced.
Is there a report that I can filter by date?
Regards,

——————————
Joe
——————————

[Jim Wilson]

Hi Joe,

What is it that you want to do with this information or why is it that you need it? Why is it that you cannot trust the archive? This additional info might help with directing you e.g. you could also perhaps turn on the change log for inserts into the Purchase Header table. This could be useful if all you wanted to know was purchase orders created (and by who) in a month but that may not meet your need depending on what you want to do with the information. Also that would only be good on a go forward basis.

Hope this helps a little.

——————————
Jim Wilson
Independent Consultant
Launch Project Management Ltd
——————————
——————————————-

[Joe Gorman]

 

Hi Jim,

We want to know how many new Purchase Orders were created the past month and what was the total value of those orders. Similarly, we want to know the same information for Sales Orders – how many Sales Orders and what was the total value.

I turned on insert in the Change Log last month and have started to get data, but it takes a fair bit of manipulation to get the results. For example (switching to the Sales data) when I export Sales Lines Change Log to excel all values are text so I then change the Amounts to values to add them.

Change Logs are on single tables so if I export the Purchase Lines I can see the Vendor Number but not the Vendor name (which has been asked for) – I can add a vlookup to the results but what would be ideal is if there were already reports in NAV that can show me this data.

And better yet if I can point the users to those reports.

[I have auto archiving turned on but it too would require significant manipulation to get total value of Purchase Orders or Sales Orders for a given month]

Thanks and regards,

Joe

?

——————————
Joe
——————————
——————————————-

[Mathew Ealy]

For this I’d write a SQL report and export to Excel. 

SQL
SELECT
P.[No_] ,
P.[Vendor No_] ,
P.[Vendor Name] .

FROM
Company$.PurchaseHeader P 

WHERE
P.[Date] BETWEEN dd/mm/yyyy AND dd/mm/yyyy 

Or something like that.

——————————
Mathew Ealy
Nav Support Specialist
Rentokil N. America
Reading PA
——————————
——————————————-

[Ted Johnston]

As you’ve probably figured out, the Purchase Header table is cleared once the PO is fully invoiced. If you have a fast turn around time, you cannot meet your goals using the Purchase Header table.

Further, the answer is more challenging if the PO is modified after creation. Example 1, I issue a PO to a vendor and the vendor responds with a price change or a notice that one or more items are discontinued. Example 2, I issue a PO on 9/1, receive it on 9/25, but invoice it on 10/5 with a cost change. NAV costing will handle this, but what answer do you want- the original issue $ or the invoiced $.

The Posted Purchase Invoice table will give you the amount invoiced during the month, but not the amount outstanding.

Purchase Archive table would help, but you have to ensure that every PO issued (Released?- depends on your work flow) is archived. You also have to answer the question of PO changes over time and how to make sure those get into the archive table.

Tough question to answer as the question may have multiple answers and the answer varies over time as POs change.

We have a modification that actually posts open PO totals to the GL Entry table (I know, I know, it’s not recommended) each evening. This way, we can use the various G/L reporting tools to know how our open PO number varies over time. We do the same for open SOs.

——————————
Ted Johnston
IT Director
Streicher’s
Minneapolis MN
——————————
——————————————-

[Joe Gorman]

??Thanks Ted and Matthew,
Ted you are right that we can’t rely on the Purchase table (Header or Lines) as the orders aren’t available for querying once they are posted.
I have a query that looks in both the Purchase tables and posted receipt  (and another for Sales Orders that looks at sales hdr and posted shipment)  which seems to work but I was hoping Microsoft might have included a report to do this.
Regards,
Joe

——————————
Joe
——————————
——————————————-

[Jimmy Yu]

Hi Joe,

I know this isn’t quite the answer you’re looking for, but I thought I’d provide my two cents worth.

NAV doesn’t not have a report for PO’s created in a certain time period due to how the Purchase Header and Line table function, as you already know.

The Change Log suggestion from Jim is probably the way I would have gone too.  To get the data you need from the Change Log, build your own report off this data.  I use Jet Reports and stitch the data together.  All the user has to do is run the Jet Report with their date parameter and get the data they need.  You’ll need to tweak the Change Log setup to find the right combination of data you’re looking for.  I started off with tracking everything for the Purchase Header and Line table (insert and modify).  Then I turned off fields that appeared redundant and not useful for our purposes after a week.  Reviewing the raw data will help you determine what NAV auto populates that’s not useful for your users.  Keep tweaking the setup until you get the right combo.  Then grab the last records available for Purchase Header and Line available and hopefully that will give you what you need.

My company uses ReQlogic/DXC Procurement to approve and create PO’s.  This has helped me in that I just grab all Purchase Header inserts with the UserID I assigned to ReQlogic/DXC.  Again, it’s not perfect because when a user alters a PO before receiving it, we don’t get that data.  But that’s not what we’re interested in.  We’re interested in knowing the PO’s “created”, so ReQlogic/DXC is my archive table after all approvals are obtained.

Let us know where you land in the end so that we can all learn from your experience.

——————————
Jimmy Yu
Enterprise Architect
Step Energy Services Inc
AB
——————————
——————————————-