Dynamic price calculation - complex case


I wish to implement a rather complex price calculation system for the objects that will be for sale in my Drupal Commerce store.

A basic description of what should be achieved:

A buyer arrives at a product page, and clicks the "Buy" button. A popup window appears where the user is presented with a dropdown lists wizard that are depending on each other.
Imagine 5 select lists, with changing options based on the previous selection:
select box 1 - type
select box 2 - region
select box 3 - size
select box 4 - edition
select box 5 - position

Each combination of any of the values in these select boxes will result in a specific price (practically speaking we are talking 1800 combinations).

Once the user has decided his preferred combination, he can click the "Add to cart" button. The item will be added to the shopping cart with the price that matches the combination of the 5 chosen select boxes.

Sounds pretty challenging right :)

I'd like to hear your opinions on how you would implement this, using as much as the possibilities that Drupal Commerce provides by default (i'm still experimenting my way through it as we speak).

I imagine i will need to have a custom display formatter for my product entity field that opens a popup, replacing the standard "Add to cart" formatter.
For the whole price selection mechanism I suspect I will have to write my own backend to store all price combinations, and present them in dependent dropdown lists.

After that step it's currently a bit blurry what should happen. I assume clicking the button I can make some API call to add the item to the shopping cart with the correct price based on the user selections...

My questions:
- Do you think this should be possible to implement given the current state of affairs? I'm not expecting this to work out-of-the-box of course...
- Do you think I can leverage Rules somewhere in the workflow? Because I'd like to avoid spending a lot of time researching that if it's not going to be useful. Note that I do not plan to add 1800 rules entries for each possible combination :)
- Is there some "attributes" system like Ubercart had, that could be leveraged? Although, again, I do not plan to add 1800 attribute combinations into the system manually :)
- Are there some other modules, concepts or APIs that I should be aware of to make my life a bit easier?

If I could borrow some of your brains for a few minutes to give me some ideas here, i'd be very grateful :) Thanks in advance.

Posted: Apr 4, 2011


svendecabooter on April 4, 2011

Upon further thinking, perhaps I'm going at it the wrong way...
These 1800 price variations apply to all products in the store, so perhaps I should create 1800 product entity items, and then X number of product nodes for each actual item for sale.

Of course then each product node would have a reference to 1800 entities, which might not be database optimal... Furthermore i'd still need to figure out the dependent dropdown method then.

Scott J on April 5, 2011

Woa there,
You have 1800 variations PER PRODUCT?!?

How many choices are there in each of your 5 select lists? It sounds like you need to experiment a little with a small subset of products to see if Commerce will work for you.

Scott J on April 5, 2011

I don't think that I understand the problem; what you seem to be describing is exactly the way that Commerce works, "out-of-the-box":

  • "dropdown lists that are depending on each other" - Yes
  • "changing options based on the previous selection" - Yes
  • "Each combination of any of the values in these select boxes will result in a specific price" - Yes
  • "Once the user has decided his preferred combination, he can click the "Add to cart" button" - Yes
  • "The item will be added to the shopping cart with the price that matches the combination of the 5 chosen select boxes." - Yep again

I'm struggling a bit here...

svendecabooter on April 5, 2011

Yeah I hadn't really discovered that feature yet ;)
I'm doing some tests now... I'm getting closer, but getting stuck every time due to the complexity of the parameters...

I'll prepare a more detailed description and example of what i'm trying to achieve, hoping someone wants to help me figuring out how to crack that nut :)


svendecabooter on April 5, 2011

I figured i'd provide some more detailed information.
The site is aimed at selling high quality photographs. The price is not attached to a single photograph, but rather based on a complex set of parameters that define the usage of the photograph (e.g. in a book, magazine, on the internet, ...)

To make it even more interesting, the parameters are different depending on that main category.
I've uploaded an example of the price calculation to Google Docs so it would make more sense to understand:

I'm currently investigating to get this spreadsheet represented in as much product entities as there are spreadsheet rows (rows with actual data of course).
Though that is proving to be a challenge given the variable nature of parameter 1 to 4. I'm testing with dumping all this various data into 4 "container" product entity fields (field_parameter_1 to _4), hoping the appropriate selections will come up, based on the entity items that are stored.

However if that would work, the next problem would be that every product node (= photograph) in effect would have to have about 1800 product references, to be able to have the dropdowns filled up. Which is probably a nightmare on the DB / performance level...

If anyone has ideas or pointers on how this could be implemented, let me know.
We'd also be willing to pay freelancers / companies that would be able to provide a decent solution for this problem if that's what it takes.

Scott J on April 6, 2011

That is certainly an amazing price list. Whoever worked that out is either mad or genius! Is there logic to it?

If "the price is not attached to a single photograph", then I think that the answer may be to change your definition of what a product is. Maybe it's the size/placement that is for sale, so that becomes the product? With the other columns as the options? Something like that should be workable.

Certainly it would not be workable to have 1800 product references, so that must be the wrong way to think about it.