Very difficult script needed

[MnR]

Hi there,

I need a script which can take into account many factors,

Firstly the script is going to calculate a bill total, but its not just adding up totals it needs to firstly check the difference between two values, WeightNeeded and WeightGot. Then if the difference in weight is (weights recorded in KG) 1kg either way more than the customer needed a discount is applied. Then this repeated 5 times as the table can hold 5 orders. But I also need to make it so that it doesn't count orders which have the field Cancel? set to Yes. But it needs some kind of loop to do all 5.

[RalphL]

This sounds more like a calculation than a script.

Case ( Abs ( WeightNeeded - WeightGot ) >= 1; Discount; 0 )

I hope that you are using a related line item table. It sounds like you are limiting youself to 5 items (orders) per bill. In a properly constructed database you would print your bill from the line item table. A simple find will omit canceled orders.

[MnR]

Thanks but I need to show the cancelled orders as the spec for the database says so. So I would use the idea you presented but that would lose me marks!

[RalphL]

OK, make your calculation for the price of the order = 0 if the order is canceled.

[MnR]

Doesn't work, nothing entered I have been trying to create a script and this is what i have come up with:

Case ( WeightNeeded - WeightGot >=1 ; WeightGot*4.78+2.5*.95 ; WeightNeeded - WeightGot <=1 ; WeightGot*4.78+2.5 ; WeightGot*4.78+2.5 )

It doesn't work but i know what i want, test1 > than 1=WeightGot*4.78+2.5*.95 test2<1 = WeightGot*4.78+2.5

I just need to get this into a script form

Pseudo code this is would look like this

If (weightneeded-weightgot is greater than 1) then

weightgot * 4.78(price per kg) + 2.50 (charge) * 0.95 (5% discount)

else (weightneeded-weightgot is less than 1) then

weightgot * 4.78(price per kg) + 2.50 (charge)

but the value needs to be placed in the field!

[stanley]

MnR:

Marks? Are you in a FileMaker class, in school? Interesting.

Anyway, this is the schtick:

Set up a global number field called "cancelled" or something like that.

Set up a number "total" field to do your total for the order.

Script:

Set Field [cancelled; 0]

Go To Record [first record]

Loop

If Cancel = "cancel" // or whatever your cancel field is, that you refer to

Set Field [cancelled; cancelled +1]

Else

If [Abs ( WeightNeeded - WeightGot ) >= 1]

// here you'd apply your discount, however that works

Set Field [Total; Total + Discount number]

Else

Set Field [Total; Total + Non-discount number]

Go To Record [next, exit after last]

End Loop

Hope that's right and it makes sense; I'm still on my first cup of tea.

-Stanley

[mr_vodka]

I would suggest that you structure your Database the way Ralph has suggested. However, in case you were curious, you could have written a script like this.

Go to Record [First]

Set Field gTotal=0

Loop

   If Cancel <> "Yes"

      If [ Abs ( WeightNeeded - WeightGot ) >= 1 ]

           Set Field gTotal = gTotal + DiscountedPrice

      Else

           Set Field gTotal = gTotal + Price

      End If

   End If

   Go to Record [ Next, Exit after last ]

End Loop

You can use a calculation field as such if you want if you do not want to use a script.

cPriceUsed =

If ( Cancel = "Yes"; 0; Case ( Abs ( WeightNeeded - WeightGot ) >= 1; DiscountedPrice; Price ))

Then use a summary field to sum up cPriceUsed.

[stanley]

MnR:

I didn't see your last post, as we must have been typing at the same time; hope I've got it right anyway. Also, the global field "cancelled" is there to count the number cancelled, which I thought you said you needed to show; you can put that field somewhere to show the number of cancellations.

-Stanley

[MnR]

hmm no FileMaker classes just going against the Access grain and beating the whole class with FileMaker I was just using pseudo(ish) code to try and get accross my needs. I don't really want to use a global field as that would mean reworking the whole database, there are only two values anyway (Yes or No).

would i need to loop it?

Sorry but i think it would be easier to use a calculated field rather than a script so if you (or someone else) could help make it for a calculated field.

If (weightneeded-weightgot is greater than 1) then

weightgot * 4.78(price per kg) + 2.50 (charge) * 0.95 (5% discount)

else (weightneeded-weightgot is less than 1) then

weightgot * 4.78(price per kg) + 2.50 (charge)

ps the brackets are comments which i should remove!

If (weightneeded-weightgot is greater than 1) then

weightgot * 4.78+ 2.50*0.95

else (weightneeded-weightgot is less than 1) then

weightgot* 4.78+ 2.50

[stanley]

MnR:

Adding a global field wouldn't require you to change anything else in your database. However, if all you want is the calculated field, use the Case() that Ralph showed earlier, but with your math in the place of his "Discount" and "0".

-Stanley

[MnR]

Case ( Abs ( WeightNeeded - WeightGot ) >= 1; WeightGot*4.78+2.5*.95; WeightGot*4.78+2.5 )

this didn't work but this did:

If ( Cancel? = "Yes"; 0; Case ( Abs ( WeightNeeded - WeightGot ) >= 1; WeightGot*4.78+2.5*.95; WeightGot*4.78+2.5 ))

thanks to all! Just need to make the summary field and I am done!

[MnR]

Noticed that if the difference is in the - numbers it doesn't applie a discount! it should if it is either 1kg out either way and the 5% discount is per 1kg out so ifs it 2kg out then it still does 5% not 10%, hey i said this was a brain scratching calculation!

[mr_vodka]

You should create calculation fields called Discounted Price and Price.

Price: WeightGot*4.78+2.5

DiscountedPrice: Let ( x=Abs ( WeightNeeded - WeightGot ) ; Price - (x * .05 * Price) )

cPriceUsed =

If ( Cancel = "Yes"; 0; Case ( Abs ( WeightNeeded - WeightGot ) >= 1; DiscountedPrice; Price ))

-John

[MnR]

wow it works! now just to tidy up the job and i will tell you if i find any more problems!

thanks again to this great forum, as each day passes my class are slowly moving away from Access and starting to use FileMaker, hopefully by the end of the year they all will be!

[RalphL]

Try this calculation:

Case ( Cancel = "Yes" ; 0 ;

Abs ( WeightNeeded - WeightGot ) >= 1; (WeightGot * 4.78+2.5) * (1 - .05 * Floor ( Abs ( WeightNeeded - WeightGot )));

WeightGot * 4.78+2.5 )

Do you have a maximun discount?

[MnR]

the calculation works as far as i can see and i guess the maxium discount would be 25% as the minium and maxium turkey weight is 5 and 10 so 5 difference meaning maxium can be 25% don't worry about the calculation going over 100% discount

[MnR]

Sorry RalphL i noticed that Mr_vodka's calc didn't applie to all the fields and yours does so i replaced his with yours and it works even better now!