Serial-Nr. Problem!

[Tissot]

Hello!

I've got a small Problem.

I've got 4 Tables for 4 Different Invoice Layouts and I need to somehow create one Serial Number for all Tables.

Basically one Serial Number spread across 4 Tables.

Thanks for youre reply!

[transpower]

Make three of the serial number fields calculation fields based on the serial number field in the first table.

[RalphL]

I wonder why you have 4 tables for invoices. Couldn't you have the four layouts in one table.

[Reed]

Why do you have different tables for the invoices? Why not just have one table for invoices and have all 4 layouts represent the same base table? I'd imagine that most of the fields would be the same, and you would just have one field that would differentiate between the 4 different situtations.

[Tissot]

Hmmm,

I dont know, how should I do that ?

[Tissot]

No, you see, they're completely different!

[Reed]

But they're all invoices right? What makes them different?

[Tissot]

One is for Time Invoicing, another is for Material, etc... there's no way out of not having 4 Tables for this Application.

[Ender]

One is for Time Invoicing, another is for Material, etc... there's no way out of not having 4 Tables for this Application.

Unless you have a compelling reason to keep them separate, it is usually better to have like-data in the same table. It would certainly solve your serial number problem.

Combining these tables does not force you to access them from one portal, if that's what you're worried about. You can use another field to differentiate the data, and include that in the relationship:

'Invoice->Line Items Materials' relationship:

Invoice::Invoice# = Line Items::Invoice#

AND Invoice::gTypeMaterials = Line Item::Line Item Type

'Invoice->Line Items Labor' relationship:

Invoice::Invoice# = Line Items::Invoice#

AND Invoice::gTypeLabor = Line Item::Line Item Type

So four global Type fields in the Invoice table, one Line Item Type in Line Items, and four table occurances for the relationships should work.

[crpatter]

I don't know if you've finished your solution yet.

You could have a central table that had the common fields that all invoice types shared, then create a related table for each layout type. Since you don't have to use a portal to access related field in other tables, each layout would be simple to create.

[Tissot]

Thanks!

The best solution is a Global field and tell Script to +1 each time you clik new!

Cya

[Ender]

The best solution is a Global field and tell Script to +1 each time you clik new!

This could be problematic in a multiuser environment, as each workstation keeps its own copy of globals that are only available to that workstation's current session.

[Tissot]

I see so how would you proceed ?

Can some one make me a smal sample ?

Thanks in advance !

[Ender]

I still recommend combining your tables into one. You haven't given a good reason not to.

But were there a good reason, you could use the Last() function to see the last serial number used from each table, then set the new serial number to be 1 greater than the highest one (each Last(Table::Serial) function would need to look at a table occurance based on a cartesian join(X) relationship.) This still has a possiblitiy of two users creating a record at the same time and getting the same next serial number.

[Tissot]

I've still got a BIG Problem ! PLEASE Help!!!

Is there a simple way to do such a thing?

Because the Global field doesnt work! (duplycates)

Central Tabel ? or....

Please Help!

Thanks bigtime in advance!

[Transmasco]

I've been lurking for a few weeks and am amazed at what you guys here do. Since I'm gonna be needing lots of help in the coming months, I decided to start posting.

I think I have a similar problem.

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 serial numbers for each company. Anyone have any pointers on how to achieve this?

Also, I've grouped info by area i.e. Administrative, Operations, Maintenance and accounting; each with its number of tables and layouts. Should I cram all these into a single file or create a file for each area? What would the pros and cons be? We have 10 users (both Macs and PC FMP7)and FMP Server 7. I created our current solution 10 years ago and its been growing over the years to the point i have a mess of unused layouts and fields. So I'm forcing myself to upgrade to FMP7 to redo everything and add new tables (accounting ~shivers~) and all this in Spanish.

Any and all help appreciated

[Tissot]

But were there a good reason, you could use the Last() function to see the last serial number used from each table, then set the new serial number to be 1 greater than the highest one (each Last(Table::Serial) function would need to look at a table occurance based on a cartesian join(X) relationship.) This still has a possiblitiy of two users creating a record at the same time and getting the same next serial number.

Can you or some one explane how to do this ? or make me a simple sample?

Thanks in advance!

[Tissot]

Is there any way els to have a serial number across 4 Tabels than a Global field ?

Global field version dosent work (I alwys get duplycates)

PLEASE HELP ME OUT OF THIS MESS!

Thanks ever so much!

[-Queue-]

Create a table that is related to all the necessary tables via a constant calculation field of 1. Create a serial and global field for this table and one record in the table. Enter your next serial into the serial field. Create a script

Set Field [global; serial]

Set Field [serial; serial + 1] {If you need to add leading zeroes, use Right( "000" & serial + 1; 4 ) or something similar, depending on how many zeroes you require.}

Call this script each time you create a new record, then Set Field [yourtable::serial; constant_relationship::global].

[mse]

One is for Time Invoicing, another is for Material, etc... there's no way out of not having 4 Tables for this Application.

Why do you need the same Serial No for different Tables? Do you want expose it to users? What for?

[Tissot]

I attached a File.

Is this what you mean Queue ?

If not can you help ?

Thanks very much for youre Help!

SerialProblem.zip

[-Queue-]

Actually, you can use a simple cartesian relationship for this. And the constant table only needs to have one record. You can use GetNextSerialValue and Set Next Serial Value to update it. You only need one script; you can use script parameters to determine which field to set.

See attachment for modifications.

SerialProblem.zip

[Tissot]

Thanks now I understand.

Could this solution cause Duplicates?

Thanks for your answer.

Sincerely

[-Queue-]

Yes, anytime a serial is not auto-entered immediately upon record creation, there is a possibility for duplicates, which is one reason this sort of setup is not very good design. I'll elaborate later when my mom isn't talking my ear off on the phone for half an hour.

[Fenton]

Duplicates would be possible in a multi-user situation; unless you lock the record. I've included a little example of what it would look like. No guarantees, but I think it'll stop duplicates.

SerialProblem2.zip

[Tissot]

Works great thanks!

I made about 200 new Records in a random way with two machines and no dupes!!!

I think this string will be quiet from now on :-)