Using Table *IC Item Loc w IC Item I can produce a report that shows Item#, I/C Item Description, Qty on Hand Plus, Picking Seq, Loc. Problem is with our 4 locations each item takes 4 lines. What I want is a Gridview with location 1, 2, 3 & 4 columns. So it will show item 1 has qty 2 at loc 1, qty 3 at location 2, qty 5 at location 3 and qty 2 at location 4. Is there a table or way to do this?

Thanks,

I can use a calculated column if someone can help me figure out the right function.

I want it do this:

{Qty on Hand Plus} for {Loc} = "WA"

I figured out that I can export physical inventory worksheets to get the result I'm looking for, but still interested in knowing how to produce reports with location columns in Gridview.

Using the IC Items Locations table you could have a filter such as

Trim({Loc})="WA"

to show only WA Items.

David

Hi Susan,

Use the {Composite Table V8.0C} to select the composite view *IC Item Loc with IC Item. This provides a single record for each item/location pair. Use and IF statement to move the QOH into a separate column for each of your 4 locations

Then summarize to item.

Hi Andrew,

Using your method I can create a column for each of our warehouse quantities, however, each item is still listed 4 times using 4 rows. Is there a way to have each item use only 1 row and show the quantity at each location in the 4 columns?

Current result:
Item# Whse#1 Whse#2 Whse#3 Whse#4
AA123 5 0 0 0
AA123 0 7 0 0
AA123 0 0 2 0
AA123 0 0 0 8


Desired result:
Item# Whse#1 Whse#2 Whse#3 Whse#4
AA123 5 7 2 8

Ahh the forum removed the spaces I entered that showed the current and desired results. Hopefully you can figure it out. Also, the forum no longer allows me to edit any of my posts. It says, "This post can no longer be edited because the maximum edit time has expired", even when it's only 5 or 10 seconds after I posted.

I have a client that required such a report and I went to Crystal Reports. Now they have more locations than will fit on a printed page in this layout.

I think the time to edit posts is down to zero......

David

Hello Susan:

To get one Item per line, you need to Summarize the View.

In short:
-Set a Sort Order by Item code
-Click on the Summarize button (the Epsilon character)

And you're right. There's no margin for error on a Post anymore!

One way to get this information is to go into Adagio Inventory and printing Physical Inventory Worksheets selecting "All items from Inventory list" for all locations including qty on hand and creating an export file. Then I can open each location's worksheet in excel and copy the quantity from each and combine them to one spreadsheet. This is a bit tiring but it works.

I was hoping to use Gridview more for inventory control but this location issue seems to keep coming up and prevents me from accomplishing the output I need.

One workaround that helps somewhat is for example a Gridview that have that shows us items that we have orders for but no stock in our warehouse. Before we order it, maybe we should see if one of our other branches is sitting on the stock we need? So I created another gridview called locations and linked it so when you double click on any item you need to order you can quickly see if there's stock at another warehouse.

The workspace you just described will work great as a inquiry tool. Not all reports need printing.
I have a workspace you describe that has a calculated column for Qty on Order > Qty on hand (also a calculated column) that sorts to show shortfalls in qty then just select item to see the quantity at all locations.

David

Hi Bruce,

I just tried summarize. I never knew it existed before. Only problem is that for whatever reason the location columns all become blank and the only way you can see the quantities again in those columns is to expand the items again.

Hi David,

Exactly, the Gridview linking is great as an inquiry tool but not so good when you want to create a report. At least I'm learning what I can and can't do. That's half the battle. Now I'm moving on to POA maintain specifications.. oh joy!

POA Specifications...good time to back up what works. I've spoiled a few..... I need to spend more time working with the editor.

David

Put totals on the location columns so that GridView knows what to do when you summarize.

It works!!!

The webmaster has extended the edit time to two years; so, even the slowest typers can edit their posts now.

(This sentenced added later.)