Jump to content
Sign in to follow this  
whoo-whoo

Defining price based on selected customer (special pricing)

Recommended Posts

Hello,

I need to create an invoice that, when a specific customer is selected, the price field will display pre-defined prices that are set for each individual customer.

I found a sample invoice solution in this forum which I will use to help pose my question. Please refer to http://fmforums.com/forum/showpost.php?post/309136/

Using this example, I'd like to have the following happen:

Adam should pay $1 per "Alpha", Betty $2 per "Alpha", Cecil $3 per "Alpha", David $4 per "Alpha" and Eve $5 per "Alpha". Ideally when the customer is selected and an "Alpha" product is added to their invoice, the price would automatically change to reflect their individual setting.

However: For all other products (Bravo, Charlie, Delta, etc.) the prices should be constant for every customer.

Would anyone know a good way to modify this DB to have this happen?

Thanks kindly!

Share this post


Link to post
Share on other sites

Off the top of my head I would think you would want a join table ('Product_Contacts') that stored prices for each product for each contact.

You could then use an auto enter calculation that looked to the 'Product_Contacts' table and used that price if it found a match, otherwise it would use the 'default' value.

Share this post


Link to post
Share on other sites

Two approaches come to mind. Both involve a child table to products for various prices.

1. Do the prices really vary by customer or is there a customer type? If there is a customer type, then create a join table btw products and customer type. This table would have both the foreign keys and the price.

2. If it truly is a price special to each customer, and there is no customer type, then you need a join btw customer and product which will hold the price. If there is no record in this table, then you could look to the default price in the products table.

Share this post


Link to post
Share on other sites

2. If it truly is a price special to each customer, and there is no customer type, then you need a join btw customer and product which will hold the price. If there is no record in this table, then you could look to the default price in the products table.

Attached is a quick modification of the file you referred to earlier, to illustrate how a join table might be used. You'll notice that the Contact 'Adam' has individual pricing for some items. If you create a new invoice and select those products you will see that the new price is selected. Any other products will use the 'default' price.

Obviously it would need some further work to tidy up, but it will hopefully point you in the right direction.

InvoicesDemo.zip

Share this post


Link to post
Share on other sites

That is absolutely what I was hoping for, thanks for your work on that! I very much appreciate it. Works like a charm.

Share this post


Link to post
Share on other sites

No problem.

The one caveat with this method is that it relies on having the Customer ID in the Line Items record. Currently it is set as a lookup, so when a new Line Item record is created, it copies the Customer ID. However, if you were to then change the Customer on the Invoice, the corresponding ID in the related Line Items would not change. To deal with this, I suggest using a trigger attached to the Customer ID field in the Invoice table, that runs a script to update the Customer ID in any related Line Item records.

Hope this makes sense, let me know if you want me to amend the example file to demonstrate.

Matt

Share this post


Link to post
Share on other sites

Actually, I believe that the ContactID in LineItems can be an unstored calculation of Invoices::ContactID since Lineitems represents the unstored 'left' side of the relationship to the contracts price table.

Share this post


Link to post
Share on other sites

Actually, I believe that the ContactID in LineItems can be an unstored calculation of Invoices::ContactID since Lineitems represents the unstored 'left' side of the relationship to the contracts price table.

Yes, thanks for pointing that out. This would work too, although then it would also be necessary to make the price a calculation field too, as it does not update on it's own (unless a trigger was put into the auto enter calc....)

Share this post


Link to post
Share on other sites

A slightly different perspective ... I cannot imagine how one would ever change the ContactID in a LineItem much less even an Invoice itself. And if you change the product, it does indeed update the price.

Share this post


Link to post
Share on other sites

Actually, I believe that the ContactID in LineItems can be an unstored calculation of Invoices::ContactID

I recall some issues with this method (which seem to linger even in version 10):

http://www.fmforums.com/forum/showtopic.php?tid/181592/

Share this post


Link to post
Share on other sites

Ah yes indeed. I had forgotten about that anomoly. It only breaks when creating LineItems via portal and I always create lineitems by going to the LineItem table, in which case it works fine.

Thanks for bringing this up again, Michael.

Share this post


Link to post
Share on other sites

Thanks for bringing this up again, Michael.

I take it back. Now I'm going crazy trying to understand the logic of it again.

I considered that it might be field creation order but nothing I try works. It obviously has to do with not recognizing the InvoiceID (while the LineItem is being created) so it can't recognize the CustomerID yet (so revolves around the auto-enter vs. commit type behavior which has plagued me during imports and other such issues). But it STILL should update - it is unstored!! The only way the unstored calc refreshes is when forced (flush cached join results) otherwise the file must be closed (or the record changed) before it inherits the unstored value.

Now I wonder where else we might be having breaks because of this type of inconsistency? It isn't even the fact that the LineItem record is new but we aren't currently in the LineItem table because, if we simply Set Field [ LineItems::InvoiceID ; 1 ] it inherits the cCustomerID! Then it works! It's the portal, it seems! [color:green]... nope, because we can create a record in the portal by setting the InvoiceID and the cCustomerID populates as well and the lookup works great.

How many other unstored calculations are not populating that we may be depending upon during script execution?

Edited by Guest
Added green

Share this post


Link to post
Share on other sites

I titled that thread "Bug? Gremlin? Feature?". :

Share this post


Link to post
Share on other sites

Yes I know you did!! And, at the time, I think I was convinced it was a bug but I was very very busy at that time in my life (working 80-hour weeks) and I think I just didn't get back to it!! But now I am!!!

IT IS A MAJOR BUG!!! Certainly NOT a feature!! And too ugly to be a gremlin!

Edited by Guest

Share this post


Link to post
Share on other sites

Hey Michael.

I think you should report this on the FM Bug forum. I think that the forum may be a better avenue than the submit bug form.

Share this post


Link to post
Share on other sites

Is there ANY way of even attaching a file there? Their website is certainly not FM Forums quality but I agree that it would be good to raise the visibility of this to their website directly!

Edited by Guest
Added part sentence

Share this post


Link to post
Share on other sites

Certainly NOT a feature!! And too ugly to be a gremlin!

Well that depends on whether you are talking about Gizmo or one of the other gremlins...

gremlins-groupshot.jpg

Share this post


Link to post
Share on other sites

Actually, the one with the little owl face is quite cute!

Share this post


Link to post
Share on other sites

Yes that was my point... Gizmo is the cute one as he was the main character... The others were just ugly...

Share this post


Link to post
Share on other sites

I think you should report this on the FM Bug forum.

FWIW, I did. I just hope that... well, you know what.

Share this post


Link to post
Share on other sites

OHHHH!!! That's Gizmo! I guess I should see that movie!!

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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