Trying to pull in "Inventory" from item table

johnsogj

hello, I'm running a report over the sales line table. I need to pull in the on hand quantity from the item table. I tried:


ItemTable.GET("No.");
QtyOnHand := ItemTable.Inventory;

however, I believe the reason the report is not finding it is because the key for the item table is "Item" (data type = text) but the "Inventory" field has data item = decimal.

does anyone have any suggestions as to how I can work around this? Is there a way to convert data types?

Sog

Inventory is a flowfield.
so Calcfields should do the trick

johnsogj

i tried this:


ItemTable.GET("No.");
ItemTable.CALCFIELDS(Inventory);
QtyOnHand := ItemTable.Inventory;

and this

ItemTable.SETCURRENTKEY("No.");
ItemTable.SETRANGE("No.", "Sales Line"."No.");
ItemTable.CALCFIELDS(Inventory);
QtyOnHand:= ItemTable.Inventory;


neither are working... am I missing something?

Savatage

a few questions.

key for the item table is "Item" (data type = text) but the "Inventory" field has data item = decimal.

key for the table is item?? are you talking about a variable or something?

It should give you a "different type" error on compile - or did you try to use the format function?

mytext := format(mydecimal);

Alex_Chow

ItemTable.GET("No.");
ItemTable.CALCFIELDS(Inventory);
QtyOnHand := ItemTable.Inventory;

This is the right code. Got a few questions:
1. What datatype did you you declare as the ItemTable?
2. What datatype is QtyOnHand
3. When you say "it doesn't work", does it give you error message? Or just display 0?
4. Are you sure the item you're have some quantity on hand?
5. Are you setting any additional filters before the ItemTable?

johnsogj

here are the answers:

1. What datatype did you you declare as the ItemTable? Record
2. What datatype is QtyOnHand? integer
3. When you say "it doesn't work", does it give you error message? Or just display 0? now I get an error message saying "Item No. '' does not exist"
4. Are you sure the item you're have some quantity on hand? yes. I run it wide open. I get "0" for all items
5. Are you setting any additional filters before the ItemTable? the main data item is the sales line table. I filter that for "Outstanding qty" is greater than 0 and "type" = "item"

Alex_Chow

1. The "No." must be the exact item number you're trying to get. How are you getting the sales line table? Where are you putting this code?
2. QtyonHand must be decimal.

jversusj

also, are you putting a dataitemview on the sales line record such that you are only retrieving records where the type = item? If not, you could have a sales line record for resource or G/L Account and that would fail your ItemTable.GET and bomb out the entire report. Try putting a condition on this GET: IF ItemTable.GET("No.") THEN BEGIN ...blah blah .. END;

Savatage

see this one
fob version
http://savatage99.googlepages.com/Repor ... temQOH.fob

text file version
http://savatage99.googlepages.com/Repor ... temQOH.txt

Here's the meat & potatoes

OnAfterGetRecord
IF Item.GET("Sales Line"."No.")
 THEN BEGIN
   Item.CALCFIELDS("Quantity on Hand");
   QuantityOnHand := Item."Quantity on Hand";
END
 ELSE BEGIN QuantityOnHand := 0;
END;

Without the IF

OnAfterGetRecord
Item.GET("Sales Line"."No.");
Item.CALCFIELDS("Quantity on Hand");
QuantityOnHand := Item."Quantity on Hand";

Add a new textbox to the report w/ sourceexp = QuantityOnHand
which is a variable type decimal

johnsogj

thx harry. it works!

MMSNavUSR

I am having a very similar issue... I have tried to utilize the suggestion from Savatage to no avail...

I am utilizing the following structure...

DataItem Name
Customer <Customer>
Integer <Integer>
Item Ledger Entry <Item Ledger Entry>
Integer Footer

I am trying to get Quantity on Hand:

Item Ledger Entry - OnPostDataItem()
Item.GET("Item Ledger Entry"."Item No.");
Item.CALCFIELDS(Inventory);
QuantityOnHand := Item.Inventory;

It is constantly returning 0.00 on the report.

Any ideas, or clues would be kindly accepted.

