Relationship headaches (merge keys,link tables,..)

[thierry.be]

Hello,

I am trying to implement a database, but I am having some ER troubles.

Does anyone know how to implement the following?

You have a job (project).

This project has a number of items.

Every item is made by a different vendor.

So I did the following :

3 tables Job, Item , vendor

Relation job-item 1-n

item-vendor n-1

So far so good BUT now how can i invoice this all?

Because 1 job can have many invoices

1 invoice has many items on it.

In the table job, i have a portal with all the items.

The idea was that i could flag the items and then know that i could invoice them.

I can formulate the question in a more general way. If you have a portal in a parent (job). Let's say you have ten items in it. Can you select a part of them and pass it on to a child (invoice).

The item should then know it has been invoiced.

I really hope someone can help me,

Thanks anyway,

Greetings

Thierry

[ March 07, 2002, 02:28 AM: Message edited by: thierry.be ]

[danjacoby]

Complex relationships can be troublesome (and do I have relationship troubles! -- but that's another story).

It seems that you want to show, in a portal in the "job" file, those items that have not been invoiced.

But first, if each item only has one vendor, you need the following three tables: Jobs (where you'll be doing all the work), Items (a list of items, along with their respective vendors), and a LineItems file where all the related data will live.

OK.

In LineItems, you have two fields, "Item" and "Vendor" (and maybe others, but you can figure it out from this basic). "Vendor" is a calc field, pulling up the vendor from a relationship between this file and the Items file, based on the "Item" field.

You have a relationship between a keyfield in Jobs (call it "JobName" for now) and an identical field in LineItems. In the portal for this relationship, you have fields LineItems::Item and LineItems::Vendor (the latter will, of course, enter automatically because it's a calc), and maybe LineItems::Invoiced (see below).

Create a field in LineItems called "Invoiced", and a script so that you can put a "X" in that field when it has been invoiced. Create another calc field ("JobItemInvoiced") that equals "JobName" & a space & "Invoiced".

Now create a relationship in Jobs between "JobName" and the "JobItemInvoiced" field in LineItems. Create a separate portal for this relationship -- it will show only those items that have not been invoiced.

HTH,

Dan

[thierry.be]

Dan,

Thank you for responding,so soon, yet however well formulated your answer was, it rises even more questions for me (probably due to the fact that i am just a novice)

But I think i have to rephrase my question.

The table jobs is actually in relation with the table client. n-1

What I actually want to do is

1. To be able to sent the client a partial or full invoice.

Let me explain : I have now a portal in job with all the items for that job in it, and also the price.

I want to select certain items in that list (or in another way) and add them to an invoice either a new one or an existing one. Later when I open that job again , i must be able to see what items are invoiced and which items aren't and then select those that aren't for invoicing.

My question is where do I hang the table "invoice"?

Do I make a relation with jobs, client or both?

What is the best way to do this in order a user (me) could easily choose an item.

I have been cracking my head over and over, I just don't have a clue of how to implement this.

So again any help is welcome.

Thanks again

Greetings,

thierry

[ March 05, 2002, 09:29 AM: Message edited by: thierry.be ]

[Rigsby]

Your explanations are a little difficult to follow, not because of you I suspect, but because your problem is complex. However, I think I know what you want, and how it can be done. But – like your explanation – the answer is also complicated. I can’t give you a run down of how to solve your problem, but I think I can put you on the right track:

Start at the beginning:

What is your material – your main DB? In this case I would say your clients:

So, a Client DB each with a relevant ID is needed.

Now you need a ‘Jobs’ DB where each Job a client gives you is stored. These Jobs use the client ID to link back to your Client DB – so each client can have any number of jobs.

OK! Each job has several items to it, so now you need an ‘Items’ DB where you can create items for a job. Here you need to use a ‘Client ID + Job Number’ key to link back to your Job DB. So, if you are following this, you now have a list of jobs, which can be traced to any client, and in each job, a list of items from the Items DB’ which can be traced to any job or client.

OK! Now we reach the invoice stage. You have quite a number of options here, but I would always recommend using another DB, which is only for invoice purposes.

Now, imagine that in your Items DB you have a key field which is a join of Client ID + Job number, and you also have an ‘InvoiceYN’ field, which is activated to say if an item should go into an invoice or not. In your Invoice DB create a new invoice, (auto enter the invoice number) and then enter a Client ID and Job number. You can now use this to show all the items from a job in a portal, and the client ID for address information etc. Mark the Field ‘InvoiceYN’ in the portal, and hey presto, the item appears in your invoice. Link this to a price list, and you’ve got a coooooool solution. Also, the items which are not marked in the field ‘InvoiceYN’ can be found and sorted at any time in the items DB

Remember, that you can split Key fields where needed to create new keys – so, for example, if you have the Client ID + Job number key “1234-26” (i.e. Client 1234 and Job 26) you can split these at any time to just get the job or client number.

I hope this helps, as trying to solve problems without really knowing what they are is not easy.

Rigsby

[thierry.be]

Rigsby,

Thanks for your help, it was extremely usefull to me, I think I will get there, yet I have some questions:

1. In the item table a seperate id is necessary right? The combination of the foreign keys (job and client) aren't unique for item?

2. If you mark invoice Y how can print only those on an invoice in other words, how can I select, view and print this on an invoice?

3. To what tables should the invoice DB be linked. Should it only be linked with items or would it be better to link it with jobs and clients?

4. How do you merge and split keys? Is this a calculation field ?

Hope you (or someone else) can clarify this

Thanks again greetings,

Thierry

[ March 06, 2002, 07:44 AM: Message edited by: thierry.be ]

[Rigsby]

Thanx for the private message! Did you get my reply?