Producers/suppliers - need advice Pleaaaase

[Ugo DI LUCA]

I have a Wholesale/Retail activity.

When I started my activity, I had a 15,000 products catalog, directly bought from Italian Producers.

I created a Product DB that has fields Producer Name, Producer Code, Collection and Reference, Public Sell Price,...and a Producer DB linked to Product DB, Purchase Order DB, Main DB for all operations.

So far so good...

BUT as my business grew up, I had to expand my offers with products that I couldn't buy directly from the Producer (some products have a Wholesaling exclusivity), using other Wholesalers catalog products. They now also sometimes buy from me.

So I became a client of my current competitors and my competitors became clients !

As I didn't have time to rebuild the DB, I put the wholesalers in my Producer DB. As the "Producer Name" in Product DB was linked to the "Producer Code", you can imagine that I went into troubles when I printed an invoice with the name of the wholesaler (my Competitor) in the line items, instead of the "Producer name".

I changed method, creating a "Producer N

[BobWeaver]

This problem often occurs as businesses evolve. One solution is to have a single file for 'contacts' which may be customers, manufacturers, wholesalers, service providers/consumers, tax collectors, etc. Then use a single contact ID for any reference to this file. That is what I did with my business.

[Ugo DI LUCA]

I made a first reply t your post yesterday night. It was 3:00 am and needed some sleep. Hope you didn't see it.

Let's write it again.

Do you suggest :

1. That I create a Main Contact Database with a single contact ID, so I could avoid having (for my purpose) a same Contact with 2/3 different codes (Could be Client N

[BobWeaver]

Question 1: Yes, I would have only a single contact database. I can't say for certain that a single contacts file is the best solution in your case because I don't have all the details of your business operations, but I think that a single file is likely easier to maintain.

Question 2: Yes, you will need to give all contacts unique keys. When you merge your original files together, it's likely that you will get duplicates of your existing key fields which will cause problems. So, you should create a unique key field that is unique across all your existing files. Then you will need to create a new key field in your sales database and set that field to the new unique key. You will likely need a script to do this. Finally, change your relationships so that everything now uses the new unique key field.

If you do go ahead with this, make sure you back up everything before you start. You will have to maintain two sets of key fields initially, until you get everything set up to work with a single contact key. Once you have all your relationships using the new unique key, you can get rid of your original Manufacturer No., Wholesaler No. and Customer No.

You will likely want to create a field in your contacts file called "Contact Type" to identify the contact as a Manufacturer, Wholesaler, Customer etc.

[Ugo DI LUCA]

Thanks for your quick answer as I stopped my work waiting for a solution.

To make it clearer, you think I should (at the end) merge my actual Contacts Databases (4 DB) to one.

Apart from adress and telephone infos, I should then create different layouts for information compilation, according to the "type of Contact" in that Database.

In fact, I simply replace DB with Layouts.

This one still doesn't come to my mind, as I am new for this.

What about if my contact make part of different "types" (in my case one of my wholesaler could also be clients) ? No Problem ? Using a multiple check box ?

Here are some more info on my current system :

Main Databases

CENTRAL DATABASE : All Databases relate to that Database in a way or another.

CLIENTS

PRODUCERS

TAX COLLECTOR

OTHER SERVICES

PRODUCTS (with Stocks in it - really urgent changes needed)

PROPOSALS

CUSTOMER ORDER

PURCHASE ORDER

TRANSPORT PREPARATION

TRANSPORT

CUSTOMER DELIVERY

INVOICES

[BobWeaver]

Just to make sure we are talking about the same thing, the Contacts file is simply a file containing the contact's name, address, phone number, contact type, perhaps some kind of description field, and other very basic information about the contact. It should not contain any information about the products they sell or anything like that. Since the basic name, address and phone number data is common to any company or individual, I don't think you will need to have very many different layouts, just an entry layout where you enter the name, address, etc.

Any other information about products, sales, etc, should go into those files.

[Ugo DI LUCA]

"Anyoyher information sould go into those files"

Which ones are you talking about?

Really need some sleep now (3:30 am - hope my employees will open the store tomorrow and leave me in peace for a while).

Please take your time for your answer, as if you were talking to a kig, cause I think we misunderstood.

I actually have a Client DB that contains only a Single Code, telephone, adress, but related to my Central DB, which is my Main File. Using portals, scripts, lists and other, this Client DB became quite my Main operative DB, as I can see anything in it, from orders to invoice. I even create the Purchase Order, via cross relations into Central DB and my Purchase Order DB.

I also have a Producer DB that contains a Code, adress, telephone,...

What I cannot understand is if I can throw away these files, using a new Main Contact DB or if I keep using them, related to the Main Contact DB for "merging" adress, telephones and to the other files of my Database system.

If I throw them away, then I could have the same layout for Contacts and Telephone, but I couldn't have the same layout to compill a Purchase Order or a Customer Order...

"It should not contain any information about the product they sell" ?

Where do I have to put these info - In the Manufacturer DB and Wholesaler DB ?

Hope I make me understand. If No, I will insist tomorrow because I'm quite lost

[Ugo DI LUCA]

Attention !! in my precedent post, I said "Take your time to answer as if you were talking to a kig".

Kig = Kid not King

I took "some distance" from my work and finally understood something.

Why did I made a Database structure with a Tab Template (as Aqua Exemple) if I only use only my Client db for everything.

Will reconsider this, but still need an answer from my precedent post (Get rid of Client DB and other Contacts DB or keep them with relation to a new Main Contact db?)

Thanks

[Ugo DI LUCA]

Maybe this can help. Here is pdf files of my Client DB (project)

[BobWeaver]

I think this is getting beyond the scope of a user forum. These design decisions are not something that I can give you based on the information that you can provide here. I suggest you get a good medium to advanced level Filemaker book that can give you advice on the proper file structure for this kind of solution, or get a database consultant who can meet with you in person to help set this up. As much as I want to help, there is a limit to what we can accomplish in this kind of forum.

[Ugo DI LUCA]

I'm Ok with that answer. While waiting for the documentation, I will continue with this DB structure. Thanks anyway doc.