Nested reciprocal portal needed

[Steve Gunther]

I have designed a stock management and ordering database.

Tables:

Stock

Orders

Suppliers

So an request-order gets created - new record, with name of stock item. Lets say vinegar. But there are two suppliers of vinegar - A and B. The staff entering the order randomly just selects one of them.

When the manager comes to place all the orders, they go to a portal which shows all the items requested by staff, organised under by supplier.

Now, I want to extend this. So that although the request was for vinegar/ supplier A, I want the portal somehow to also show supplier B, and the manager will then make a choice as to which vinegar to order.

But I still want the stock requested to be listed under supplier. So there wil the stock actually requested (Vinegar, supplier A), and then I want the portal to show the extra choices for products which have more than one supplier, also appear under each supplier (so supplier B will also appear in the portal, with Vinegar underneath).

The closest I have achieved so far is in the request layout to show a table of the suppliers for each product...but cant work out how to achieve what I want in the Manager ordering layout. Screen shots accompany.

So, to try to make this clearer. If an item has been requested, I need all the alternative suppliers to appear, but as supplier headings, under which the item appears, not as in the portal in the screen shot where the suppliers are simply listed underneath the product. The products need to be listed under the suppliers, as at present, but somehow the parallel products need to also appear, and call up the appropriate supplier.

Another way to do it might be at the stage of the requests. If there was some way to generate records automatically from the infomation in those portals in the request layout - if vinegar has the two suppliers, then some way to extract the two item numbers from the portal and generate two records, one for vinegar supplier A, and one from supplier B. They would then appear in the current Manger layout. But I dont know how to auto-generate records from the portal information.

Steve

Picture_29.pdf

Picture_28.pdf

[comment]

A portal always show records from a single table, one row per record (though a portal row may also show related FIELDS from other tables).

I am not sure it's such a good idea to add artificial records for products that haven't been actually ordered: how will you be able to tell them apart?

You haven't explained what will the manager do with this information. Wouldn't it be sufficient to place an alert on an order, in the form of "There are 3 other suppliers that carry this product" - perhaps with an option to view a detailed comparison in another portal?

---

P.S. Your attachments are PNG's, but they have a .pdf extension?!

P.P.S. I would give serious thought towards toning down those layouts a bit. I mean, it's not possible to place an alert there - it would just blend with the rest.

[Steve Gunther]

Thanks. Yes, my wife criticises my overuse of colour.

I see what you mean - could put that alert in. But I guess I just want to display all the options. I think if I create the extra records, and a portal to view them, then that can take care of the problem.

[David Jondreau]

But there are two suppliers of vinegar - A and B. The staff entering the order randomly just selects one of them.

Randomly choosing is an odd business practice. Why not leave the Supplier selection up entirely up to the manager? And what criteria does a manager use to choose one Supplier over the other?

It sounds like what you're doing is creating a way for managers to select a supplier for a product on order. The manager really only needs to see the various suppliers and quantities for the different products when selecting the supplier...so that information should be presented when a supplier.

If it were me, I'd leave the Supplier ID empty until the manager chooses a Supplier by clicking on the Supplier field, triggering a script. That would bring up a selection window or selection tab showing all the info the manager needs to make his choice.

Edited July 26, 2008 by Guest
typos

[comment]

I would never consider adding records that do not carry real information. If Adam ordered widgets made by Acme, the fact that widgets are also made by BrandProducts is already in the database. Creating a duplicate order of the same widgets adds no information to the system - though it may well add confusion. What should the portal show when Adam orders 5 widgets made by Acme, and Betty orders 3 widgets made by BrandProducts?

Besides, I'd assume a manager goes over these orders one-by-one - so I don't se the point of showing ALL the options at once. I would let them click the currently considered order and show the relevant alternatives in another area of the layout.