Items by Location Report

MMSNavUSR

I am attempting to create a report that will list items by location. I would like to filter the report by "Location Code" and "Item Category Code". I am having great difficulty as I cannot find a table, or reference that I can use to determine items by location; additionally, I am having issues forcing filter on "Location Code" and "Item Category Code" rather than by "Item No." -- any leads and or assistance will be greatly appreciated.

Alex_Chow

Have you taken a look at the Inventory Valuation report then checking on By Location checkbox on the options tab?

MMSNavUSR

Alex,

Thank you for your response. I was unable to find the report you refered to; however, I was able to find a report "Physical Inventory Count Sheet" #10151 that refered to a table "Stockkeeping Unit" #5700... I rebuilt my report to include this form.. and I discovered an interesting error... I am unable to print the text from "Description" or "Description 2" to my report... the text from "Item No." comes in just fine.

Just for reference, this is being used as a label... I am needing the Item # and Description fields (Description + Description 2 or Search Description (Custom)). I have already created a working version but needed it to be specific to our individual locations (so as to produce less waste).

Savatage

on the Item Journal Line dataitem you're going to need to add code to get the item table information.

Something Like..
Create Global Variable:
Name=Item
Data Type=Record
SubType=Item

OnAfterGetRecord()
IF "Item No." <> '' THEN
Item.GET("Item No.")
ELSE
CLEAR(Item);

Now you should be able to add fields to your section(s)
with sourceexp of Item.Description+' '+Item."Description 2" (if you want to combine)

PS..how can you NOT find Inventory Valuation report. It's a staple of the system.

For help with reports check the Application Designers Guide that comes with the product cd.
Else you can download & printout the "5" version here:
http://www.microsoft.com/downloads/en/d ... laylang=en

w1w1adg.pdf

MMSNavUSR

Savatage,

That worked great... Thanks a ton! I now have a stack of barcoded labels printed off from a specified location including the "Item no." and "Search Description".

I would like to take this a step further... When loading the report the "Sort" options come up... is there a way to preset which options are available? It is currently showing "Location Code", "Item No.", and "Variant Code" we would not use "Item No." or "Variant Code" in our printing procedure. Additionally I would like to be able to sort by "Item Category Code" but it is not a column in the "Stockkepping Unit" table (it is found in the "Item" table).

Savatage

The order in which the information appears is determined by the sorting order defined by the current key.
If more than one key is defined for the table, you can switch between the sorting orders that these keys define.

Click on the link above, then click on the w1w1adg.pdf download. once Open click Ctrl-F (Find) Type in Sorting & (F3) to see how valuable a doc this is.

Also as valuable if not more is the Search here on Mibuso.
search.php

Almost any question you can think of has probably been asked a few times before.

MMSNavUSR

Wow, how much fun we can all have if we are steered in the right direction!

I am closing in on making this the perfect label... I have been able to add the ability to sort by "Location Code" in addition to "Item Category Code: and again I have run into an issue with data not being available to me. I am utilizing two tables to gather the data: "Item" and "Stockkeeping Unit" I am trying to add the "Location Code" to my label... the location code is a sort filter and is in a separate table than I am doing the C/AL coding in...

Addr[ColumnNo][1] := '*' + FORMAT(Item."No.") + '*';
Addr[ColumnNo][2] := FORMAT(Item."No.");
Addr[ColumnNo][3] := Item."Search Description";
Addr[ColumnNo][4] := 'LOCATION ID: ' + "Stockkeeping Unit"."Location Code" + ' PRINTED: ' + FORMAT(TODAY,0,4);

"Stockkeeping Unit"."Location Code" is returning null... Each of the labels should have the same "Location Code" depending on what the usr selections upon loading the report.

Any ideas, thoughts or direction will be greatly appreciated.

Savatage

MMSNavUSR wrote:

"Stockkeeping Unit"."Location Code" is returning null... Each of the labels should have the same "Location Code" depending on what the usr selections upon loading the report.

What do you mean by "depending on what the user is selections upon loading the report"

Is there a request form where they are selecting or entering the location code?
Are you entering a a "location filter" on a dataitem? which?

Alex_Chow

You're going in the wrong direction I think...

Take a look at the report Inventory Valuation (Report ID 10139). It's a standard report in Navision. On the options tab, check on Breakdown by Variant and Breakdown by Location.

No custom coding should be necessary.

MMSNavUSR

Savatage wrote:

What do you mean by "depending on what the user is selections upon loading the report"

Is there a request form where they are selecting or entering the location code?
Are you entering a a "location filter" on a dataitem? which?

Yes, There is a request form with two tabs:

1. "Item" Consisting of required field/filter "Item Category Code"
2. "Stockkeeping Unit" Consisting of rquired field/filter "Location Code"

The purpose of this excersize is to create an easily manageable label solution allowing me/others to print per location within a selected "Item Category"

For example: for location "1" I need labels for "WIDGETS"... in our system that category may have 10,000 items of which location 1 only caries 2500.

On my label I am including when the label was printed, and what location it was printed for... for tracking when we need to update the labels.

My report works and I printed over 2,000 labels yesterday... I had to manually add the "Location Code" I would like for it to be automatic.

Savatage

So you want to get one of the values used as a filter in the report.

Try GETFILTER
http://msdn.microsoft.com/en-us/library/dd355306.aspx

Usually looks something like this - you can see it working in many reports.
OnPreReport()
FilterString := "Stockkeeping Unit".GETFILTERS;

Then the text variable Filterstring will hod the value you are looking for.
and you can use it as the sourceexp of a textbox to print on the report.

MMSNavUSR

Thanks! Worked like a charm.