Yes, but I can't tell you how to do it without knowing a great deal more about your database, the tables, relationships etc.
Thank you for your reply. I have a database - finished products - in which I enter the product code. This product code is self-explantory, so when I type in "ADB C14 Z" as product code, by means of calculation fields, the record shows that you need 2 pcs of A, 3 pcs of product D and 1 of product B, C with a height of 14 and C stands for "plastic version" in order to assemble product ADB.
Now the raw materials, A, D B, C and Z might already exist or not. Therefore I would like by means of a script, first check if they exist already in another database, the "raw materials/parts-file". If they exist already, they do not have to be created. However if part B should not exist, it should be created in the raw materials/parts-file.
Between the finished products file and the raw materials/parts file I do not have a relationship.
In a way it is entering a product code, the system then knows which parts you need for creating this inserted code.
The system should however create (or check if they exist already) these sub-parts.
I hope it is more clear now,
Pleae let me know if not,
I've worked with BOMs before. This is just a general discussion of the issues. The details can get a bit complex, depending on your manufacturing needs.
Your product code would seem to me to have a potential for some real problems. What about material subsitutions? If you determine that Item X will be a better/cheaper component than item Z for your product, you'll have to change your product code. If you then want to change back in the future, you'll have to change it again. That can cause major complications in many manufacturing situations where such substutions often take place--sometimes on a daily basis. In the Job where I had to deal with this, we sometimes had two or three functionally identical raw material components supplied by different suppliers or which had slightly different dimensions that all worked as long as the operator set the machine for the given raw material dimensions.
I'd first suggest you set up a BOM table where for a given Product ID, you can lists the components and quantities as a group of records with the same product ID. This can make life a lot simpler for you. (There are several ways to manage the BOM records to enable substitutions and other changes.)
Now managing the BOM requires pulling up a found set of records in the BOM table and stepping through them one at a time. Each would have a component SKU (Stock Keeping Unit) that could function as the key for matching against to part/materials inventory to generate a list of components that will need to be first manufactured in order to complete the assembly/manufacture of your specified product.
Thank you very much for your reply, reading about BOM's makes me scary. About the code, it really was more an example.
The different components are mostly the same, only the dimensions change.
Guess, I'll have to think this really trough, how to find a solution, thank you for your help.