Defining price based on selected customer (special pricing)

[whoo-whoo]

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!

[tv_kid]

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.

[bcooney]

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.

[tv_kid]

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

[whoo-whoo]

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

[tv_kid]

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

[LaRetta]

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.

[tv_kid]

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

[LaRetta]

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.

[comment]

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/

[LaRetta]

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.

[LaRetta]

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 May 21, 2009 by Guest
Added green

[comment]

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

[LaRetta]

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 May 21, 2009 by Guest

[mr_vodka]

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.

[LaRetta]

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 May 21, 2009 by Guest
Added part sentence

[mr_vodka]

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

[LaRetta]

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

[mr_vodka]

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

[comment]

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

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

[LaRetta]

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

[Daniel Farnan]

Technically, Gizmo is a mogwai, not a gremlin. ;)