mohana_cse06

why are you calculating on Item Ledger Entry - OnPostDataItem()?

and where are you showing the QuantityOnHand?

Try it by moving the code to Item Ledger Entry - OnAftergetDataItem..

MMSNavUSR

You will have to forgive me, I am a bit new to all of this.

This report shows items sold by Customer & Date Range. We are needing to be able to see how many of each item is still on hand to make determinations about restocking. We want to add the "Quantity on Hand" to each line of the report for the item it is displaying.

Example:
Item No. Description QOH (Qty on Hand) Etc... Etc...

mohana_cse06

mohana_cse06 wrote:

why are you calculating on Item Ledger Entry - OnPostDataItem()?
Try it by moving the code to Item Ledger Entry - OnAftergetDataItem..

Did you try this?

mohana_cse06 wrote:

and where are you showing the QuantityOnHand?

in which section you are displaying QuantityOnHand?

Savatage

The easiest way to accomplish something like this is use the report wizard. Main Table is the Item Legder Entry Table. Add the fields you want, select the sorting you want, select the grouping you want, select the fields you want to total.

save & compile. Then f3 and add Customer dataitem above Item ledger Entry.
View Properties of Item Ledger Entry Dataitem:
DataItemIndent = 1
DataItemTableView = SORTING(Source Type,Source No.,Entry Type,Item No.,Variant Code,Posting Date) WHERE(Source Type=FILTER(Customer),Entry Type=FILTER(Sale),Invoiced Quantity=FILTER(<>0))
DataItemLink = Source No.=FIELD(No.),Posting Date=FIELD(Date Filter)

