Jump to content

Recalculation Help Required


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

Recommended Posts

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 by Guest
changed formatting to make it easier to read
Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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,

Link to comment
Share on other sites

This topic is 5717 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.