Ok I have an invoicing system(with all the usual, lineItem, inventory, vendor, invoice tables) for a parts database that works great!

We are part of an insitute so we have many departments, for eg. Graphics Design, Audio, Video etc). All departments have fixed cost for each service thy provide

Graphic Design department for eg has the folowing cost:

- Color Printing

- Scanning

- Laminating

- etc

And Audio department would have something like:

- Cassette Digitising

- Cassette to Cassette Dubbing

- Cassette to CD Dubbing

- etc

1) Firstly, Do I create a table for each department and link in to the lineItem table?

2) I want to a user to go to a different layout based on get(accountName). So when a person from graphic department logs in, he can create an invoice by a pull down menu that brings up the fixed cost "ONLY" from the graphics table..

is there anything wrong with this? Can u suggest any other ideas please...

Thank you for reading this and hope you comment..

One way to solve this may be Conditional Value Lists. See my post and the resulting answers here: http://www.fmforums.com/forum/showtopic.php?tid/174087/post/194371/hl//

Just change my values for "Type" - "Vegetables, Fruits, Meats" to "Graphics Dept., Audio Dept., " and then change what I've got for "Item" - "tomatos, corn, apples, etc..." to whatever the service item is provided by that dept.,

You can then add a field for the fixed cost associated with that service.

Thank you toolman...

Umm.. what I have done is created a separate table for each department with the following fields:

Charges_ID(PK),Description and price.

And then I have linked this table to "LineItem" (Charges_ID is FK) table.

Then I have created a separate layout for each department. Data is coming from LineItem Table. On this layout I have a DropDown menu that pulls data from charges table.

What do you think of this?

You should not use separate tables for each Department. Instead, use one Service table, where Department is just an attribute (field). This can then be used to filter the relationship for your conditional value list.

Thank you Ender.

But I do not how to accomplish what you have said. So I have a service table with department, price, and type(conditional) fields?

And is this service table is linked to LineItemTable?

OH I forgot to mention some of these departments uses CD/DVD to dubbing, digitising,burning a copy for the client. So will this come from the inventory table?