Automatic Discount entry on Quotation

[Keith Shelton]

FM4, Familiar with very simple scripts but not calculation scripts etc.

Have a routine quotation file using repeating fields containing usual Product Code which calls in Description and Price (from Product File).

All works OK but what we wish to do is have a discount appear automatically instead of having to look it up and enter manually.

For example, if we enter a Product Code and then a Quantity of 100 which attracts a 10% discount, is there any way of making the discount come in automatically with perhaps 25% for 250 and 50% for 1000 etc.

We would still need the option of changing the discount figure manually if necessary, i.e. it would probably be no good if the discount field only allowed automatic insertion.

Thanks in advance.

[Keith Shelton]

Sorry, I should have said that the Product Code field is related to the Product File and when a code is entered and tab hit, it calls up the Product Description and Price. There is no script involved but I thought that a script would be necessary (or am I in the wrong Forum section???).

Thanks for your efforts.

[Kurt Knippel]

quote:

---

Originally posted by Keith Shelton:

Sorry, I should have said that the Product Code field is related to the Product File and when a code is entered and tab hit, it calls up the Product Description and Price. There is no script involved but I thought that a script would be necessary (or am I in the wrong Forum section???).

Thanks for your efforts.

---

Set your discount field to be an autoentered calculation, making sure to uncheck the "Evaluate even if all referenced fields are empty" option. Use the calculation that i gave you above. This will remain uncalculated until you put in the Qty.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[Kurt Knippel]

quote:

---

Originally posted by Keith Shelton:

FM4, Familiar with very simple scripts but not calculation scripts etc.

Have a routine quotation file using repeating fields containing usual Product Code which calls in Description and Price (from Product File).

All works OK but what we wish to do is have a discount appear automatically instead of having to look it up and enter manually.

For example, if we enter a Product Code and then a Quantity of 100 which attracts a 10% discount, is there any way of making the discount come in automatically with perhaps 25% for 250 and 50% for 1000 etc.

We would still need the option of changing the discount figure manually if necessary, i.e. it would probably be no good if the discount field only allowed automatic insertion.

Thanks in advance.

---

I am assuming that you use some kind of scripted procedure to enter the pricing. Simply include a step in that procedure with the following calculation:

CASE ( Qty >= 100, .10, Qty >= 250, .25, Qty >= 500, .5)

Use this to set your discount field. This will be a normal number field and will still allow a user to edit it, but will now be filled in automatically.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[Keith Shelton]

That works fine but I have a problem. There are hundreds of products, divided into lots of groups, and each group attracts a different level of discount. For example, products type A might get 10% for 20 plus but products type B might only get 10% for 100 plus.

Somehow we need different discount scales to be attached to the relevant product groups. The one problem being that the discount can only be triggered when the Qty is entered in Quotation file.

Is there any way of doing this?

I did hit one problem which I couldn't understand when using CASE. If I applied this to the product code, it would only work if I entered numerals but the codes are alpha numeric. If I entered C1P which is a code, a message said it couldn't find the field (C1P). What am I doing wrong? My calc was CASE(Product Code =C1P; 25).

Thanks again for your efforts.

[LiveOak]

The problem with your Case statement is the way you specified text. It should be:

Case( ProductCode = "C1P"

Without the quotes FileMaker goes looking for a field called C1P.

As to your pricing, in your product file you can have a series of field:

Break Qty 1

Discount Percent 1

Break Qty 2

Discount Percent 2

Break Qty 3

Discount Percent 3

.

.

.

Break Qty N

Discount Percent N

Then perform a selection in your order file or whatever:

Discount Percent =

Case(

Qty < Prod::Break Qty 1, Prod: :Discount Percent 1,

Qty < Prod::Break Qty 2, Prod: :Discount Percent 2,

Qty < Prod::Break Qty 3, Prod: :Discount Percent 3,

.

.

.

Qty < Prod::Break Qty N, Prod: :Discount Percent N,

0)

"Prod" is the relationship with your product file.

If you are a real FM beginner, while not a really hard application, this will be a little difficult for you. You might want to get some training or help. Medical students don't expect to do brain surgery the first day of medical school after one day of studying!

-bd

[This message has been edited by captkurt (edited October 21, 2000).]

[Keith Shelton]

Many thanks for your help.

At a glance your instructions make sense to me and I shall work on it.

I take your point with regard to training but it is the usual problem of finding the right trainer and covering the right ground.

Just to give you a little background: I am self taught and have developed, with no assistance in my spare time over the last few years, a respectable small-company database system. The system uses portals, relationships and lookups etc. My main weakness at this level is definitely involved scripts and calculations etc. - I haven't needed them in the past but my current discount aims would make our quote creation a little more slick. In addition I do enjoy a little mental wrestling with these things.

Could you tell me if the book 'Scriptology' is likely to be the best publication to fill the gaps in my knowledge?

I have started using the Forum over the last month or two and I think it is great - as well as asking questions I have managed to answer a few simpler problems for other users.

In any event I shall try not to jumble anyone's brain through lack of training

Thanks again.

[LiveOak]

My comments on training or assistance still apply. If you have been using FM for some time and have not explored calculations and scripts, you've missed most of the power of FM. To make effective use scripts and calculations also requires some background in math and programming.

Scriptology is definitely one of the better references for FM technique.To fill in gaps in math or programming may require additional materials. -bd

[Keith Shelton]

Please do not think that I disagree with any of your comments - the more training and knowledge, the better.

Unfortunately, running a small business quite often means that you become a 'Jack of all trades' and don't have the time to deal with some peripheral aspects in the way that you would prefer.

I know only a little of programming but I am reasonably strong on maths and logic. Simple scripts and calculations for our quotes, for instance, were no problem.

Obviously it has taken me longer and I have had to work harder than if a good teacher had been readily available.

However, while nothing is perfect, I am not conscious that we have missed out seriously with the final result and I hope that I am not applying 'happy ignorance' by saying this. My confidence is based on my experience of various major proprietory software and, in particular, the Accounts Software which we use. This is definitely not as quick and friendly as when using the equivalent aspects on our FM database. Nevertheless I am always looking for ways of improving the latter and I shall purchase Scriptology.

Finally, your time spent and comments are really valued and greatly appreciated. Thank you very much.

Regards Keith

[LiveOak]

My pleasure, Keith. FM without scripting is like going to movies with your eyes closed. When you open them you'll see a whole new world! -bd