On the last groupfooter (i assume that's where you want the qty on hand)
on Presection add *QtyOnHand is a variable type decimal.
IF Item.GET("Item No.") THEN BEGIN
Item.CALCFIELDS("Quantity on Hand");
QtyOnHand := Item."Quantity on Hand" END ELSE BEGIN
QtyOnHand := 0;
END;

See Here: IMport the fob and modify to your needs.
Report 50063-Customer Sales-DateRange
http://www.biloltd.net/Mibuso/Report500 ... eRange.fob

I deleted all the detail lines not sure if you wanted that but that's what's good about the wizard - have it do the complicated part and then modify & add it it.

MMSNavUSR

Savatage,

When I run the report you created I get the following error:

"The Item Ledger Entry table does not have an active key that starts with the following field or fields: Source Type, SOurce No., Entry Type, Item No., Variant Code, Posting Date."

I also created the report following your outline and I get an error:

"You have specified an unknown variable.

Item

Define the variable under 'Global C/AL symbols'."

Savatage

In Globals create a variable called Item - Type Record - Subtype Item
you have to define that table

view the properties of the Item Ledger Entry dataitem
Click on the assistedit of DataItemTableView

Change the key to something you have. try "entry no." or one that has source type if you don't feel like creating a new one with the one specified in the error message.

FYI if you look at the keys of the item ledger entry table I have that key as my 5th one. Perhaps yours isn't enabled?

MMSNavUSR

Savatage,

Thank you! I was able to produce a report... now with a little bit of work I should be able to get the exact data I need. I wish I could dump all of the data you know into a book for reference.

Savatage

You can start with the Application Designers Guide "w1w1adg.pdf"
http://www.microsoft.com/downloads/en/d ... laylang=en

Ad by looking at the guts of some reports to see how those work

MMSNavUSR

Savatage,

I found a small issue... When I run the report it is showing "Quantity on Hand" company wide even after adding the "Location Code" filter to "Item Ledger Entry" is there another step.. or am I missing the boat completely?

Savatage

I believe "Qty on Hand" is being pulled from the item table as you requested.
So putting a filter on the item ledger entry isn't going to do anything.

What do you mean by company wide? Do you have Multiple locations?

IN your previous posts you use
Item.Inventory

My report is using Item."Quantity on Hand" - which is the field name we have.

Do you have a field called Item Inventory? then use that.
The report posted is just a quick report built using the report wizard, as stated in an earlier post.
I did it using the "Item Ledger Entry" table as the base table. I selected some fields and a grouping by item & totaled the ILE "Qty Invoiced Field". Now these might not be the fields you are looking for, I was just guessing. Then as before I Inserted the customer table above it, setting the links needed.

So, what field do you want to get inventory from?

MMSNavUSR

We have multiple locations. I am trying to poll the data by location/customer/date range...

Basically the rundown is as follows...

We need to know how many of a particular item a customer has purchased over a period of time AND we need to know how many of that item is available at a specific location... We are using this as a forecasting tool to insure we have enough of an item in stock to cover future orders.

In my report there is:
Item No.
Description
Sales # of this item sold to the customer
Qty on Hand how many of this item is on hand by location
Sales $ this is Extended Price/Unit Price (Average Sale Price)
Extended Price

MMSNavUSR

I guess the question is...

Is there a way to filter the "Item" table to determine by location what the QTY ON HAND is?

We are pulling the QTY ON HAND from this code:

IF Item.GET("Item No.") THEN BEGIN
Item.CALCFIELDS(Inventory);
QtyOnHand := Item.Inventory END ELSE BEGIN
QtyOnHand := 0;
END;

So in theory all we need to do is filter this by location....

Ideas?

aseigle

Are you using Stockkeeping Units for each location? It will keep track of the inventory per location. Perhaps you can use that;.

MMSNavUSR

aseigle,

Yes.. we are using stockkeeping units.

Savatage

MMSNavUSR wrote:

We need to know how many of a particular item a customer has purchased over a period of time AND we need to know how many of that item is available at a specific location... We are using this as a forecasting tool to insure we have enough of an item in stock to cover future orders.

THe thing that I keep scratching my head over is why specific customers have to be involved in this report.

Unless each one of your customers all buy differnet items?

I can understand seeing what you have in each location & how much you sold during a certain period to figure out how much you need, but to be able to run it by customer doesn't make sence to me. :-k

What good is know that customer ABC bought 50pcs of item 123.
What if then Customer XYZ now wants to buy 50pcs Item 123?
You only allocated for Customer ABC?

So I'm not 100% following - have you looked at running form 491 (Items By Location)?

Andwian

Just curious: is there a field named "Qty. on Hand" on Item table?
Is it other than "Inventory"?

aseigle

Quantity on Hand is the Caption for the Inventory field, field 68.

Andwian

aseigle wrote:

Quantity on Hand is the Caption for the Inventory field, field 68.

Thank you for pointing that out. In ID version, the caption is still Inventory.

MMSNavUSR

Hey Everyone, thanks for the help!

What I ended up doing was:

Scrapped my original report and started over with a new report built entirely using "Item Ledger Entry" and and filtered it by "Location Code", "Date Range", and "Source No." (Customer Code).

-- Notes:

This report allows us to look at what a customer purchased for a given period of time and compare it to what is currently in stock (Quantity on Hand) we can then make a decision on how many of an item we need to stock in order to keep the customer supplied. This is for LARGE customers, those that order upwards of 1,000 of an item at a time.

As a supply company we order items in large quantities and in order not to have too much/or too little inventory on our store shelves we look at a trend of purchases over time... this report allows us to make sure that our large customers are kept happy by not having back-stock or sold out items.

--- Challenges:

"Quantity on Hand" or "Inventory" is a flow field requiring use of "CALCFIELDS"
-- Programming was required to make it work:

IF (CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Item No.")) THEN BEGIN
Item.GET("Item Ledger Entry"."Item No.");
ItemLedgerEntry.RESET;
ItemLedgerEntry.SETCURRENTKEY("Location Code","Item No.","Entry Type","Posting Date");
ItemLedgerEntry.SETRANGE("Location Code","Item Ledger Entry"."Location Code");
ItemLedgerEntry.SETRANGE("Item No.","Item Ledger Entry"."Item No.");
ItemLedgerEntry.CALCSUMS(Quantity);
InventoryQuantity := ItemLedgerEntry.Quantity;
CurrReport.SHOWOUTPUT(TRUE);
END ELSE
CurrReport.SHOWOUTPUT(FALSE);