Auto-entry possible based on max value found in records of another table?

[RPath]

Is there any way for a field in a table to be setup for auto-entry based on the "max" value found in a group of records (instead of a repeating field) within another table? I'm trying to find the biggest number in a set of records for a field from Table 1, and then auto-entering that found number plus 1 into a field in Table 2 whenever a new record is created. The MAX function apparently won't work, since it only searches for values within a particular repeated field and not within an entire set of records (using a particular field). A search in forums on this site didn't turn up any info about this yet...

[mr_vodka]

Have an instance of Table 1 related by 'X' to Table 2. In table 2 have your auto enter calc be

Max (Table 1 by X::number) + 1

[RPath]

Mr Vodka -- thanks for the response, but it unfortunately doesn't work for me. Here's more of the background on the setup:

The field AssetNumber exists in two tables, AssetMaster and Desktops. A relationship exists for this field between the two tables. The AssetMaster table contains more records for AssetNumber than the Desktops table does, since AssetMaster lists asset numbers for many things besides desktops. When a person adds a new record to the Desktops table, I'd like the AssetNumber field to be auto-entered with a number that is the largest found in all records for AssetNumber from the AssetMaster table, plus 1. (This new AssetNumber is then created in a new record in the AssetMaster table as well.)

When I tried your example, it didn't like the "by X" addition, or "by" any field name. I also typed in the calculation without the 'by X' addition (which is what I originally tried), but that doesn't work either. The MAX function apparently only looks at one particular record, not all of the records for a field.

Any other ideas? Or is this just something that FileMaker can't do (at least easily)?

[mr_vodka]

Rpath, maybe you might have misunderstood me.

When I mean 'by X' I mean use a cartesian join. When you relate two tables together, there is an option for the 'X' operator symbol instead of the '=' sign. When you have this type of join, all records in Table1 are related to all records in Table2. You are then taking the Max value for that particular field of all the records.

[RPath]

Mr Vodka -- ...ahhh, I got it now, and it works like a charm! Thanks very much for clearing up my misunderstanding and offering the solution!