Customer specific pricing Help

[X-Pert Pool]

I've been modifying John Mark Osborne's invoicing solution found at vtc.com

I run a swimming pool company and what I want to do is modify the price of a swimming pool opening based on the total square footage of the pool in the customers record.

I've tried making "swimming pool opening" as regular product with a base price and then made another field called "price_modifier" with a case statement that said if the product seleted was for the pool opening and if the pool was a certain size to return a number into the price modifier field. so for example if a pool was 500 square feet then it would return "10" into the price modifier field and then on the invoice the product price field would actually equal product_price + price_modifier

so far i've only been able to return the first value in the statement, and also it will show up in every product.

here is what I wrote so far...

If anyone can help it would be greatly appreciated.

Case (

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 392;0 <---this 0 is the only value I've returned so far and it shows up no matter what product is picked

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 450 & CUSTOMERS::pool_square_footage_total >392;5

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 512 & CUSTOMERS::pool_square_footage_total >450;10

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 576 & CUSTOMERS::pool_square_footage_total >512;15

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 648 & CUSTOMERS::pool_square_footage_total >576;20

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 720 &CUSTOMERS::pool_square_footage_total >648;25

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 800 & CUSTOMERS::pool_square_footage_total >720;30

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 880 & CUSTOMERS::pool_square_footage_total >800;40

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 880 & CUSTOMERS::pool_square_footage_total >800;40

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 1125 & CUSTOMERS::pool_square_footage_total >880;50

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 1250 & CUSTOMERS::pool_square_footage_total >1125;60

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 1500 & CUSTOMERS::pool_square_footage_total >1250;95

;

_kp_products_id = "PRO000000018" & CUSTOMERS::pool_square_footage_total ≤ 1800 & CUSTOMERS::pool_square_footage_total >1500;120

)

[Vaughan]

For one thing you are using "&" in what looks like a boolean where AND would be more appropriate. The ampersand is the concatenate operator.

[comment]

For another, you should not be hard-coding data into a calculation - especially not price data that changes often. Make yourself a Prices table and look up the prices from there, based on matching the product and the customer-specific dimension (you will need an unstored calculation field in LineItems for this).

[X-Pert Pool]

I'm trying to attempt to go the route you suggested with a new table for product prices. Please keep in mind though, I'm very new to filemaker and sometimes get stuck on relatively easy tasks (for example I got stuck once because I made a field a number instead of a calculation and wondered why it wouldn't show data automatically).

Anyways I've made a new table with a foreign key connected to primary key of products. Do i need another link of keys between prices and customers table?

I'm also a little confused on what to put inside the price field of products_prices. It's supposed to be a calculation right? If so, should I be using a case statement?

Thanks

[comment]

I'm also a little confused on what to put inside the price field of products_prices. It's supposed to be a calculation right?

No, it should be the actual price of the product at that price point - something like:

PriceID  ProductID  FromQty    Price

1        18               0      100

2        18             500       50

3        18             750       40

4        18            1000       25

...

[X-Pert Pool]

Thank you for the information, I'm going to try that. I hate to keep asking questions because I feel like a bother. If I do run into a problem I can't figure out on my own in the future is it against board policy to offer payment via paypal for hands on help with a solution?

[comment]

The board has a 'Services Wanted' section where you can advertise for hired help. But I suggest you try posting your questions first. It's no bother - that's what the board is for.

[X-Pert Pool]

okay

Just to clarify things

I've got a table for products, invoices, lines,

and now I need to add a table for products_prices right?

and inside that table put something like this

PriceID ProductID FromQty Price

1 18 0 100

2 18 500 50

3 18 750 40

4 18 1000 25

...

Or did you mean to get rid of / modify my current products table to work something like that?

Sometimes I can't visualize how to link everything together.

Thanks

[comment]

now I need to add a table for products_prices right?

Right. It's an additional table, a child table of the existing Products table (one product has many prices). Your relationships graph should show:

Invoices -< LineItems >- Products -< Prices

Then you'll need another relationship for looking up the price into LineItems:

LineItems::ProductID = Prices 2::ProductID

AND

LineItems::Qty ≥ Prices 2::FromQty

(Note that this requires a second occurrence of the Prices table.) In this relationship's definition, sort the records from Prices 2 by FromQty, descending. This will turn the highest matching FromQty into the first related record - which is the one you want to get the price from.

[X-Pert Pool]

my prices table has a primarykey for prices and a foreign key for products.

Do i link the prices table to products table via the pricesID or the productsID?

and what would the relationship link be between the prices2 table occurrence and lines?

[comment]

Do i link the prices table to products table via the pricesID or the productsID?

ProductID.

and what would the relationship link be between the prices2 table occurrence and lines?

I thought that was explained in detail in my previous post?

[X-Pert Pool]

Sorry, things just have a hard time clicking once in a while..

So I have a link from prices to products via productID

and I should link prices2 with lines by...

Prices2Table>foreignkey_productsID to LinesTable>foreignkey_productsID

?

I'm really sorry it all just gets so confusing sometimes. I'm trying to learn FMP and it seems like everytime I get something to work right I find another problem.

[comment]

and I should link prices2 with lines by...

LineItems::ProductID = Prices 2::ProductID

AND

LineItems::Qty ≥ Prices 2::FromQty

[X-Pert Pool]

If it's not too much of a bother could you visit the chatroom? It might make it easier for me to understand

Thanks

[comment]

Sorry, that's not possible.

[X-Pert Pool]

that's okay.

I understood now what you meant with lines2

my question is though the example your giving with fromqty. You are just giving a basic example of cost per unit reduction based on quantity right?

Anways I was trying to figure out if I should add more fields into my prices table for example

the price would change based on the square footage of the pool as well as the type of cover

for example lets say the square footage was 392 or less it would be a price of:

$207 with a safety cover

and

$217 with a tarp cover.

Would this be possible with the current setup you're suggesting?

[comment]

It's best to have a separate record in the Prices table for each individual price. Using your example, "swimming pool opening, safety cover" would be one product and "swimming pool opening, tarp cover" another - each with its own set of prices. But it also depends on how your other products are defined - for example, if every product has sub-categories, you may consider another table for those in order to ease the selection.

[X-Pert Pool]

What about a price that doesn't really change much?

For example a 16x32 pool with a safety cover has a price of $200

and a 16x32 pool with a tarp cover is $10 more so it would be $210

having a tarp cover just really adds $10. But I wouldn't want to show that on the invoice, just include it in the price of the "pool opening" product price.

would something like the price field in lines equaling "productprice * qty + coverprice" be a good idea assuming that if a customer didn't have a tarp cover that field would equal zero? Although I think this is something I tried to do before but came to the conclusion that coverprice would alter the price on other products as well not just the "pool opening" product

[comment]

would something like the price field in lines equaling "productprice * qty + coverprice" be a good idea

I am not sure I understand this. Where would the coverprice come from? There's no problem with products having two "prices" (or more correctly, two components of a price) - as long as the arrangement is consistent among all products.

having a tarp cover just really adds $10. But I wouldn't want to show that on the invoice

Well, you could have another description field in the Products table for showing to the customer. For example, you would select between "18 swimming pool opening, safety cover" and "19 swimming pool opening, tarp cover" - but in another field both products would be described as just "Swimming Pool Opening" and that's what you would show on the invoice (again, if I understand correctly).