A tricky serial number Q

[Transmasco]

I posted this on another thread, but I need to resolve this quickly.

I'm making a solution which involves 5 different companies of our group, 3 of which do exactly the same work (cargo transport), 1 does maintenance and the other real estate. So, for instance, I have Purchase Orders on a single table but need it to auto-enter independent serial numbers for each company. Anyone have any pointers on how to achieve this?

[Lee Smith]

I posted this on another thread, but I need to resolve this quickly.

This amounts to a double post. Go back and delete your other post.

Also, this is sometimes the problem when you hijack someone else's thread. It is better to start new thread, explaining your own problem, so that we are not confused by old and new problems.

I'm making a solution which involves 5 different companies of our group, 3 of which do exactly the same work (cargo transport), 1 does maintenance and the other real estate. So, for instance, I have Purchase Orders on a single table but need it to auto-enter independent serial numbers for each company. Anyone have any pointers on how to achieve this?

There is a difference between Serial and Client ID numbers. I have a feeling you mean Client ID numbers?

Serial Numbers should be Automatic for each form.

Client ID numbers should be unique to each Client and are used for invoices, purhcase orders, etc. for that client.

Lee

[Transmasco]

Thanks Lee

The post on the other thread was to a problem I though was similar to mine. i do admit to hijacking tho (mea culpa), won't happen again.

I'm actually talking about serial numbers. i know you can auto-enter S/N's for each form. My situation is that the same form is used for 3 different companies and each requires a unique/sequential serial number. So when I create a new record, and specify what company the record belongs to, the serial number will increment by one on that companies serial number:

new record Company1=serial "C1-001"

new record Company2=serial "C2-001"

new record Company3= serial "C3-001"

new record Company2= serial "C2-002"

new record Company2= serial "C2-003"

new record Company1= serial "C1-002"

new record Company2= serial "C2-004"

new record Company1= serial "C1-003"

etc.

Is there a way to do this in an auto-enter calculation?

Thanks

[-Queue-]

See this post from earlier today. It uses the same idea, though for your purposes, will be much simpler. Let me know if you need clarification.

[Transmasco]

Thanks Queue. I got it already. I made a self joining relationship named Company Serial (Company Code = Company Code) and created the following fields:

[-Queue-]

Nice job. The main difference between our two techniques is your use of Max to produce the next number, which can greatly slow down a database as the number of records grows. I would suggest sorting your Company Code relationship by Serial descending. Then use the calculation Company Serial::Serial + 1 as your NextCompanySerial.

[Transmasco]

Thanks Queue, I see where you're going. I'm gonna tweak it further.

[Transmasco]

OK, I'm having some issues with this.

1) If I, for whatever reason, re-select the truck code, the serial skips. So if the initial serial was say 01-00000, and I re-select the same truck (or another for that matter) the serial changes to 01-00001. If I do it again, it changes to 01-00002.

I need these serials to be bullet proof as they are my key match fields. They need to be entered once and not change for any reason. I will be needing to do the same procedure on several tables for the very same reason.

2) I've been fighting with this for days! >: I have a looked up field based on truck ID wich looks-up the trucks license plates. It's looking up the wrong data. Could this be from the serial changing? (I don't think so).

I've bought and started reading "Special Edition Using FileMaker 7" (great book BTW) and have made some changes based on what I've read. Could these changes be at fault?

I'm at a point in which I'm second guessing myself. Please help :`(

[-Queue-]

I believe CompanySerial should be an auto-enter calculation with 'Do not replace existing value...' selected. If it's a normal calculation, it will update whenever CompanyCode or Serial changes, which you don't want.