Ask Your Question
0

Hello, I'm using Base to build a small business database to create invoices for my customers but I am struggling as all my customers pay different prices for the same products. Can someone suggest how I can integrate this in the design?

asked 2016-10-17 18:59:35 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Thanks in advance

edit retag flag offensive close merge delete

Comments

How do you currently determine what that price is for each customer? A percentage? A specific price? Based on quantity? Is each product a different pricing structure? How many products are you dealing with? Information is key to figuring out what may be needed.

Ratslinger gravatar imageRatslinger ( 2016-10-17 20:02:54 +0100 )edit

Thanks for your response Ratslinger. I appreciate I was a little vague. I have around 150 products and 50 or so regular customers. 80% of the prices are standard across the board but the others are specific prices to each customer (most individually negotiated to beat existing suppliers / encourage repeat orders).

ScottB gravatar imageScottB ( 2016-10-17 20:11:29 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2016-10-17 21:20:46 +0100

Ratslinger gravatar image

With 150 products and 80% standard prices, a product line for each customer is unreasonable. If a price changes, you would need to change it in 50 places.

With a price table containing the product number (or name - unique identifier) and a customer number (with say customer #999999 being the standard price and non 999999 being specific to a customer it can be done with some code in a macro. Can't see any custom pricing without some coding. Mostly done with SQL like (pseudo code):

SQL = Select Product, Price From tableX where Product = XXXXX and Cust# = XXXXXX
Execute SQL statement
Check return
If return empty Then
    SQL = Select Product, Price From tableX where Product = XXXXX and Cust# = 999999
    Execute SQL statement
    Check return
    If return empty Then
       Display INVALID PRODUCT NUMBER
       Exit Routine
    EndIf
EndIF
Retrieve Product and Price.

The first SQL statement checks if there is a special price. If not then get the standard price. If that is not found, it is an invalid product number.

To me, keeping special pricing in a separate table would be more confusing for managing purposes. Multiple places to look and still requires some coding.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-17 18:59:35 +0100

Seen: 307 times

Last updated: Oct 17 '16