Recalculation Help Required

[oakbridge]

I've taken over an existing project. I'm having a problem fixing a bug in a quotation system. There are 2 files involved, sales quotes and price list.

In the price list, there are three fields, price, member set price and member price. If member set price is empty, member price is set to price, if not, member price is set to member set price. That way certain products have a member price, others don't.

In the sales quotes database, there are fields for unit price and another for member unit price, both are lookup fields from their respective fields in price list.

When a new line item is created in sales quotes (repeating fields, not my choice) the prices are brought in correctly and all is well in the world. However when a user modifies the unit price to be something different than what is in price list, member unit price is not being updated. This causes a problem because there is a summary field that calculates and displays a member's savings.

I've considered making sales quotes: member unit price unstored which I think will fix the recalculation problem, however I think I need it to be a stored value.

Does anyone have any suggestions, or can point me in the right direction?

Thanks,

Edited January 26, 2006 by Guest
changed formatting to make it easier to read

[Lee Smith]

You have so many of the << and >>, I'm having trouble following your question.

BTW, the << >> are used by FileMaker as Merge Fields.

You can use other means to show your fields, such as Bold, Color, etc. See if this isn't easier to read.

I've taken over an existing project. I'm having a problem fixing a bug in a quotation system. There are 2 files involved, sales quotes and price list.

In the price list, there are three fields, price, member set price and member price. If member set price is empty, member price is set to price, if not, member price is set to member set price. That way certain products have a member price, others don't.

In the sales quotes database, there are fields for unit price and another for member unit price, both are lookup fields from their respective fields in price list.

When a new line item is created in sales quotes (repeating fields, not my choice) the prices are brought in correctly and all is well in the world. However when a user modifies the unit price to be something different than what is in price list, member unit price is not being updated. This causes a problem because there is a summary field that calculates and displays a member's savings.

I've considered making sales quotes: member unit price unstored which I think will fix the recalculation problem, however I think I need it to be a stored value.

Does anyone have any suggestions, or can point me in the right direction?

Thanks,

Also, I have relocated your question to this Topic Area as it fits the sprit of your question.

Lee

Edited January 26, 2006 by Guest

[oakbridge]

I've re-edited the original post as per your suggestion. Thank you.

I have made some progress on my own. I changed unit price to be an unstored calculation field:

If(PRICE LIST2::CMA Set Price=0, Soft price, PRICE LIST2::CMA Price)

which corrects the problem with the modifications. However member price is never coming across from price list. It appears that the above calculation is always passing (i.e. FM always treats CMA Set Price to be zero, even when it isn't).

Any suggestions?

Thanks,

Edited January 26, 2006 by Guest

[oakbridge]

I was able to solve my own problem but it seems to be a poor fix. I was trying to do an IF(...) on a field that was in a related field. I couldn't seem to get that to work.

So I created a new field in Sales Quotes that is a lookup to the field in Price List. I changed my calculation to check that field instead of the related field. Now things work.

Perhaps someone could confirm that you cannot do this in a calculated field:

If(PRICE LIST2::CMA Set Price=0, Soft price, PRICE LIST2::CMA Price)

when I change it to this:

If(CMA Set Price=0, Soft price, CMA Price)

it works but I have to duplicate the data now in two fields. Seems to be a poor database design.

If someone could either confirm this or provide me with what I'm doing wrong, I'd be grateful.

Thanks,