Need help with a calculation

[carlew]

Hello,

I need help with a calculation.

 

I am a volunteer working on a database for an organization offering meals on wheels.

They have decided to sell prepaid meal cards so the volunteers delivering the meals , need not handle money.

Each card is valued at $ 50.00 ( 10 X 5.00 meal)

 

What I need is a calculation which will inform me the service coordinator that the value of  a card has expired based on the meals purchased.

 

Then If I have a conditional field based on amount left on card that shows '' need renewal'' then I could have a warning shown that this person needs to renew his card.

 

For example:: 1 Card purchased Jan 1 2013 for Mr X   value $50.00

  Mr X purchases 2 meals Jan 6       value now $40.00

  Mr X purchases 2 meals Jan 15       value now $30.00

  Mr X purchases 6 meals Jan 20       Value Now $0.00

 

Display Dialog     '' Mr X  needs new card''

 

Thank you very much in advance,,

 

 

 

 

[RodM]

make it simple,

 

I have 3 fields... basically for  test.. i always make mistakes..  but with the help of always setting up a debug step situation,

  watching whats happening...  testing watching your vars, there i learned a lot whats happening.. if that makes since

I am sure some can put it in 1 line too. 

 

field1     have the balance of your card...       Xbalance   =   Xbalance  -  newPurchases     belongs to Joe..

 

filed2   variable to work with doing some figuring    ie,   $warning  = $10

 

field3    be a calc field    Inside  in set up a

Case  (  Xbalance =< $warning;  "Say something "Warning --Need to add money"   )  ; "")       ///else say nothing

 

field 3  only show if $10 or less

 

hope that helps

[comment]

Question: when Mr. X purchases 2 meals - does he charge the purchase to a specific card? Or is it just a matter of maintaining a balance, i.e. purchasing a credit then charging meals against it?

[RodM]

ya...i added an extra case  )     bracket.. like i said.. always check you code, dit da dit

[carlew]

Comment :   He purchases a credit and charges meals against it.

 

 

 RodM:  Thank you; I will try this today.

[carlew]

OK  I think I follow but how do I calculate NEW purchases

 

Amount on card - amount purchased       based on dates?

 

Date card purchased            $ card value

Date meal purchased            then (quantity x price of meal)

 

I tried an If statement  if date of card purchase is > = date of meal purchase

then balance = card value - meal price extended                                                   Did not work !

 

Thanks

[comment]

Comment :   He purchases a credit and charges meals against it.

 

This is more of a relationship issue than a calculation one. If you have a table of Customers and a related table of Transactions, you can simply sum up the transaction amounts at the customer level. You could also replace the Transactions table with separate tables for Cards and Meals = although really, you are talking about managing an account where a card is credit and a meal is debit.

 

 

You do need to consider the number of transactions over time, since going back to the beginning of time to calculate the present balance may eventually turn out to be too slow.

[RodM]

If it does not work..

do you use Debug? and Watch

 

Set debug on, execute your code,

step through each line of code

 

In step state, use Data watcher tab paste code in there playing with what doesn't work etc.

Along with watching your vars, fields etc react