Drop List - related items, exclude current

[Madapple]

Working on an inventory bin consolidation solution, I have a portal that shows all of the locations & qty for an item. I also have a drop down that the user marks as yes to move, the qty they want to move, and the bins with the same item in them. 

 

I would like to filter out the current bin from the drop down, the current value list is the location field from the "count" table. 

 

Suggestions? 

[Cable]

Can you set the value list to be a related value and set the relationship to exclude where Bin = current bin?

[Madapple]

That is what I want to do - but don't know how

 

[Cable]

Ok, create a table with one record for each bin in a field called BinName.

Create a relationship from the Inventory table to the bin table. Set the relationship to be the Bin in the Inventory table to relate to the Bin name and make that relationship "does not equal".

Then, create a value list with the option "Use Values from field" and specify the BinName field.

[Madapple]

gotcha - but will this show me only locations that already have the item in it. 

So if Item AB405 is found in locations/qty:

Bin     QTY
102a   1
105b   10
110a   2
205C   2

 

and we want to consolidate everything in bin 110a & 205C into 102a,

Value list on 110a to show 102a, 105b, 205c
Value list on 205C to show 102a, 105b, 110A

 

I don't want to show every location in the warehouse, for a midsize warehouse we have over 8,000 locations

 

 

[Cable]

No, that's not how I understood your question. I'll look at it again tomorrow and try to understand what you're trying to do. It would help if you uploaded a sample of the tables and what you're trying to do.

[Madapple]

I didnt explain it very well at first - thats my fault.

 

I'll upload a sample database when I get a moment to strip out some junk from the solution

 

thanks for your assistance!

[Cable]

Ok, I think I understand.

Like I said, create a relationship from the Inventory table to the bin table. Set the relationship to be the Bin in the Inventory table to relate to the Bin name and make that relationship "does not equal". But also set it so that the relationship does equal the key/foreign key for the item in inventory.

Then, create a value list with the option "Use Values from field" and specify the BinName field.

That will show you only bins where the item exists but excludes the bin it is currently in.