This might be a dumb question, but why not use a single Line Items table that includes both Miles and Fuel? Call it something like Fill Ups?
on exiting the state field in fuel line items you check for PatternCount(stateField) in ExecuteSQL("Select State From MilesLineItems where TripID = ?";"";"";FuelLineItems::fk_TripID) and revert record with a beep on 0.
because if he fills up 3 times in Texas you will have
048 TX 50
048 TX 50
048 TX 25
(TripID, state, gallons)
Not a dumb question. I had gone with that option, but because of how we do data entry, it wasn't as conducive an option. It is INCREDIBLY easier to enter all miles first and then enter any fuel. Miles come from a trip report turned in by drivers. Fuel comes from either a stack of fuel receipts or a fuel report. This is used to keep track of Quarterly Fuel Taxes for trucking companies. We are entering data for hundreds of trucks for dozens of companies.
I was having trouble trying to make it work with combined miles and fuel together, which is why I broke it out into two. It works extremely well. I just figured there would/could be a fairly simple/straight forward way to "compare" the fuel line items to the miles line items for a selected trip, and make sure that every state with fuel also had miles.
So you don't have a one-for-one correspondence between fuel and miles?
No, he'd have:
048 TX 50 310
048 TX 50 312
048 TX 25 155
(TripID, state, gallons, miles)
And ... so what? Missing why this is a problem.
you can't avoid having to sum the gallons but at least you can avoid having to sum the miles. (only 1 entry, 048 TX 777). Also, you can handle the miles line items to the maintenance dept who will change tires or brakes or whatever based upon miles done and handle the fuel line items to the accounting dept for different reasons. Less not needed info going around.
Hm. Okay, so the primary entity is the "trip". I have a similar solution, but it's intended for consumer use. It's very easy to write down the odometer and get a receipt at each fill-up. You put in the date, odometer, cost and gallons, and the solution calculates cost / gal, MPG, maintenance schedule, etc. In that case, you're really not concerned about a "trip", but I can see why you would be for a trucking company. So my paradigm is a little different.
Mike, If I understand your question correctly, then that is correct. While every state we travel in will have miles, they won't all have fuel. For instance, If I a trucker took a load from Grand Rapids, MI to Miami FL, it would look something like this:
Miles Line Items: (State, Miles)
Total = 1503
Fuel Line Items (State, Gallons)
Total = 227.2
Yes, in the scenario I'm laying out, the Trips is the primary. It is comparative to Invoices, Line Items, Products where:
- Trips = Invoices
- Line Items = Miles/Fuel Line Items
- States = Products
Yeah, took me a minute to catch up.
siplus has given you a good option using ExecuteSQL ( ). Assuming the state code is available in both Line Item tables (based on your description, I think it is), you could also do something like this:
not IsEmpty ( FilterValues ( List ( FuelLineItems::State ) ; List ( MilesLineItems::State )))
Use the same Script Trigger siplus recommends (OnRecordCommit) to check the calculation.
Well Mike, yes, the trip is what I intended as primary entity, too. During the trip we can have 2 different kinds of events:
1) Crossing an interstate border (which triggers an event in the Miles line items, registering the current mile reading in the current record as end value, creating a new record and setting it as start value), or
2) Fueling, which creates a new record in the Fuel line items, registering quantity and price paid.
If the purpose were to cross the US and give an average value of the $$ paid per gallon depending on the state, then a single line item table could probably be better. But as I understood the OP's problem, that's not the case.
I got it. Took me a bit, but I got it.
I will make a minor suggestion to Siplus' recommendation: you can do it with a single ESQL statement, instead of using PatterCount() combined with an ESQL call. Here's what I would suggest:
ExecuteSQL("Select COUNT ( * ) From MilesLineItems where TripID = ? AND StateID =? ";"";"";FuelLineItems::fk_TripID ; Statefield )
This returns how many MilesLineItems there are for a Trip in a given state.
Now for a large departure from Sipuls' solution.
What about some different UI instead? Not sure where you are starting from UI wise, but I will assume that you are on a 'Trip' record, probably a detail view, with a portal to 'Miles' and a portal to 'Fuel' tables. I would leave the portal to 'Fuel' on the layout but only as a non-editable list view. Then, what if you put an 'Add Fuel' popover button in the 'Miles' portal? This popover would then show a small window where you would enter in the Fuel information. You could save this information in temporary global fields, and then when they close the popover, have a script trigger that would save that temporary info into a new record in 'Fuel' table. You would grab the StateID from the 'Miles' record that was clicked on.