Portal problems

[Johnny777]

Hi all

I have a database laid out as follows Contacts, sales, and products with two joining tables between contacts/sales and sales/products. I then have a portal in Contacts to show sales.

Ideally I would like to be able to enter sales directly into Contacts.

Unfortunately I cannot get the drop down list of productID's on the Sales portal in contacts to show up. The portal fields in Contacts are set up the same as the corresponding fields in sales and these fields perform a lookup from the Sales/Product join table.

Can anyone poss give me some clues as to what I maybe doing wrong.

If anyone needs anything clarifying please don't hesitate to ask

Many thanks for reading

[bcooney]

This is a typical Client->Invoice->InvoiceLineItems->Products setup, but you might be missing a table, although you mention join tables (what are they called?).

You would have a portal on Client to see his invoices (Invoices stores a client foreign key). However, you wouldn't create a new Invoice for a client by directly entering it into the portal, but rather scripting a New Invoice.

In InvoiceLineItems you would specify a ProductID. It would lookup the Product Desc, and Price, etc.

You could have on your Client Form a portal to InvoiceLineItems. Just autoenter the ClientID when you create an invoiceline item.

Attached is a quick and dirty demo.

InvoiceDemo.zip

[comment]

A join table between Contacts and Sales is not exactly typical. I am not sure if this is a mistake, or if there truly is a many-to-many relationship between them.

In any case, it won't be possible to enter items into a sale from the point-of-view of Contacts, until ONE SPECIFIC sale is selected for this purpose, e.g. by entering SaleID into a global field in Contacts. The portal must be then based on a new relationship using the global.

[Johnny777]

Hi

Thanks for that - you have basically clarified what I was beginning to conclude. ie that it was going to be too problematic to enter sales directly into clients. As my database allows for more than one client per sale it becomes doubly difficult

I will try to add a button that allows the user to set the sale as your demo version does.

Many thanks once again for taking the time to reply

[bcooney]

More than one client per sale? I've never seen that. Can you elaborate?

Also, if your sales did not have sale line items, then you could enter then directly into a portal on Clients. But, most sales have line items.

Edited October 17, 2008 by Guest

[Johnny777]

Basically some of the sales are events ie briefings. We often have more than one person from a company attending, so they go on the same sale. This should then allow a much more accurate picture to be built up in regards of exactly who has attended each event.

So basically my database is the same as the demo you sent me but I have another join table sitting between Clients and Invoices/sales.

I have managed to get the button working which takes the user from clients to invoices and enters the client details in the new sale so thanks a lot for that.

[bcooney]

I took "Sales" to mean Invoices.

You define a Sale to be both an Event and an Invoice? Why not reference an Event on the Invoice. Then you'd know where the "Sale" was made.

I see Events as a table, and a Registration join table btw Events and Contacts. Contacts also relates to Clients (Companies).

[Johnny777]

Hi

Not quite sure what you mean. The sales table can be a number of different products (events or mini conferences, subscriptions, royalties etc) There is then a seperate table for invoices with a portal back to the sales table to show the invoice number for each sale (There is only one matching invoice per sale). I have used a seperate invoice table because for a subscription there is a new invoice every year. If I could work out how to upload a small diagram I would send you a small plan showing the layout

[bcooney]

I think you might not have the best data model setup. Can you describe the business rules, or post an ERD?

[Johnny777]

HI

I have attached an ERD of my database

Just to clarify

New Leads is the main contacts table

Sales is what some people may expect to be the invoice table. There is another invoice table called invoice test as at this point I haven't decided whether to store invoices in a seperate table as some of them are recurring (once a year)

There are then two join tables between newleads and sales and sales and sales products.

The sales can be one of two types: a single contact and multiple products or one product and multiple contacts but not a combination of the two ie not multiple contacts and multiple products.

There are two portals in sales - one to salesmergejoin and one to salesspjoin. At the moment most of it is working OK but am struggling trying to do print layouts.

Thanks for reading

[bcooney]

This is not an ERD, it's a shot of the relationship graph--not the same thing.

If you can go into a drawing program and do an erd that would be great. As it is, I'm not sure I have time to unravel this. Anyone else with more free time willing to help?