How to link more than one order to a job

[Steve-vfp9user]

Hello

This is a follow up to a previous question I asked shown at thread184-1732500

That issue has been resolved but I am trying to establish how I achieve the following:

Each time I create an order, the ADULT table called SUPPLIER a unique number allocated (for example 58) and when items are added to that order in the CHILD table SUPITEMS the unique number (example 58) is added to those records for each subsequent item added to the order. The tables are linked on my form in the data environment via an index field called PURNLINK. That's ok and works fine.

If I now link a customer record I can also add the unique number allocated from the order (58) which links all three table records together.

My issue/question here is, if I create another order (as there may be one than one) the new order will be given a new consecutive unique number (example 59) which won't match up with the customer record.

I know I am going to need a unique somewhere to link up the order, items linked, customer record.

Is there an example in the help file and if so, what do I look for or what would you suggest I do?

Thank you

Steve

 

[Olaf Doschke]

Of course a customer is linked to n orders, so you need

customers
id, name,...

orders
id, customerid, date,...

orderitems
id, orderid, productid,...

products
id, name, supplierid

suppliers
id, name,...

Does that help? If not, you need to explain your data model in more detail, it seems to differ from that. You normally have customers placing orders with orderitems each about a product (or service), which you get from suppliers, so the suppliers are not at all chained into the order or orderitems, but in products.

You can always link tables having a many-to-many relationship via an additional table, called an asociate or linking table, eg if you can get the same product from different suppliers, then you have a productsupplier table with a productid and supplierid, the supplierid is not part of the product table anymore, then. You have to do two joins then, or relations, but you can do that via RELATION.

The tastrade and northwind sample databases coming with VFP both have a usable table design for trading, customers, orders, suppliers. Take a look inside.

Bye, Olaf.

 

[Steve-vfp9user]

Hey Olaf

Appreciate the post and will take a look at your suggestions.

To expand on this slightly, the scenario can be that I create an order for a client first, then create a purchase order for the supplier. In some cases we already have an existing order on the go from a supplier and from that order we would give our client some of the materials. hence the reason I am trying to link everything if the need arises.

When I come up with my findings, I'll post back.

Thank you

Steve

 

[Olaf Doschke]

What you're talking about is about inventory/stock and matching current orders and purchases. You may put purchases in a separate tables or in orders, too, being the client yourself (for your client). You could then reserve some of your purchases and assign it to fullfill a certain order. That would be about a state, count and assignment of products, as you obviously can't sell the same item twice. If you have all that in your database design, it might help to show your full database design to help you make a corresponding GUI. If you're in the state of rethinking or building up your design, you can't feed us crouton here, if you want a good database design, that's a decent job for a week or two to speak through everything involved.

Then of course there are many ERP solutions available.

Bye, Olaf.

 

[JRB-Bldr]

What you are trying to build could be either an ERP (Enterprise Resource Planning system) or a MRP (Manufacturing Requirements/Resource Planning system).
See MRP Versus ERP at:

http://www.sheetmetalworld.com/shee...anagement-and-production/11086-mrp-versus-erp

Those can be either complex or simple based on your individual needs.

You might want to look through the various database architecture diagrams at:

https://www.google.com/search?q=erp...WHMyPqAaV6YHIAg&ved=0CCoQsAQ&biw=1106&bih=903

or

https://www.google.com/search?q=mrp...HKM2CqgbFv4GABQ&ved=0CEAQsAQ&biw=1106&bih=903

And see if one or more of those diagrams can guide you in the direction you desire.

As you design your database architecture, remember to maintain Data Normalization.

Once you have your database architecture designed, then you can begin to create your User GUI Forms and put the necessary code around the Form's objects to utilize the data from the various sources.

I will add that after having personally designed and developed a good number of proprietary ERP/MRP systems for clients who were CERTAIN that they ABSOLUTELY needed THEIR OWN METHOD OF SYSTEM OPERATION, in retrospect it might have been much more cost effective to have found an off-the-shelf system and modify THEIR processes to fit.

Good Luck
JRB-Bldr

 

[Steve-vfp9user]

Olaf and JRB-Bldr

Appreciate your additional posts. I will look into this over the next few days and post back

Thank you

Steve

 

[Mike Lewis]

Steve,

You might like to know that the sample database that comes with VFP contains tables that exactly match what you are trying to do. This is the Tastrade database in the Samples folder. In particular, it has customers; these are linked to orders on customer code; it has orders, which are linked to order items on order number; and it has order items that are linked to products, which in turn are linked to suppliers.

I'm not suggesting that you should use this actual database, but it does provide an excellent example of how this sort of linking should work.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Hey Mike

Appreciate your post and will look at your suggestion.

Thank you

Steve