updating/ not updating

[peaceandplenty]

Say you have an invoice for widgets. One customer pays X, another pays Y for the same widget. How do I construct a relationship between INVOICES and PRODUCT PRICES that would allow this?

 

And further, what if the price I charge customers increases. How do I keep an old invoice from showing the newer values?

 

And when I make a new invoice, how would I populate a value list for the widget so I would see all the prices paid by all previous customers? 

 

I looked at the starter DBs for some clues but don't see how they handled this, which must be a pretty common feature of things like proposals and invoices, no?

 

Maybe I need to think about this in a different way?

[comment]

One customer pays X, another pays Y for the same widget. How do I construct a relationship between INVOICES and PRODUCT PRICES that would allow this?

 

By creating a relationship that matches both on ProductID and CustomerID. In most situations, such relationship would be between ProductPrices and LineItems - not Invoices.

 

 

what if the price I charge customers increases. How do I keep an old invoice from showing the newer values?

 

By looking up the related price into a field in Invoices (again, usually in Lineitems).

 

 

I didn't understand your last question. Value lists are used to facilitate data entry - not to view records. You could probably use a portal for this, or find the relevant records and view them in list view.

[Matthew F]

Keep in mind that your invoice should have its own field for product price and it should not simply display a related field from your pricing catalog.  Its fine to create a relationship between the two, e.g. to provide popup or drop-down functionality, but the invoice needs to have its own data field which is static and not dependent on changes to your product catalog.  

[Matthew F]

 

 

when I make a new invoice, how would I populate a value list for the widget so I would see all the prices paid by all previous customers?

 

If you really want to do this, then add a second Invoices table occurrence and create a self-join relationship with the main Invoices table via the product ID. Also create a value list which references the prices in the new self-joined table, for your price pop-up.   This will literally give you all of the older prices.  

 

I'm guessing that you would actually want a better way to control this and not simply show all previously invoiced prices. Why not link back to your product catalog and show both current prices and previous prices? In your product catalog you can create more than one entry for the same item, with a version number or a field to toggle the currently active price, or some other price categorization scheme depending your business practice.  

[David Jondreau]

For the value list, you could use your newly created table occurrence ( or another one just based on the product ID/name) to show the price and the client name.

[peaceandplenty]

Thank you EVERYone for your answers. I can't thank you enough as I slog through this. DB design is oddly compelling, and interesting, but not my day job.

Matthew said: "Keep in mind that your invoice should have its own field for product price and it should not simply display a related field from your pricing catalog. Its fine to create a relationship between the two, e.g. to provide popup or drop-down functionality, but the invoice needs to have its own data field which is static and not dependent on changes to your product catalog."

And that is exactly how I have been conceptualizing it. But why is it wrong? I'm not clear on 'lineItems'. Is this a field in the Invoices DB? or?And how is that different from just grabbing the price from the Products table? I see that it somehow lives between the two tables, but flummoxed on how to create it?

[comment]

 I'm not clear on 'lineItems'. Is this a field in the Invoices DB? or?And how is that different from just grabbing the price from the Products table? I see that it somehow lives between the two tables, but flummoxed on how to create it?

 

Will your invoice always be for one item only, e.g. "12 widgets"? Or is it possible for your customer to buy several items on the same invoice, e.g. "12 widgets, 8 gadgets and 7 doodads"?

 

If the latter, you will need a record in a related Lineitems table for each item bought. Otherwise your Invoices table would need an unknown number of fields - large enough to accommodate any future set of purchases. Much worse, it would be virtually impossible to produce a report of sales by product, for example.

[peaceandplenty]

I think I need to step back in terms of an explanation, Matthew, as I'm not seeing what the tables are exactly. I get how to look up a price from one table to the next, which is what I've been doing, but don't get how to insert the line items that you are describing.

 

Are saying that each item on the Invoice is an event that consists of the widget and the quantity, so the invoice is a sort of a list of purchase events?

 

Look at the starter solution that comes with FMP12, which is what started, and why I began this thread. I don't see anything there that looks like what you are describing (also not sure how this forum handles images; I uploaded a screen cap).

 

 

[comment]

1. I am not Matthew.

 

2. In your screen shot, the Invoice Data table is a line items table (a rose by any other name...).

 

 

See also:

http://fmforums.com/forum/showpost.php?post/309136/

[Matthew F]

I agree with Comment. When I wrote Invoice Line Items I meant individual items purchased on a single invoice, which in your case is the Invoice Data table. You probably will display these data on the Invoices layout in a portal.  According to your relationship graph you would want to use the Products table for a value list of all product names.  

 

Use the Invoice data::Products relationship to show the prices once a product has been specified in Invoice data. You could handle your problem of multiple prices in different ways. One way is apparent on your Invoice data table already and employs a user-specified discount rate. The other way that I had suggested is to enter the same product more than once in your Products table (which I referred to as a "Catalog").  This would allow you to update prices by creating a new Product record, without removing the old pricing data.  

 

Note: If having multiple product entries, with different prices is too confusing, then you may want to create separate Product table (one record per product model) and a Product Catalog table (one record each time a product price changes). You could sort the latter by date to see a products price history. 

[comment]

The other way that I had suggested is to enter the same product more than once in your Products table

 

No, I don't think you would ever want to do that. It violates 2NF - which in practical terms means that whenever you wanted to modify a product's attribute other than the price (e.g. product description), you would need to perform the same modification several times (as many times as there are customers with special price for that product). That's exactly what a relational database is designed to prevent.

 

The correct approach is to have a ProductPrices table (which is mentioned in the opening post of this thread but doesn't appear in the screen shot attached later), with fields for:

• ProductID

• CustomerID

• Price (or Discount, either as a rate or as a fixed amount)

 

 

I am not sure a history of price changes is required here (other than the history of prices actually charged, reflected in the LineItems records).

[Matthew F]

The correct approach is to have a ProductPrices table

Yes, that's what I meant by a "Product Catalog" table. "Product Price" is a better name.