copy info from one table to another

[ianwarren]

Hi all, I'm a newbie to FMPro so I hope someone can help with what is probably a simple problem that has me tearing my hair out.

I'm making a datbase for a small business. The database will be logging each client enquiry that comes into the business. It will also be used to record the existing clients the company has. So far I have made a database where I can enter clients in one layout and then enter client enquiries in another layout (basically the simplest thing in FMPro!)

I would just use a lookup but half the client enquiries come to nothing, while the other half actually develop into proper paying clients of the company that then need to go into the clients table. What I want to do though is instead of having to rewrite all the information from the client enquiry table into the client table, to enable it so that with a button click I can take the client information from the client enquiry table, and add it as a new record in the client table as and when a client making an enquiry becomes an actual client of the company.

I hope I've explained this clearly, but if anyone has any advice it would be greatly appreciated!

Thanks,

Ian

Edited June 16, 2007 by Guest

[LaRetta]

Hi there, Ian, and welcome to FM Forums!!

Whether a Customer or simply an inquiry, these records share something in common ... they all contain a Name, address, city, state, zip, phone and so on. Therefore, they should reside in the SAME table. Tables holding both types of people are sometimes called CLIENTS or CONTACTS, ie, everyone that comes into contact with your company. You would then designate whether they are a PROSPECT or a CUSTOMER by simple radio button attached to a field called Client Type.

By using a structure such as this, there will be no need to transfer records from one table to another. In addition, when you want to search for someone named Joe who lives in Waynesville, you will find him whether he is a customer or not. If you have any questions, please feel free to ask ... there are many, many great people here who are willing to assist.

LaRetta

[ianwarren]

thanks for that... it's amazing how simple something seems with a little lateral thinking!

Just one problem though, once a client is flagged as a CUSTOMER rather than PROSPECT I need said customer to be able to add multiple enquiries on their page. Would this simply be a case of adding a second table and a portal for enquiries but only adding enquires of customers who are flagged as customers?

Edited June 16, 2007 by Guest

[LaRetta]

I need said customer to be able to add multiple enquiries on their page. Would this simply be a case of adding a second table and a portal for enquiries but only adding enquires of customers who are flagged as customers?

You nailed it ...

FIELDS:

If you need additional fields for SOME Clients but not others, you could use another table but it is NOT necessary; in fact, it is usually better to have some blank fields than to hold data in a related table unnecessarily. When you place data in another table you pay a price - it will not update automatically if data in the main table updates. You will hear complaints all the time about related data not updating for people so I suggest you don't split one-to-one unless the number of fields is significant.

RECORDS:

But that is not the issue you face here. You will not only have additional fields but you will have multiple ENQUIRIES per Client and only some Clients. This 1:n (one-to-many) dictates that you MUST use another table to properly track that information.

LaRetta

[LaRetta]

I don't want to overload you here but you seem to be catching on quicly. It is also (usually) better to have addresses in a separate table. In this way, you can have a Mailing Address, Physical Address, Shipping Address etc for each Client. The same is true for phone numbers (or all numbers including web address etc).

Consideration of the best structure beforehand will save you many hours of headache down the road.

LaRetta

[ianwarren]

I don't want to overload you here but you seem to be catching on quicly. It is also (usually) better to have addresses in a separate table. In this way, you can have a Mailing Address, Physical Address, Shipping Address etc for each Client. The same is true for phone numbers (or all numbers including web address etc).

Consideration of the best structure beforehand will save you many hours of headache down the road.

LaRetta

Don't worry about overloading me it helps me learn quicker! Thanks for all of your advice. Like I say, it's amazing what lateral thinking can do... I didn't think of putting things into one table but looking back it's going to save me hours of hassle doing it this way.

For example as an talen agency we have three types of enquiries, a direct booking, a simple enquiry, or a casting. It just occured to me that for three types of enquiry we collect basically the same information, so why not just put all three into one table?

Thanks for all your help with this and hopefully one day soon I'll have learnt enough to help other people on here with their problems :P