a lookup pick-list - I'm baffled

[toddj]

I'm trying to create the following scenario...

I have a table of inventory parts that lists parts numbers, description, qty on hand, vendor, etc.

I want to create a "shopping list" which is a lookup (copy, not a direct relationship), of parts that are running low.

I'd like it to look like two portals side by side, the left one listing the parts table, the right hand list showing those parts I've selected to add to my shopping list. There'd be a button between the two portals, to [Add] a part to the list.

It would work exactly the way we add a script step to our scripts.

I can't wrap my head around how this can be accomplished though. And I'm sure it's gott be a piece of cake.

..Todd

[Søren Dyhr]

Before getting deeper into a solution, is it important to know how the qty on hand is esablished, either via recursive data structures and unstored calcfields or the transaction model?

You might not know these expressions, but then try to explain how you do it in your solution.

--sd

[toddj]

Nope, the QTY-on-hand is not really relevant to this task. The shopping list 'picker' will be operated manually/visually, not necessarily based on current QTY.

There will be optional filters applied to the master parts list to limit the parts choices to, for example, those with qty=low, color=red, vendor=acme, or whatever, but that's not a problem.

[Søren Dyhr]

not necessarily based on current QTY.

I just thought you needed some sort of canban ranking or selecting the items in dire need for reordering. But yes if the list isn't too large can you probably eyeball them.

I would never suggest copying or lookups, because it gives redundant data storage .....unless I really have to!

I've therefore made you a template that uses a pair of selfjoins to accomplish something in the vicinity of what you're trying to.

It uses a transaction'ish approach by sorting on the QTY so the lowest gets to the top of the list, arrows have been added to the assignment button indicate if an item already is on the shoppinglist....

--sd

itemsReordering.zip

[toddj]

Thanks Søren,

I'll look at the template this evening. Much appreciated!

In my case, I neeed to use lookups/copying to create a historical snapshot of the current state of affairs, and store that state. Much the same as you would in an invoicing system.

I realize that's not the usual way to handle a re-ordering task, but the workflow is in charge here, not conventions.

What is "canban ranking"? I've never heard that term before.

..Todd

[toddj]

Søren,

That solution is elegant, simple, and wonderfully tricky. You are a true FM Pro.

No wonder I couldn't figure it out. I wasn't even close.

Thanks again.

..Todd

[Søren Dyhr]

What is "canban ranking"? I've never heard that term before.

http://en.wikipedia.org/wiki/Kanban

--sd