Display records from same table twice?

[marconey]

Hi everyone,

We have a service business where we work with warranty companies to service their customers. I am designing an invoice where I have a "Bill To" address and also a "Customer" address field with names etc. ofcourse. The thing is the Bill To company is also a customer in a sense. So what I did was just combined both entities into one and call them both "Customer" (I have a single table Customer. How do I pull (and create) into the Invoice layout, records from the same Customer table but into two different areas of the Invoice (Customer and Bill To)? I have thought of the concept of table occurrence but don't know how to implement it. Is creating another occurrence of the Customer table on the Rel. graph, the way to go?

Any help would be tremendously appreciated!

Kenneth

[aldipalo]

I had a similar set up where I have contacts who also are references for other contacts. I set up a ContactID and a ReferenceID since my reference check form needed to have both parties and their contact info on the form. All I can say is it was a nightmare and I'm constantly tweaking it. In my new app I will not approach the problem this way.

Clearly, you have a client and a service customer and IMHO should have separate tables for each. It is cleaner, neater and as your database grows it will give you a lot less headaches. If you are in the building stage now is the time to make it right.

[marconey]

I had a similar set up where I have contacts who also are references for other contacts. I set up a ContactID and a ReferenceID since my reference check form needed to have both parties and their contact info on the form. All I can say is it was a nightmare and I'm constantly tweaking it. In my new app I will not approach the problem this way.

Clearly, you have a client and a service customer and IMHO should have separate tables for each. It is cleaner, neater and as your database grows it will give you a lot less headaches. If you are in the building stage now is the time to make it right.

Separating the two types of customers may an option but what happens when down the line when one of the customer's(warranty company's) customer becomes a direct customer of ours - in other words they become the Customer AND the Bill To customer (i.e. both spaces on the invoice would be filled with the same names and addresses). One would then end up with redundant data in two tables (same record appearing in two tables) - isn't this a no no in DB design?

I do like your signature quote - I guess I need to adapt it too :

[aldipalo]

Wouldn't that be similar to a "Bill To" and "Ship To" address? If I understand you sometimes a Client can be the service customer. The Client always gets billed because all the work you do is warranty work, is that correct? Unless you have many 100's of Clients I don't see where 10 or 20 client/service customers would be a problem. From my experience, as quoted in my previous post, the other way was quite cumbersome and continues to be problematic. So, from that point of view I would not do it again.

Perhaps someone with greater experience than ours can offer some of their infinite wisdom.

[marconey]

Well yes, I see what you'r saying. Currently we are only having customer's customers. (!) But eventually when we start having customers (directly without going through a warranty company). Then we are going to have hundreds of such direct customers. Wouldn't that cause problems?

Anyway I'll try to fiddle with you ideas a bit!

Thanks for the suggestions.

Kenneth

[aldipalo]

I see what you are saying. Eventually you expect to have direct bill to the service customer not the manufacturer. Couldn't you script that, perhaps with a flag field Direct Bill (Yes) / (No)

If(Direct Bill)

Set Field(ShiptoName;Customername)

blah, blah

else

Blah, blah

Also, take a look at BPS "Sales Orders" and see how they are handling it. I don't know if it's the best way, but it works. I think it may get clutsy if you have a very large database.

HTH

[marconey]

Thanks for a great suggestion! I'll certainly explore that. I have looked into the FM BPS sales order. Funny, some of the stuff they have done there goes against everything (that means whatever little I remember!) I learned in DB design theory classes! They have basically put pretty much everything into one table (sales orders)...

Anyway I guess it's just a trial and error learning method!

Thanks,

Kenneth

[marconey]

I know it's a simple issue, but hey! I am a beginner! Please see the attached PDF. I want to design the DB in such a way as to. I am designing an invoice I have created Customers, Invoice, LineItem, Appliance tables.

1. Avoid duplicate data in two tables (if there were two tables) - this is pretty basic DB theory anyway.

2. Customer (on invoice) is currently not the Bill To person, but someday that might change and the Customer may become the Bill To person as well.

3. Is it possible to have a single table of people or customers or whatever (since Bill To is also our customer (we simply service our customer's customers) and pull records from that same single table to fill in both (Customer AND Bill To) spaces on the Invoice? (and yes, I have an Invoice table whose layout I am designing the invoice on).

4. I could create two tables of customers and billTo entities but then when one of them would become the other, I would end up with duplicate entries in both tables... not desirable.

Any ideas?!

Thanks

Kenneth

Invoices.pdf

Edited October 5, 2007 by Guest

[comment]

This seems to be a duplicate thread:

http://fmforums.com/forum/showtopic.php?tid/190470/