trouble creatg custom auto-numbers numbers

[George Swisher]

need any help I can get.

Situation:

I have a main database that creates job orders and each job order is assigned a unique job number assoicated to a client code. I have created several source databases that are referenced in relationships with the main database. One of the source database manages my client list which allows me to assign a client code for each client and use that value as a reference value. An example would be, client name is George Swisher, client code is SWI. Always a three letter/number value.

Now to the records (job numbers) in the main database. Each record (job number) created in the main database is associated to client codes. So each record is unique such as, new record created for client SWI would have the first record for that client as SWI-3000 (Client: SWI, Year: 3 for 2003, and 000: for first job for client code); each record after for just this client needs to run in sequence as SWI-3001, SWI-3002, etc as new jobs are created. Right now I have to look up records for the client code to see the last record created, then assign a new one. I have 100 clients running in the main (job numbers) database and doing the manual lookup is get timely.

How can I create a script to create a new record for each client code and in sequence for each client code without maunaully looking up the previous client's related records?::

Basically there is one field that needs the calculation, which is the job number field. I have tried serveral ways to do this and nothing successful yet. Any suggestions would be greatly appreciated.

thanks in advance,

george

[Robert Kidd]

I would create a relationship & 4 fields ( no scripts )-

A self join relationship in the main database matching ClientCode to ClientCode

ClientCode as a lookup from your clients db of ,in this case "SWI"

YearCode as an auto enter text field = Right(Year(Status(currentDate)),1)

JobCodeMax as a calc Max(RelationshipName::JobCode)

JobCode as an auto enter number field = JobCodeMax +1

Finally your JobNumber becomes a calc = ClientCode & "-" & YrCode & Right("00" & JobCode,3)

[George Swisher]

ok, I got everything to work except the relationship. Your message to create a self join relationship with ClientCode to ClientCode. I created a relationship called ClientCodeRelationship and choose the main database as the file to relation which is called Job Tickets (the main database). In the relationship window, the two boxes showed Job Tickets for the databases and I chose ClientCode for each box.

When I tried to define JobCodeMax and I chose ClientCodeRelationship as the file, then JobCode, I got an error that said Circular definition and would not allow me to create the calc.

Did I do something wrong with the relationship?

[George Swisher]

Just wanted to follow up with my last question about the relationship?

Thanks in advance for your help!

-george

[George Swisher]

Sorry to bug you again Robert but ran into another snag.

I have everything working great for the Job Number but just through a script because of a few other items added.

Now, one the Job Number is created, I will have the need to create Change Orders that are specific to the JobNumber. (Kind of like the JobNumber is specific to the ClientCode).

I tried to duplicate the JobNumber process by creating:

SelfJoin relationship called JobCodeSelfJoin. Because the JobNumber is really the JobCode, we just added some other varible to the JobCode for the full job number.

JobCodeSelfJoin is JobTickets as the JoinFile and JobCode as the match.

So I created:

CONumber = CONumberMax+1

CONumberMax = Max(JobCodeSelfJoin::CONumber)

The part where I am stuck is how do I create a new CO (which is really a new record because it has its own estimate and task associated to it) without creating a new Job?

I have a script that duplcates the Job(Record) then inserts a Calc result for the CONumber. But I can not get them to create in order.

Any help is appreciated and I attached some test files to see how it all works.