Recursive lookups? Help!

[FSRGuy27]

I have an inventory system in a heirarchical tree structure. When an assembly is entered into inventory, it automatically subtracts all of the discrete parts that make up that assembly, times the number of each part per assembly. This is very nice for a snapshot of where all of the inventory is currently in the assembly process.

However, I've been asked to provide ordering information for the inventory specialist. NOW I need to be able to recursively search through everything and multiply the parts per each assembly times the number of assemblies on hand and get a total inventory of each part, regardless of which level, or assembly, it's tied up in. I've studied a little bit about recursion in FM, but all I've seen so far are vague references to lookups and posts that vent frustration at FM's apparent inability to handle linked lists well. Is there a way I can get at the information I need? I just need the total inventory counts to be generated in a report form for our inventory specialist.

Thanks for any suggestions!

[LiveOak]

Probably saw one of my posts on linked lists.

I attempted to write a multi-level marking system for a customer (after I tried to talk them out of it and they begged me to try) without much success with a relational structure. The calculation wouldn't automatically drive up through the list. The only approach I think will work is to incorporate a notion of "level" and successively find and relookup starting at the n-1 level, followed by the n-2 level, until you reach the top level. I'm sure not the answer you wished.

-bd

[Rigsby]

This is in old problem, and as LiveOak says, the best way is to stay away, or try to talk clients out of it. I have a similar problem that I

[Geeksharka]

Instead of using searches, recursive or not, I suggest using relationships. Set up properly, these will show users 'on-the-fly' data instantaneously, and easily.

The key is establish the exact parameters of what you wish to show. Usually this is a combination of several date, status (indexable) fields. Create a calculated key (in the Child table) that checks for your indexable parameters and returns (per record) a Y or N indicating whether that record currently matches the parameters. In your case everything that is currently in Production.

Then, from a Home/Central table, set up a relationship to that calculated key field with a global field that equals "Y". That portal will always show any of the line items that match the parameters as set by you in the Child table, meaning that even as users may 'push' the line items through their productiuon process, you will always 'see' the ones pertinent to that portal. Then, as to adding up the pieces etc., it's a simple SUM field in the Parent of the related child records, or fields therein, such as QTY.

I use this all the time in Mfg/Tracking systems, allowing especially Accounting and Executives to instantaneously see their 'business' as different departments push items through. It's a little development heavy on the relationship side of things, but the users never need to do complex or recursive searches -- just go to pre-set layouts in the Home/Central table to see how and where things are.

HTH

[Rigsby]

Sharka. I think you

[Geeksharka]

Yes, you're probably right. My solution entails no scripts at all, for to do so with the problem above, as I understand it, would be tedious and slow. My solution involves looking at the system's setup from a different angle, and relying on indexable status fields, that can then be evaluated by an indexable calc field and used as effective relationship keys, skipping layers of tables and relationships. In effect 'finding' and relaying the needed data directly to the Central/Home table, console, what have you.