Dynamic Inventory Pricing

[kjoe12]

Was trying to find the right place to put this, hopefully I will get some answers here.

 

So basically I am trying to create a matrix style inventory database. The basic idea is that we have about 200 products and 700 or so customers. For the 200 products, each customer may pay a slightly different price. For invoicing purposes, I would like for FileMaker to know what the price of a product is once a customer is selected.

 

Now I am looking at this in two ways, there could be more, which is why I am here!

 

 

I figure that one approach is to have a table for each customer, with two columns: product # and price.

 

Second approach is to have a table for each product, with two columns: customer id and price.

 

 

 

Ideally, I would want a table with the far left column as the customer ids, and the column headers be the part numbers, thus creating a sort of price matrix. This would require a ton of column headers, which I am fine with if this is the best solution, but I figured I would see if anyone else has suggestions.

 

Thanks.

[eos]

I figure that one approach is to have a table for each customer, with two columns: product # and price.

 

Second approach is to have a table for each product, with two columns: customer id and price.

 

Now combine those two approaches, and instead of many tables, use one table with many records: each one a combination of customer, product and price.

 

Displaying those data in a matrix is an altogether different matter. I think that using a looping script to produce an HTML table is the most flexible approach: the only fixed element is the Web Viewer (its dimensions, to be precise) in which you display this table; everything else comes dynamically from your data – the number of columns and rows and the contents of the cells.

[kjoe12]

Ahh thank you for your reply. That makes total sense, I think I was over thinking the problem! I guess I was stuck in an Excel world.

 

I don't necessarily ever have to view it in matrix form, it was just my term to help describe the kind of design I wanted, but now that you mention the HTML table, perhaps I could think of some uses. Very helpful thank you!