I am working on a purchasing Gridview and I need to use only the "WA" quantities. Our qty on hand includes all of our warehouse and I want to include only our Trenton warehouse. I have 2 .gv's to run this:

The first one is "I/C Items (1,3) Master" = Items to order.gv

The second is "I/C Item Locations Master" = Trenton qtys.gv

Using the Trenton qtys.gv I am calculating the amount qty of WA: IF({Loc} =" WA",{Qty on Hand Plus}-{Qty Shipped not Cost},0) this works fine.

I want to bring that calculated column into my first Gridview to calculate how many we need to order. How can I do this? I used: GETLINKEDVALUE("Trenton Qtys", "Item #", "Trenton", 0). This shows nothing in the column. How can I see only the WA (location) qtys in my "I/C Items (1,3) Master" View?

Hi Lance,

In the GetLinkedValue formula, is "Trenton" the name of the calculated column that displays the WA quantity?

I presume that both of these views are in a Workspace. If so, try accessing the File / Set Workspace Recalc Order area an ensure that the Trenton Qtys view is set to calculate first, so that the contents of that inquiry are determined before the Items to Order inquiry.

Ensure that your Trenton Qtys inquiry has a primary sort by Item #, so that the lookup works. Also, if there are multiple rows in the Trenton Qtys inquiry for each item (ie one for each location), that may confuse the lookup. You might have to consolidate the Trenton Qtys so that there is only one row per item number.

Try without the trailing ,0 as the 4th parameter. The required usage of this is to have a positive number (for the next item) or negative number (for the previous item) - not 0 - in case the exact item # is not found. This is not the desired results for your inquiry.

The syntax of your formula is otherwise correct, assuming that the "Item #" field appears in both views and have the same column title.

How would I do this? There are multiple rows for each item. What do I do to consolidate that?

I suspect it might be better to do a Filter in your 'Trenton Qtys' view to select only the rows associated with the 'WA' location, rather than a Calculated Column that outputs the quantity if the row represents the 'WA' location. That way, there is only one row per item number in the lookup view, and the 'Items to order' view only has one record per item to read to get the appropriate quantity.

How would I filter from multiple locations to show just WA?

Thanks

Anyone?

Create a Filter in the quantity by location table to select only the "WA" locations. Typically:

which selects all the "MD" location records in the sample data. You'd replace "MD" with "WA".

Here is the issue I am having.

In Trenton Qtys.gv I have a column that only has the WA qtys. I can't seem to add a second filter because its my second Gridview in the work space. When I pull the Trenton column from Trenton Qtys.gv to Items to Order.gv I believe it pulls the first qty from the Trenton column in the Trenton Qty's.gv file. In the screen shot below you can see AM5992 (in red) only has 2 locations WA and ZSS it pulls the 12 over from WA. AM6002 (in green) has 6 locations KI, LI, SW, WA, ZSS, ZZZ. I believe it is pulling the 0 from KI (circled in red) because its the first amount for that item.



How can I get this to show me to correct Qtys?

Add the Filter in my post to "Trenton Qtys.gv" and change "MD" to "WA" in the Filter.

Hi Andrew,

"Trenton Qtys.gv" has that filter applied.

Thanks