1 Reply Latest reply on Jun 20, 2011 11:58 AM by philmodjunk

# Making a  (simple ??)  calculation

### Title

Making a  (simple ??)  calculation

### Post

I have orders for tickets, I have only a finite number of seats available(capacity)on each bus (route) (there are many routes),

All the people are allocated a particular route according to the needs in a related table called Student

All I am trying to do is in my routes table to show how many seats are occupied and how many seats are spare on the date i look at the report. This is going to be determined on how many valid (in date) tickets are in circulation for  a particular route and how many seats that bus has)

Each individual route has it's own set capacity. With your help I have already worked a flag for whether a pass is valid on that day  (see the calculation below)

SO if Route 510 has a capacity of 75 seats and in my orders table  the field Students::RouteID has say, 55 that are  "Valid"  my calculation should show 20 spare.

Like wise on that day route 621 with a capacity of 50 seats and 30 valid passes would have 20 seats spare.

I've tried every which way to create something that works properly, sadly failed on each occasion I've been fiddling around for days now trying to be brave, to no avail..

Could some superhero help a little Dunce!

Route Field Name               Field Type                 Formula / Entry Option

OrderID                               Idexed

StudentID                            Indexed

Valid From                            Date

Expires                                Date

cToday                                 Calculation                If ( Get (CurrentDate )  ≥ ValidFrom and    Get (CurrentDate ) ≤ Expires ;                                                                           "Valid" ; "Invalid" )

Count of Orders                    Summary = Count of StudentID

Today Count                          Summary = Count of Today

Table “Route”

Route Field Name               Field Type     Formula / Entry Option

RouteID                               Text

RouteCapacity                      Number

Spare Seats                        Calculation (Number)

Table “Student”

Route Field Name               Field Type     Formula / Entry Option

StudentID                         Text            indexed

RouteID                           Text

(&& a lot more address fields etc!!)

• ###### 1. Re: Making a  (simple ??)  calculation

Your Today Count and count of Orders fields will always return the same counts. You need a different field to count or sum for this to work.

Try this field: If ( Get (CurrentDate )  ≥ ValidFrom and    Get (CurrentDate ) ≤ Expires ; "Valid" ; "" )

And define Today Count as the count of it. (Count counts the records where the specified field is not empty.)

If you pull up just the Orders records for one route at a time, this calculation should return the number of available seats:

Route::RouteCapacity - Today Count

If you pull up orders for multiple routs at one time and sort the records by Route, you'd need to use the get summary function.

Route::RouteCapacity - GetSummary ( Today Count ; Student::RouteID )

This asumes that you sort your Orders records by Student::RouteID to group them by Route.