How do I get MAX of only related records?

[SoCalMacDude]

FM7, 2 tables, Clients and Jobs.

When I create a new job for an existing client, I want to increment the Job Number for that client's jobs only. I want Client # 001-Job # 001, 2, 3, etc., then Client # 002-Job# 001, 2, 3, etc.

So I need to find Max value for ONLY the related records, in order to add 1 to it. Script triggered from Clients layout will create the related (by client#) record, and assign the next highest job # FOR THAT CLIENT.

How would YOU do it?

Thanks!

[RalphL]

Assumig you have a TO "Jobs" where Client # = Client #, then Max( Jobs::Job Number) will give you the maximun of the Job Number for that client. This may not be what you want to use as the next Job Number. You may have to format it with a calculation.

I don't like this method of asigning job numbers. I use an auto-entered serial number.