Resolving a bill of materials
Hello,
I am currently building a bill of materials. There is a table PARTS and a table BOM (which is an n to m relation of PARTS with PARTS as well as a field QUANTITY. So each PART can basically have any number of parent PARTS and child PARTS. Now, the question I ask myself is how I can calculate the sum of required quantities for production planning.
Example: I have PART1. This part consists of PART1_1 (1 x installed) and PART1_2 (4 x installed). PART1_2 consists of another PART 1_2_1 (5 x installed).
Then I would need for 1 x PART1:
1 x PART1_1
4 x PART1_2
20 x PART1_2_1
Now I want to know for production planning, for example, how many quantities of each PART do I need to produce 40 x PART1. Unfortunately, with my FOR loop this only works for 2 levels. Does anyone have an idea how to solve this?
Thanks and greetings
Fabi
9 replies
-
The data structure you describe is recursive. Recursive data structures are wild animals that are not easy to tame. In CS textbook, the standard solution is to write a program involving recursive procedures, matching the recursive structure of the data. And the result is obtained like by magic. The actual mechanism underlying the magic is that the implementation of recursive procedures invariably involves some kind of stack.
I don't know (yet) if Ninox allows one to build recursive functions. But there is another solution: one can write an iterative program, and take care of the recursive aspects by defining and managing an explicit stack. In Ninox, a stack can be simulated by an array.
You are probably more interested in a practical solution than in a lenghty lecture. So I have build a small database demonstrating the concept, available at the URL below.
In the problem at hand, two different types of information must be pushed on the stack, so I used two arrays operating in parallel. Since the nature of the problem is the traversal of a n-ary tree, I did include two versions of the program, traversing the tree in two different ways, yielding the result in two different orders.
-
Hi Alain,
you should write a book! Not only because you are certainly able to provide solutions, but also because you have great-to read, fluent writing style ;-)! I just imported the archive - and now I need some time to learn and understand.
Thank you very much for your help which is highly appreciated!
Kind regards,
Fabi
-
Hi everybody, the example above works great. However, now I want to do this "bottom-up". I.e., I want to open a PART and see, how many products I can produce with the stock available.
I assume I need to have a look in the bill of material of one specific product. Then find out how often PART A is used in that BOM, and then divide the amount available in stock with the amount required for a product (correct?). But I don't get it how to write a function / script for that. Can anybody help?
Kind regards,
Fabi -
Hello everyone.
I allow myself to contact you, because I also have to develop for my database a management of the nomenclatures with a management of manufacture.
And honestly, as I'm new to NINOX, I'm having a lot of trouble.
Could you help me?
Didier
-
There are a few posts cropping up re BOMs. I still use FileMaker for this and, with no levels or recursion, it works like a dream. Let me see if I can re-create it in Ninox sometime soon
Ninox does not allow for recursive functions to my knowledge.
Content aside
-
1
Likes
- 1 mth agoLast active
- 9Replies
- 672Views
-
5
Following