building a order list

[randy remark]

Firstly let me explain my database

I have a quotation database (QD), a job database(JD), a purchase order database(PO), a materials database(MD) and a products database.(PD)

So basically a quotation is prepared by selecting products and/or materials in a portal from the PD and the MD. The important thing to know here is that Products are given a title and are made up of materials

Example a Gizmo 2000 as a product is made up of 20 units Thingys and 18.5 Metres2 of Whatzits.

These are given a cost price per products

When a Quotation is accepted, it becomes a job in the JD.

I have managed to successfully promote a quote to a job, but I now need to print out and order list of materials and if possible automatically raise Purchase Orders per each supplier, This is where I am totally lost, simply beacause a product may have 34 parts from 12 suppliers, I need to raise a PO for each supplier seperately.

Also many materials are used more than once, let me try and explain.

A GIzmo 2000 contains Whatzits but so does a Gizmo 3000, and job may have 20 Gizmo 2000's and 10 Gizmo 3000's, so the supplier order would need to show 360 whatzit not 185 whatzits and then another 175 whatzits later on in the list, I hope this makes sense.

It would be great if anyone could help point me in the right direction as I'm getting grief to sort this out.

many thanks

[BobWeaver]

Buy the book 'Advanced Filemaker Pro 5.5 Techniques for Developers' by Moyer and Bowers. It has an example that covers this type of inventory/order system, and it's just a good book to have anyway.

[randy remark]

I have ordered the book but I would have thought someone could have helped me a little in this forum, if we all bought books we wouldn't need the forum.

[BobWeaver]

To paraphrase the solution given in the book: Create a parts database that has a multi-level relationship to itself so that assemblies relate to sub-assemblies in the same file, and sub-assemblies relate to sub-sub-assemblies, and so on down to the individual parts. Then use scripts to traverse the multi-level relationship to generate bill of materials for construction and parts re-ordering. That's the basic idea, but I think you can see that there are a lot of details that need to be filled in. I wasn't trying to avoid your question, but the book can provide an answer in a lot more detail.

if we all bought books we wouldn't need the forum.

Not true. There's a lot of information in this forum that you won't find in any book.