Advanced Problem. Need Help with Calculation.

[Papabjoe]

I believe this is a calculation problem.

The data looks like this;

Each record has three fields <State> <Miles> <Gallons>

We process fuel taxes for truckers and must create a report that totals the amount of miles traveled and gallons of fuel purchased in each state. We will enter Data that looks like;

<AZ> <151> <200>

<CA> <132> < >

<NV> <115> < >

<AZ> <100> < >

<NM> <200> <150>

etc...

I need a field that will total miles by state, and another field that will total the gallons purchased by state.

Any one have any suggestions?

[Fitch]

If this is for a report, the simple "FileMaker" way to do it is:

1. Create a summary field, Total of Miles

2. Create a summary field, Total of Gallons

3. Create a list layout, with a subsummary part when sorted by state

- you can delete the body part

4. Put your 3 fields on the subsummary part, sort and preview, all done

[Papabjoe]

That's not quite the solution I need. Once the total miles in each state are calculated, that value will be used in another equation. I have an FM file of just the equation itself that works just fine. My only problem is that I can't get FM to do the work of sorting and totaling by state and the populate the necesary fields in this equation.

[Fitch]

Again, if this is only going to be used on a report, you can use the GetSummary() function to do further calculations on summary fields. Or, you can use a relationship, so you'll have something like:

Sum(state::miles)

Now, whether you do this on an individual record, or using a global field to select a state, or from a separate file with one record for each state, depends on what you want to do. It would help if you'd provide more details.

[Papabjoe]

Let me give you the details in the best way that I can.

The report format is already determined by the jurisdicion we submit it to.

Our clients keep track of their miles traveled in each state as well as how many gallons of fuel they purchase in each state. This data is turned in to us on a handwritten form. The objective is to key enter this data and let FM format the end report.

The data comes to us in six fields of data

<Date> <State> <Route> <Loaded Miles> <Empty Miles> <Gallons Purchased>

The first step in completeing the report involves three fields

<Total Fleet Mileage> <Total Gallons Purchased> <Average MPG>

I have been successful in creating fields that will give me a grand total of Fleet Mileage as well as Total Gallons Purchased. The field for AMPG is simply a calculation of the first two. The AMPG is used throughout the rest of the report.

Now the rest of the report consists of columns and rows. The first column is where the individual state is listed. In the first row of the first column I would enter the State AZ. In the second columne first row I would enter that state's tax rate, In the third column I would enter the total miles traveled in that state. In the fourth column I would enter the total number of gallons purchased. The remaining columns are calculations of the values in the previous fields. This process is repeated for each state. In some cases the trucker will owe a state taxes and in others he will get a credit. At the bottom of the report, all of the credits and all of the taxes are added together and that becomes the truckers liability.

I hope that was clear enough as to what I am trying to do. If you have any other questions let me know.

Barry

[Ugo DI LUCA]

Hi,

First of all, as a trucker may travel in many state, and that each state has its specificity, I would involve a State File here, so that the fiscal information for each state would be a lookup from the State Code, which appears to be unique, so usable here in se.

As you submit this report to a juridiction, this means that this report would be a monthly or other pre-determined period, so you would need to store the Period as your first Sort key.

As you submit this report for any trucker you are in charge, you would obviously need to create a report by Trucker, but I'm sure this Trucker File already exists.

The report is based on some Fuel Purchase, but it seems needless here to create a Purchase file, so you'd input the <Date> <State> <Route> <Loaded Miles> <Empty Miles> and <Gallons Purchased> in a Portal from the Trucker File, related to a Join File where you'd store these inputs and use them for the current report.

In this Line Item, to get your Average MPG, rather than a Summary field, I'd involve a relationship here (or scripted calculation based on this relationship if you need to have it indexed).

IMO, the relationship would be Trucker_ID & Period ::Trucker_ID & Period and you'd get the Average with a MPG = Sum(your relationship::Milage) /Sum(your relationship::GallonsPurchase)

Then the subsummary report would be created sorted by Period, Trucker and State (if this later is what you need), and you'd use Relationships to get the Sum you need for tha Taxes dues or credits.

HTH