Complex DB Solution

[stanmcman]

While I'm not a noob, my skills are somewhat limited. I can usually find a way to make fmp do what I want, but sometimes it's an ugly hack job.

In this case, what I have is "functional", but I know there's got to be a MUCH easier way to do this. Here's my scenario:

The purpose of this db solution is to keep track of all the miles traveled per state, and fuel purchased per state. This info is tracked per trip, per truck, per customer. Each Customer can have several trucks. Each truck can have several trips. I use the Calc Sheet DB to provide a snapshot overview of total miles per state and total fuel per state, along with grand totals per a set of trips for a specific truck.

Databases:

- Calc Sheet (the overview)

- Trip Sheet (enter Miles/Fuel Line Items)

- States (List of States)

- - Contains calc fields:

- - - cQty Miles=Sum(Miles Line Items::Miles Qty

- - - cQty Fuel=Sum(Fuel Line Items::Fuel Qty

- Miles Line Items

- Fuel Line Items

The problem comes from the fact that in the States db, each state is a record. In the Calc Sheet db, each state is a field. I currently have to run a HUGE loop script which looks something like this:

If[state::State ID = "AK"]

   Set Field[Calc Sheet::"AK Miles";State::cQty Miles]

   Set Field[Calc Sheet::"AK Fuel";State::cQty Fuel]

Else

If[state::State ID = "AR"]

   Set Field[Calc Sheet::"AR Miles";State::cQty Miles]

   Set Field[Calc Sheet::"AR Fuel";State::cQty Fuel]

Else...etc, etc, etc for all 48 states

Is there an easier way? How can I convert Summaries based on line items, where states are a record, to the Calc Sheet, where states are field?

Thanks in advance

[elo]

I'm not 100% sure I understand your database, but the simpler way to track this which would also make "rollups" easier is more many-to-many relationships using an intermediate table.

Here's what I would do for tables and part of the relationship diagram:

tCustomers -> tTrucks -> tTrips -> tMileageItem

-> tFuelItem

Somewhere you need a lookup table, lStates, each tMileageItem and each tFuelItem would have a stateID from lStates.

You should then be able to use summary fields with breaks at changes in stateID for rolling up the summaries.

But again, not 100% sure what your DB structure or the problem you are trying to solve.

[stanmcman]

Thanks elo,

I know I'm not being very clear. I've attached a shell of db solution outlining what I'm doing (and trying to do more efficiently). It contains 7 dbs and a _READ ME file. Open the Customers DB (I included the Read Me text in teh cust db as well).

If you goto either the Miles Line Items db or Fuel Line Items db, and goto the Script: "Total Batch", you will see my scripting loop (as explained in the _READ ME.txt file).

Any thoughts are appreciated. If you need further explanations, PLEASE post your questions here and I will do my best to clarify.

Thanks again,

Stan

Customers.zip

[elo]

Just saw this today (8/22), anyhow, some of the files are corrupt (Line Items.fp7 among them).

I cannot make sense of this entirely, but am trying to create something simplified in FM Pro 9 to send back to you.

[elo]

Ok, I've redone this FM7 style with a single file and attach the relationship diagram.

CalcSheet in the original solution does NOT record Truck ID's this is a bug IMHO.

A better name for Calcsheet would be mCustomersTrips (using my nomenclature) so I've moved the truck ID over to the Trip record since it didn't really make sense on the calcsheet, though it could be there.

For trips, I've eliminated the overly complex 2 types of lineitems (mileage, fuel) and replaced with one.

Then I'll try to send the calculations and files that avoid 50 relationships (1 per state) to calcsheet.

[elo]

Ok, I'm gonna stop at this point b/c with the corrupt files I just cannot be sure that I understand your DB.

I'm not sure if each "CalcSheet" can have multiple trips or not, I decided NOT in the way I laid things out, but maybe they can.

That said, if you use a more FM7 like structure it will be easier for other to help you AND you should be able to show SUMMARY fields on a suitable layout that subtotal the fields you want across relationships.

Sample.fp7.zip