Auto-enter doesn't work consistently on import

[iNik]

I have a simple database which I use to import a job log from our print server, and then assign charges for prints based on this import.

In order to do this, I've set up some auto-enter fields in the table that the job log goes into that do some calculations to generate prices and determine if the job is a "free rider" that shouldn't be billed. I also have some auto-enters that create a proper date field (it has a lengthy date & time stamp field in the log, which is useless for sorting) and another that adds an ImportID index so that we can run reports on just the latest import, or on a historical import.

The problem is that of all of these auto-enters, the only ones which succeed are the last two. The price and "free rider" calculations fail.

I did some additional testing, and it appears that any calculation I auto-enter which relies on a relationship to another table will fail. (The ImportID is based on a global field -- no relation there, and the date is based on the same table.)

Here's the price calculation so you can see what it's trying to do.

 ( Job Log::Total Number Of Color Pages Printed * Click Charges::Color Rate ) + 

( Job Log::_Total_Number_Of_Black_And_White_Pages_Printed * Click Charges::K Rate ) + 

( Job Log::Number Of Pages Printed * Lookup ( 

Paper Prices::Price ; Paper Prices::Default Paper Price ) ) 

Both of these calculations work just fine if I make any modifications to the record and thus trigger the auto-enter. I've tried setting auto-enter to replace values or not and to always evaluate the calculation or not. It doesn't seem to make a difference.

Is there some way to make this happen, or will I just have to script these calculations as "set field" steps on a loop that walks through the import?

(And yes, I could make them calculation fields, but since they aren't stored, it makes report generation extremely onerous. I'd rather the DB did more work up front to make future reports simpler.)

[-Queue-]

Auto-enter calculations are dependent on field creation order. Make sure your calcs don't reference fields that fall below them in creation order.

Any chance you could post your file for review?

[iNik]

Hmm... That shouldn't be the case here.

The file's attached as well as a sample log. Just open the DB up and click the "import job log" button (or call it from the script menu) and choose the attached job log file. It'll do the rest.

Job_Logger_and_Log.zip

[-Queue-]

The lookup fails because there is an additional space at the beginning of Media Size.

[iNik]

Good catch. That log file isn't exactly clean, but it's what we've got to work with...

However, even with a judicious application of trim() functions, it still didn't work right. What I ultimately did was attach Lookup() functions around each of the click charge field lookups, and that forced them to evaluate. That, plus the Trim()s made everything work just fine.

Thanks a TON for your help, Queue!

[-Queue-]

Did you try making Media Size an auto-enter calculation (with 'do not replace existing value' deselected) of Trim(Media Size)? That should make the Price calc work as is.