I’m looking for an approach to start, not necessarily a full solution. My question is how do I construct a program that takes a part number and goes up each level in a Bill of Materials, checks if the Next Higher Assembly is listed in a separate table while still keeping in mind the first part number that the user inputs? I have included example data and how the program would work in my mind.
I believe I would need to use arrays or some kind of recursive function of some sort. I don’t really know what would be the best approach besides the SQL to find the Next Higher Assembly.
I have a table with part numbers and their Next Higher Assembly like this that gives the Bill of Materials for various part numbers called tbl_PartandNHA:
Part Number NHA
Part A Part L
Part A Part M
Part L Part S
Part M Part S
Part M Part R
Part S Part Y
Part S Part Z
Part R Part Y
Part B Part N
Part N Part Q
Part Q Part W
I also have another table with a list of part numbers that meet some criteria. I’ll call this tbl_PartMeetsCriteria.
Part Meets Criteria
Part Z
Part Q
For Part W, the bill of materials look like this:
Part W
Part Q
Part N
Part B
The program is given a part number and climbs up the bill of materials looking for a part number that is listed in tbl_PartMeetsCriteria. The algorithm would work like this: Find NHA of Part B. The NHA is Part N. Check Part N in tbl_PartMeetCriteria. It is not in the table so find NHA. It is Part Q. Check Part Q in tbl_PartMeetsCriteria. It is in the table. Stop routine and show a msgbox “Part Q is in the table. Part B builds Part Q” In reality, my program will store the match elsewhere.
Now we have Part A to analyze. The Bill of Materials with Part A is written here.
Part Y
Part S
Part L
Part A
Part M
Part A
Part R
Part M
Part A
Part Z
Part S
Part L
Part A
Part M
Part A
The program will see that Part A has two NHAs (Part L, Part M). The program will check each of these in tbl_PartMeetsCriteria. Then the program must branch out. It has to look for the NHA of Part M and then the NHA of Part R. It will turn up with no matches. Then it has to go back and check the NHA of Part L then Part S to look for matches within tbl_PartMeetsCriteria. The NHAs of Part S is Part Y and Part Z. The program will find a match with Part Z and say “Part Z is in the table. Part A builds Part Z”
My question is how do I construct a program that can go up each option of the bill of materials to find a match?
I see it as a series of nested arrays like this:
Part A (Part L (Part S (Part Y, Part Z)), Part M (Part R (Part Y), Part S (Part Y, Part Z)))
I understand how to query something using SQL. I understand the basics of arrays and FOR loops, but I don't know if that is the right approach. The problem is that the example data is a simplified version of the Bill of Materials. There are thousands of connections and a lot more levels depending on the lower part number. The program would need a dynamic amount of arrays within arrays to store the options and investigate each possible thread of next higher assemblies until it finds a match or gets to the highest level. How could I do this? Is there a better option than arrays?
The real concern is the way you are allowed to edit your source data. Circle reference would cause an infinite recursion and that's why any recursion setup never allow direct input. Any input has to be validated.
Anyhow here is some recursion samples using VBA followed by the answer of your question where you can read and expand to suit your needs.
Below code will recursively search for the next element until no next element is found. F(x) = F(f(x))
This code is same as above but checks whether the value is found in your meetCriteria table.
In your example table, you have multiple nodes starting with same element. like A, L..; A, M.. each node has to be searched separately. For that you need a list of all nodes to search. Use standard sql and couple it with recursive method.
In theory, you will list all nodes
this is same for each sub nodes for M and S. Hence you need this listing function to be included in your recursive function. So it becomes
to find all nodes starting with user input:
Usage: try this in your immediate window
You should be aware this is just a proof of concept. You can use this to understand VBA's recursion capabilities but you must take additional security measurements before implementing this. You may shorten few lines of codes using sub functions. I leave the bigger version for you to understand. RecursionDepth is for you to implement.