Perform find in other db and return value +1?

[snackcakes101]

In File 1 there is a two-position alpha code indicating a client code. File 2 is

a job number log, which has one five-position alpha-numeric field called job number that consists of a 2-digit alpha code (client code) and a 3-digit numeric code.

>

When assigning a new record in File 1, I need the system to

pause to manually enter the client code, open the Job Number Log (File 2),

search the 2-digit alpha code for the correct client, go to the last job

number for that client and return the next sequential job number. FM then

needs to enter that next job number in the appropriate field in File 1.

How do I write a script to perform a find in another database using a field that has been entered by the user, then have the system find the last record and return a value plus 1?

[Fitch]

You don't need a script to give you the number, you can do it with a calculated field:

Max (client::job number) + 1

[snackcakes101]

Thanks for your quick reply... however I am stuck WAY before I get to that point. I can't seem to get the 2nd database (job number log) to perform the find based on the alpha client code I entered in the 1st database. Since it will not be performing the same find every time (different client each new record), I don't know how to set it to read the client code and search on that.

Do I need to take the script I write in DB #1 and then point to an external script in DB #2, which would perform the find and go to the last record? Then resume DB #1 script for the max calculation? I feel like I have tried EVERYTHING, but I think I'm missing something obvious. I'm fairly new at this.

Thank you so much for any insight you can offer.

[Fitch]

My point is, you don't need to search for the client in the 2nd database! You have a relationship by client id from the 1st file to the 2nd file, right? If not, then make one. Now in the 1st file, make the calculation like so:

Max (client::job number) + 1

This is saying "look at all the job numbers (in the related 2nd file) for the current client, find the biggest number and add one."

You're really missing out if you don't get a grip on relationships. If you insist on doing it with a script, then yes, do it as you described. Copy the client id, perform external script that enters find, pastes id, performs find, goes to last record, copy number. Then back in file 1, paste number, then set field to number + 1.