2 files, same portal = complex relationships

[the_moto]

Help,

I have 2 files: 1 called "Jobs" that contains specific job information. Including a portal to a file called "jobItems". No problem here. The other file is called "Invoice" and contains billing information for each job, also with a portal to the same file called "jobItems" where a user will check a box when the itam is invoiced.

So, the 2 seperate files need to display the same portal. At one point, i got it to work, but was not able to create multiple invoices for the same job with the same set of "jobItems" and the same job number.

Here's what I think is involved relationshipwise:

Jobs - jobItems = 1 - many

Jobs - Invoice = 1 - many

Invoices - jobItems = many - many

I hope all this is clear.

Thanks

[StevenB]

Let's see if I can help...

If your portal in the Invoice file shows the Job Items and you have the checkbox INSIDE the portal, then the first time you check the item it will stay checked in the Job Items file the next time you try to create an invoice for the same job number.

This is because if you make a change to the record in the portal - it will change in the file that is being displayed.

Oneway around it is to put the checkbox field in the Invoice file or set it up so that when you select the Job Number, you can use a script to copy the JobItems to the Invoice file.

Hope this helps.

[the_moto]

Well, I want the checkbox's to remain checked between Jobs and Invoices. My goal is to be able to create multiple Invoices for each job. I just can't set it up so that I can make a new invoice with the same job number and not have dulpicate copies of the joItems information.

?

[djgogi]

Actually you need an associative table between Job and Jobitems 'cause its an many to many relation(IOW one job can have many job items, and one job items can be part of many jobs)

This table should contain only fields jobID and jobItemID.

Also your Invoice table should have pk invoiceID anf fk jobID

Now you can solve everything using relations:

JobInvoice::Job(jobID)----->Invoice(jobID)

InvoiceJobItems::Invoice(jobID)----->jobItem(jobID)

HTH

Dj

[the_moto]

Great, Thanks, I knew it would involve a join table.

But now I'm confused. I though the many to many relationship was between Invoice and JobItems.

1 job - many jobItems

1 job - many Invoices

many Items - many Invoices (the same 1 Job)

Is this incorrect?

[djgogi]

I'll try to explain the situation.

First some definitions

MVD-Multivalued dependency

MVD occurs when in a relational table containing at least three columns, one column has multiple rows whose values match a value of a single row of one of the other columns.

Suppose that job can have multiple invoices. Also suppose that job can have multiple job items. If we record this information in a single table, all three attributes must be used as the key since no single attribute can uniquely identify an instance.

Consider a table:

Table A (jobID, invoiceID, itemID)

The relationship between jobID and invoiceID is a multivalued dependency because for each pair of jobID/itemID values in the table, the associated set of invoiceID values is determined only by jobID and is independent of invoiceID. The relationship between jobID and itemID is also a multivalued dependency, since the set of itemID values for an jobID/ invoiceID pair is always dependent upon jobID only.

To transform a table with multivalued dependencies into the 4NF (Fourth normal form) move each MVD pair to a new table:

Job_Invoices(jobID, invoiceID)

Job_JobItems(jobID,itemID)

Now you asked about relationship between items and invoices

Consider again table A

jobID invoiceID itemID

J123 Inv11 I789

J124 Inv12 I456

Next we add new job to this table

jobID invoiceID itemID

J123 Inv11 I789

J123 Inv11 I456

J124 Inv12 I456

Now, we project this information into three tables

invoice/items(invoiceID, itemID)

invoiceID itemID

Inv11 I789

Inv11 I456

Inv12 I456

job/items (jobID,itemID)

jobID itemID

J123 I789

J123 I456

J124 I456

job/invoices(jobID, invoiceID)

jobID invoiceID

J123 Inv11

J124 Inv12

However, when we rejoin the tables again, the recombined table contains spurious results.

jobID invoiceID itemID

J123 Inv11 I789

J123 Inv11 I456

J123 Inv12 I456--spurious data

J124 Inv11 I456--spurious data

J124 Inv12 I456

So we can not split the above table into three smaller tables without introducing

incorrect data:

Conclusion

Relational model is made of following tables:

Invoicing(jobID, invoiceID)

InvoiceItem(jobID,itemID)

Job(jobID)

Invoice(invoiceID)

JobItems(itemID)

note

Only pk reported

HTH

Dj