Help: Thinking of a way to bundling records together

[SteveGriff]

Hi people. I wish to share some ideas and hopefully will recieve some handy hints on setting up this little process that I will now tell you about.

Well, firstly, we've got three databases, Products, Suppliers and Product-Supplier. The Product-Supplier database is there to solve the many to many situation between Products and Suppliers.

What happens is, when someone needs to place an order to the supplier, you can choose between more than one supplier for each product. To solve this, by next each product is a drop down list of all the suppliers you can buy that product from.

Once you have made your supplier selection for each product, you will be able to press a "next" button on the layout which will then execute a script to group each product to a supplier.

This is where it gets messy. What I want is the next layout to have each product to be underneath each supplier. Like this: -

Supplier 1

Product a

Product b

Product c

Supplier 2

Product d

Supplier 3

Product e

Product f

etc. I'll take it something like this would be done within a portal of some sort. Any ideas on how to accomplish something like this? By each product you will have the price of the item according to how much the supplier charges for it. This layout will be handy for a "what are we buying from who" report. So you can make a final look before placing an order with the supplier.

I could see this being done pretty straightforward with a SQL and ASP/PHP based system but I'm using FM so lets see if we can solve it

Thanxs for your time people

Steve Griff

[zincb]

The three files you mention will handle the supply side of the equation. You also need another file to handle the demand side, one called Orders. You might be able to use the Supplier/Product file but it will get messy very quickly.

It is in the Orders file that you first select the Product in which you are interested. If there are only a few products, they could all be listed. If there are a million products, you should use a droplist for the Product, also. To create the Value List you will have to select "use values from a field" and "all values" from the field Product Name, in the Product file.

Still in the Orders file, to make the Supplier dropdown sensitive to the Product that is selected, a Relationship must be made between the Orders file and the Supplier/Product file, based on Product Name. To create the Value List for Supplier you must then select "use values from a field" and "only related values" from Supplier Name, via the relationship just created.

Now create a calculated field in both the Supplier/Product file and in the Order file: ( Supplier Name & " " & Product Name ) and in the Order file link these two field with a relationship.

Next, create a new row or table layout in the Orders file and put the following fields on it:

Orders:Product Name, Orders:Supplier, Supplier/Price:Price, Orders:Quantity

On the Product Name field add the Product value list; on the Supplier Name field, add the Supplier value list.

Throw in a New Record button and create and fill in all you orders. When finished, simply sort by Supplier and Product, and you will have your list.

You can add a total Quantity subsummary to separate the suppliers visually when printed.