Max function

[The Big Bear]

Hi all

I have convert an Excel speard sheet with 1500 records into FM Dev. 6. I need to find the lastest Serial Number in the db. I made a self joining relationship called Max_serial using the serial number field for the relationship and define a calculation field called Max_serial and used the relationship named Max_serial and chose serial number for the field. I installed this Max_field in the header section of a view layout.

Example:

Max[Max_serial:: Serial number]

This only shows the serial number that the pointer is set to at that time and does not show the lastest serial. If you move the pointer to another record in the view layout, the field max_serial show that serial number.

The field for Serial number is a terxt field as the serial number contain letters and numbers.

This looks so simle yet so frustrating.

What am i doing wrong.

Thanks in advance

Lionel

Edited September 28, 2005 by Guest

[Slobey]

Welcome

[BrentHedden]

Good Day!

[-Queue-]

Create a calculation number field equal to 1. Then change your relationship to match the calculation field to itself. This way, all records in the file will be related to each other, instead of each serial only being related to itself.

Max( ) is often a very slow operator. You may find it faster to sort the relationship by serial number descending (so that the largest one is 'first') and then put the related serial field on your layout (it will always display the first related field according to the sort order; so the largest serial will show on every record) or reference it in any calculations you require.

[The Big Bear]

Thanks Quene

I have tried the sort decsending but some of the serial number fields are blanks and some have different types of numbers. They change the number for the serial numbers about two years ago for consistence but I have to live with the old number.

Thanks again for the information

[-Queue-]

How about a calculation field that only returns a value if the creation date on a record falls after the time the serial format was changed? Such as,

Case( dateCreation >= Date( 1, 1, 2004 ), serial )

You could then sort the relationship by this field.

Why would any of them be blank though? Is it a manually-entered serial (which is a big no-no) ?

[The Big Bear]

Queue

Yes, So far is has been a manual enter Serial number because of the serial changes as the year changes.

Example: Serial number for the year 2004 would be

60004001

60004002

Serial number for the year 2005 would be

60005001

60005002

Serial number for year 2006 would be

60006001

60006002

that is why the serial number is manual and i would like to known the last number use so as not to repeat it. I will write a script to check for duplicates but knowning the last number used would give us another check point, I known that this is not the best way but that is not my decision about the way serial numbers are chosen.

I agree auto serial numbers would be the best way.

thanks

Lionel

[-Queue-]

You can use a real serial field and keep the pseudo-serial for visual display only (i.e., not for relationship keys). The pseudo-serial can be an auto-enter field also. This is not too difficult to implement, if we can make rules on how it should look.

What does the initial 6 mean? What is the purpose of the the two zeroes between the 6 and the two digit year? If you have more than 999 records in one year, what should happen to the year digits?

[The Big Bear]

Quene

The serial represent our location in the company and the year the equipment was built and the last part is the number of units built.

Example

6000 is our location number

5 would stands for the year 2005

001 would be the number of units built.

We will never built over 999 unit in one year.

Changing some of the old serial number and making notes of why the serial number were changed, We have been able to auto-enter the serial numbers now upon your suggestion that we do auot-enter the serial numbers. Ever January, someone will have to go and reset the auto-enter serial number but that is no big deal.

Now we can sort the db by descending and know the lastest serial number used.

Thanks for the Hepl Queue

Lionel

[Salesman0Gil]

I know I'm a little late, but I'm curious Queue if you think this would work.

Make a calc field;

GetAsNumber (Right ( serial number ;3 ))

Then either sort, use that number to start the auto serial value, or a summary value set to most?

[-Queue-]

Lionel, see attached for a solution that does not require user-intervention when the year changes.

Gil, that would probably work, but only for a found set that includes records from a single year.

BigBear.zip