Work order to parts tree

unknown

I'm new to visual and have been tasked to write a simple app to show all work orders and their parts in a tree view.

I noticed that the Work orders are mixed in with both the Work_Order table and Requirement table. ??? I was wondering if anyone has ever had a requirement to produce such a nested tree and if so, how did you write the query?

Is there an API or BODs that will do this? I'm using MS SQL 2005 and the latest Visual.

End result:
Parent Work_Order.Base_ID
- Part
- Part
Sub Work order Requirement.WorkOrder_BaseId
-Part
-Part
Another Sub From WorkOrder_BaseId
-Part
-Part

I hope this isn't confusing...
Thanks

larry.stewart@novaecorp.com

unknown

Hi Larry,
Generally a tree is not easily pulled up via a single SQL query because part of your criteria is coming from the data you are getting. I have seen a discussion of the approach in Joe Cielko's SQL for Smarties column, but cannot remember much about it. He did not find it a practical way to access the data, as I recall.

Generally you'd be pulling the records you could access with the root part ID, then you'd be using the sub ids to get the next level(s), etc. You could use recursion to go at this, but a simple stack is just as useful.

I've not done a tree from this particular set of data, but have done similar things with more complex and varied data. Shouldn't be a big deal.

Don't know what you're doing here with the data, so I'll check that you don't need the Operations table in here as well?

Happy to answer any further questions --

Dorothy