I have a classified advertising database and I need it to figure the following for an ad based on word count.

It costs $40 for a minimum of 25 words and an additional $1.50 a word over 25.

I would like to be able to keep it where the dollar amount and word count can go up over time.

Thanks...and I am extremely new to FMP.

This is how I would approach it. Use a preferences table so that you can change the criteria in the future. The preferences table would serve as a lookup price list each time a new Ad record would be created.

Advertising.zip

I would lookup/auto-enter the pricing into three fields:

- MinimumWords

- BasePrice

- ExtraPrice

then calculate the price as:

BasePrice + ExtraPrice * Max ( 0 ; cCountWords - MinimumWords )

Edited July 12, 200718 yr by Guest
Misunderstood the nature of the relationship in John's file

Let (

diff = cTotalWords - Ad_MaxWords_TierOne;

Case ( cTotalWords; Ad_Price_TierOne + diff * Advertising_SJ::Ad_Price_TierTwo )

)

The problem with this calculation is if cTotalWords is under the TeirOne max you could actually end up with a negative price which is definitely not what you want. I would change the function to look more like comments so:

Let (

overage = cTotalWords - Ad_MaxWords_TierOne;

Ad_Price_TierOne + if( overage > 0 ; overage * Advertising_SJ::Ad_Price_TierTwo )

)

you can add an if condition in there to determine if the words are positive first too.... depending on how nit picky you want to get.

As far as which solution is better, either one is fine. I definitely like the idea of having some type of preference table because then non-"[Full Access]" users can edit the data. I don't think hard coding in the values is the way to go.

I dont see what you mean. If the difference comes out negative, that would also mean that that cTotalWords < Ad_MaxWords_TierOne, which in turn would make Advertising_SJ::Ad_Price_ TierTwo empty. Regardless of the sign of the diff, when multipled by Advertising_SJ::Ad_Price_ TierTwo, it would turn to zero and therefore default to:

Ad_Price_TierOne + 0, which in this case would equal $40.00