Jump to content

Customer specific pricing Help


This topic is 4311 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

...

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

okay :P

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This topic is 4311 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.