field rule: lookup error

[hov333]

Hi all,

I'm trying to create a lookup for my Serial Number field. This worked fine in the past but for some reason I'm having an issue with it now.

when you type in a serial number that has a history, it is supposed to lookup the past records "repair date" field and fill that into the current record's "previous service date" field.

These are both 'date' fields, 8 characters in length.

After you fill out the form/browse with the serial number, I get an error data value cannot fit into a numeric field of size. I know this has something to do with field structure/length but both my fields are date fields so I'm stuck.

Then from this point on, you can't even exit the form/browse or abort the entry because it says "enter must begin first" its impossible to close A5 unless you end task in the task manager.

[Stan Mathews]

Re: field rule: lookup error

Golly, a sample of the table(s) would make things easier to diagnose.

[hov333]

Re: field rule: lookup error

I'd love to Stan, I actually tried to upload a zipped "backup" copy of my database but it exceeded the max filesize by a few MB's. Any way to get around this? How can I just upload 1 or 2 tables?

[Stan Mathews]

Re: field rule: lookup error

Hilight a table name in the control panel, right click, choose Zip...

will do one table at a time. If you do the same for a set it will include all tables in the set and the set definition.

[hov333]

Re: field rule: lookup error

here we go I think i got it, its just 1 table, but that's the one I'm having trouble with. Specifically the "serial number" look-up.

unfortunately, I wasn't able to include the dictionary files which include the field rules, my zip file would exceed the file-size limit.

see if you could just add the field rule as i mentioned.

linked by = serial_number
source field =repair_date
fill field = previous_service_date

then try to create a record using the serial number that's already in a previous record (555) and see if the problem comes up for you

[Stan Mathews]

Re: field rule: lookup error

I see now you mean you are lookinig up in the current table. Will examine.

I wasn't able to include the dictionary files which include the field rules, my zip file would exceed the file-size limit

The table attached is only 2 kb. If the zipped support files exceed the 5 meg upload limit you seriously need to compact your database.

[Stan Mathews]

Re: field rule: lookup error

No errors here when creating that field rule. The error must be triggered by some other field rule for the table which fires after this lookup.

You can email me the table and support files at [email protected] if you like.

[hov333]

Re: field rule: lookup error

ok I just compacted the table and that resulted in a much smaller file.

I don't see how this could be a good thing though in terms of the record numbers all being reset essentially because its packing the deleted records. Each record is linked to a record number, wouldn't compacting regularly completely move around all the record numbers?

Anyway, its uploaded here with the dictionary file as well so you can see the field rules.

Just make a new record and use 5555 for the S/N field, you should see the same problem I'm having.

[Stan Mathews]

Re: field rule: lookup error

Since many of your fields in this table have field rule lookups to other tables, those field rules fail when those other tables are missing. Impossible to test without them. Any attempt at creating a new record reveals Cannot open table Parts to post to.

I'll have to have the whole database.

Each record is linked to a record number

Record number is highly not recommended as a linking field for the reason that records will be renumbered by packing the table(s).

You need to link your tables on a foreign key.

[hov333]

Re: field rule: lookup error

ok Stan, here is the entire database.

thanks so much for the help so far...

as far as the packing/compacting goes, in my application, I actually cannot delete records since each unit has a record. So i can imagine in a few years I should have say over 10,000 records, which would result in a much bigger filesize, I don't see why would this be abnormal? Each unit the company services is record number/work number. Not the only way it is linked but the easiest. This would all get damaged if I was to compact the database...

[hov333]

Re: field rule: lookup error

I was using lookupd() function for my previous_service_date fields before but was getting the reported problem, thought doing it through field rules would avoid the problem but still no. If there's a better way to lookup what I want, please let me know.

I just need it to pull up certain field values from previous record history if the S/N is found in the database.

i.e.
previous_service_date
previous_service_date2
previous_complaint
previous_action

[Stan Mathews]

Re: field rule: lookup error

The problem is not the lookup of the serial number. The problem is some value that is calculated from the looked up date, I believe. Still searching.

[hov333]

Re: field rule: lookup error

thanks so much stan! i really appreciate it

[Stan Mathews]

Re: field rule: lookup error

It is the days_out calculated field. If you leave the date_in blank you wind up with the calculation

{} - {12/12/2010}

which yields -2455543

which won't fit in a numeric field of size (4).

You have some other field rule issues. You have two places where you have lookups and defaults specified. That is self contradictory. (Status in the main table and bill_to_shipping_address in the customer table if I remember correctly.)

In the customer table the recipient name field defaults to

BUYER_NAME+BUYER_LASTNAME

which is invalid as those field don't exist.

[hov333]

Re: field rule: lookup error

ok, I will look into these issues, thanks a million for finding these problems.

I know that my day's out expression is:
IF(PREVIOUS_SERVICE_DATE={},0,DATE_IN-PREVIOUS_SERVICE_DATE)

So wouldn't that mean that if it is blank, then it would result in 0.

Or at least that's the result when I evaluate the expression for record 1. It was showing the day's out normally when there is a previous_service_date as well.