Need help with First() function

[Rich Hartnett]

I have police officers filling out a daily activity log, which starts off with the patrol vehicle's starting and ending mileage reading. (The total miles are caculated.) Sometimes they use the patrol vehicle on their days off for non-patrol activities, such as going to court, and on those days they do not record their mileage.

Each month I produce a "Monthly Activity Report,", and two of the fields are "Starting Mileage" and "Ending Mileage." Normally I would get the information from the first starting mileage entered for the month by using the first(startingmileage) expression, and last(endingmileage) for the last day of the month, but I'm running into problems where the officer used the vehicle on the first or last day of the month, and for whatever reason, does not have a starting or ending mileage listed.

In those cases I'm (naturally) getting a resulting Starting (or Ending) Mileage of "0".

So my question is, how can I write the expression to assure me that I am getting the first mileage of the month that is NOT "0", and the last mileage of the month that is NOT "0." (or >0 or something like that) ?

[Stan Mathews]

Re: Need help with First() function

Report writer functions such as first() are not filterable. Since you can't really have a "Starting Mileage" field, let's call it startm.

You might be able to create a calculated field

realfirst = if(statrm >0,startm,9999999999999)

Then create another calculated field like your current first() but use minimum().

[G Gabriel]

Re: Need help with First() function

sounds like you need:
dbmin() and dbmax() ... where the filter in each will contain the month in question and startingmilge <>0

[Rich Hartnett]

Re: Need help with First() function

Stan thanks for the reply, but I really don't understand the answer. (I'm sure it's just me.)

My actual field is called "StartingMileage." If I create a calculated field based on your formula, it looks like this:

StartingMileage = if(startm >0,startm,9999999999999)

but the resulting answer is logical. (T/F)

So am I now creating a minimum(startingmileage) calculated field based on the first true "startm" entry?

And if so, how?
-----------------------------------------------------------------------

And Gabe, I tried to use dbmin(), but was confused on the parameters.
My table name is: "admin_ncpd"
The only options I have for c_tagname are: Date & Record_No
For key value, I assume it would be <>0 (or maybe >0)
But for the "c_field" name, of course it is looking for a character field, and my StartMileage field is numeric.

Can you explain how to propertly set up the dbmin() function with these parameters?

[G Gabriel]

Re: Need help with First() function

I think your best bet is a UDF given the fact that dbmin() and dbmax() require an index and given the fact that you are searching within a certain date, i.e. the index has to be updated before running the calculation, you might as well use a UDF.
Here is a pseudo-code:

Function millage as N (search_month as n, vehicle as C)
t=table.open(...)
t.query_create("t","put here filter to check for search_month","put here order by millage")
t.fetch_first()
if t.millage=0
t.fetch_next()
start_mil=t.millage
end if
t.fetch_last()
if t.millage=0
t.fetch_prev()
end_mil=t.millage
end if
millage=end_mil-start_mil
End Function

The filter would be something like:
"month(your_date_field=var->search_month.vehicle_field=var->vehicle"

Edit:
posted before reading your latest post.. rather than addressing any issues with dbmin() and dbmax(), try the UDF.

[Stan Mathews]

Re: Need help with First() function

I said create a new calculated field.

realfirst = if(StartingMileage >0,StartingMileage,9999999999999)

For any record with a starting mileage of zero, the expression returns 9999999999999. Since it is unlikely to have a vehicle with 9999999999999 miles on the odometer, when you use the minimum() on the calculated field this record will be excluded. The minimum() function should return the lowest recorded mileage > 0.

[dik_coleman]

Re: Need help with First() function

This may be a little off the subject, but....

you might want to limit all field and variable names to 10 characters or less.

If A5 crashes, and you have more than one field name that starts with the
same 10 characters, they will all be truncated to the first 10 characters.

That means you will have multiple fields with the same name. Every place that
you use that field name (scripts, functions, etc) will cause errors. And some
will not be evident until bad data starts showing up.

Doesn't happen very often, but once will be more than enuff. Everyone seems
to go thru the long, explicit field/variable name phase, cuz it's easy to read.

D

[Rich Hartnett]

Re: Need help with First() function

Thanks Stan. I actually WAS creating a new calculated field, but not correctly. After reading your last post it made sense to me, and it works just as you described.

Thanks again.

_______________________________________________

And dik_coleman I hear you loud and clear!

I should have known better than to have long field names. I actually did have a database crash several years ago, and I did experience the problem with truncated field names. What a pain that was.

I'll have to be more creative with my field names in the future.

Thanks everyone.

[Stan Mathews]

Re: Need help with First() function

There is of course the possibility that the minimum recorded mileage is also an error like the non recorded mileage of 0. The minimum function does not look for the first recorded mileage > 0, just the lowest recorded mileage. If a user was to record 1 as the mileage, that's what you'll get.

[G Gabriel]

Re: Need help with First() function

Not really sure why are you concerning yourself with the starting millage of the month? Isn't the same as the ending millage of the previous month? and the ending millage is simply maximum() for that month?

[Rich Hartnett]

Re: Need help with First() function

Well Gabe you are right in theory, but there are times when an officer will have to change cars for one reason or another, and as a result, the starting mileage for one month, may not necessarily be the same as the ending mileage of the previous month, due to the officer driving a different patrol car.

If it wasn't for that though, you're right, I could have just used the previous month's ending mileage.

[G Gabriel]

Re: Need help with First() function

Not to beat a dead horse, but I don't see why you can't?
And what happens if they change cars in the middle of the month? or a car breaks down?