PLEASE Help: Auto serialisation field?

[mikmac68]

Hello every body,

Thanks a lot for that extraordinary forum. I love It. BRAVO !

My problem is:

I use a Valuelist: with 06 possible choices: BSP,BRP,BID,BLE,BRE,BDI. (The choce is made by user).

When done, I want to generate an incremental serial ID for each category, wher the format is

- BSP1,BSP2,BSP3,...

- BRP1,BRP2,BRP3,...

- BID1,BID2,BID3,....

When a new record is added, and the selection of category entred by the user, I want a calc that searches in the table to find the last categ serialized, pick the max and then add 1 (exp: BSPxxx+1 and so on) same for the other categories.These Numbers should be displayed automatically to the user and no chages allowed on it.

In parallel, a real auto entered serial Number is defined in another field defined for the relationships and integrity.

How to perform this.

I found so many posts treating that subject but, I didn't find out how to implement it for my own use.

Thanks in advance.

mikmac.

[aldipalo]

You can accomplish this by having a serial number field, your value list field and a calc field:

::Valulistfield & ::Serialnumberfield

See attached file.

I'm sure someone will have a more elegant answer, but this works.

BTW, you only need to post your question once. Try to pick the correct forum topic. People tend to get a bit dicey when you have multiple posts of the same question. Don't fret all newbies make this mistake.

Custom_serial_field.zip

Edited January 6, 2007 by Guest

[mikmac68]

Thanks Aldipalo,

But the Valulist contains: 6 Values: BSP - BDI - BRP - BRE - BLE - BID.

The serial Number I want to generate should be made upon these values and the format would be a serialisation from 1 to n (On creation) for each category picked from the Valuelist.

- Category "BSP" : BSP1, BSP2, BSP3, BSP4, ...

- Category "BRP": BRP1, BRP2,BRP3,BRP,...

- '' "BID": BID1, BID2,BID3,.....

Same kind of serialisation for all the categories listed above in the valuelist.

Adding a new record is a button on the layout and a selection in the valuelist as described above (BSP, BRP, BID, BRE,BLE,BDI).

The user can create a record in any category he wants and the serialisation must be incremental by 1 within each category

exp:

BSP1

BSP2

BSP3

BRP1

BRP2

BRP3

BSP4

BLE1

BRP4

BLE2

BLE3

BRP5

BRP6

BSP5

BSP6

BRP7

BLE4

BLE5

And so On... (For each category a counter within it).

Thanks Alot.

[Stuart Taylor]

The is not strictly a serial number but i get what you are trying to achieve.

Table 1 contains 6 Records with 6 unique references

AAA

BBB

CCC

DDD

EEE

FFF

Table 2 contains line items

AAA 1

AAA 2

BBB 1

BBB 2

DDD 1

CCC 1

These are 2 fields ...

Table 1 Reference (eg: AAA)

Table 1 Reference SerialNumber (eg: 1)

1. create a relationship between the "Reference" in both table

2. For table 1 Sort the values by "Reference SerialNumber" decending

2. In table 1 create an unstored calc "Last SerialNumber" and make the result "Table 2 Reference::Reference SerialNumber"

3. "Table 1 Reference SerialNumber" can auto enter "Last SerialNumber" + 1

[aldipalo]

As I said someone would probably have a better way.

:

Edited January 6, 2007 by Guest

[mikmac68]

Thanks, But still incompete !

The SerialNb I generate in the form AAA1, AAA2 or BBB1,BBB2,BBB3 is a an ID in another table (customer's Orders)

How to make it work if the fields are separated on 2 Tables ?

All the sequence is an ID Nbre for each customer's order I add in my layout when I push NEW RECORD.

Still waiting, Thkx

[Stuart Taylor]

Still Waiting?

Maybe you should have tried to follow the step by step instructions i spent time writing before posting a blind reply.

Time to hire a developer?

See attached

SpoonFed.fp7.zip

[T-Square]

You said:

The SerialNb I generate in the form AAA1, AAA2 or BBB1,BBB2,BBB3 is a an ID in another table (customer's Orders)

How to make it work if the fields are separated on 2 Tables ?

All the sequence is an ID Nbre for each customer's order I add in my layout when I push NEW RECORD.

You are headed for a serious set of troubles down the road. For example, what happens when a user changes AAA to BBB? You really should be using non-meaningful ID fields (i.e., unique auto-enter serial numbers) for your relationships. You will save yourself HUGE headaches. If you just have to have these numbers, set them up as calculated fields that aren't actually used for any relationships.

David

[Raybaudi]

Hi

you can try this one, too.

Custom_serial.zip

[Stuart Taylor]

Very neat daniele :

[LaRetta]

Just so you know. I broke it by two Users creating a new record simultaneously. I wouldn't trust this over a network. All I did was leave the cursor in the field in the first window and opened a second window. Duplicate. Same situation which would happen in a networked environment. I sure liked the concept however. :wink2:

[LaRetta]

I should add that I think adding a Commit Records/Requests might prevent it. But I STILL would not trust custom serials. There have been hundreds of discussions on the issue and any time you look outside standard record serializing, to a count of records, it will break eventually.

[Stuart Taylor]

This should work...

Custom_serial_2.fp7.zip

[Raybaudi]

... or try this one too ( an eventually duplicate record will have a new serial )

Custom_serialMod.zip

[Stuart Taylor]

Behaviour Observation..

In my example if you delete a middle value the next serial will be the next highest

In your example it fills in gaps so if i delete BSP2 and im up to BSP10 the next serial will be BSP2 not BSP11

This is very interesting and probably has some great applications.

[LaRetta]

Turning off entry in Browse (field behavior) is a Band-Aide. A User, when creating a new record, will keep their cursor in OTHER fields and the record will not be committed. THAT is the problem here. I just created a duplicate record, Stuart, by adding another field and (as a typical User) didn't commit the record before another User created one.

I see Daniele posted a file. I haven't looked at it yet and I don't have time to look now. Suffice to say that I stand by my point that it is NOT SAFE in multi-user; only FM auto-serials can be trusted.

Edited January 7, 2007 by Guest

[Stuart Taylor]

:titanic:

[Raybaudi]

Your mod is better than mine !

BTW:...

This is very interesting and probably has some great applications.

... that is the way that custom function operates, it recovers the progression :

[Raybaudi]

only FM auto-serials can be trusted.

I'm of the same your opinion, LaRetta :

I want only point here that he said:

In parallel, a real auto entered serial Number is defined in another field defined for the relationships and integrity.

[Stuart Taylor]

Hi LaRetta,

Point taken, but the reason i took part in this discussion is that i have a client base that demands the same functionality.

I use a serial as an actual key but they want to track the incremental creation of records by each maker of the objects in the inventory.

It is not good practice but neccessary in certain industries ... i use something similar to a combination of my first and second example with a bit of scripting to try and avoid dupes.

Its unfortunately an unavoidable need by my clients.

This is not my Topic but any "MORE SAFE" suggestions would still useful for those who need to implement this kind of thing.

You will also notice that i made it clear thta this is not strictly a serial and called the field a Reference instead.

I agree with you 100% but the thing still needs to be addressed.

best

Stuart

[Stuart Taylor]

Now i read the original post i notice the point was to achieve a SERIAL ID

This is not possible safely using any method other than serials themselves.

best

Stuart

[LaRetta]

I want you both to know I liked what you were doing (with the concept) but not the implementation. I was greatly concerned that it be made clear this should NOT be trusted to hold a relationship together; and that they are aware of possible duplicates. The problem is that ... when they trust that number to mean something, then they will trust that number to mean something. And they can be wrong. And since it can be wrong, why use it at all? Why not just display the unstored aggregate count for them?

As long as they clearly understand the risks in putting faith in this type of process, I'll leave you all be. I can be overly fussy.

UPDATE: I hadn't seen your last post, Stuart. :^)

Edited January 7, 2007 by Guest
corrected typo

[Stuart Taylor]

Just looked at my working solution.

In that the NEXT SERIAL is set on the MAKER table by a script rather than a calculation.

As of yet 4 years it has not broken.

If someone else runs a script it will always reverence the value that has changed on the MAKER table.

this avoids the need for relationships, calculations and uncomitted records.

It also means that the New Record script must be customised.

I used to use a Paused Loop script to do new record locking.

Custom Menus is a much better solution today.

best

Stuart

[comment]

The question is WHEN does the next serial increase, compared to when is a new record committed. It is this gap that causes the concern.

A safe method would be to have a numerator table per category. Possibly, a method could be devised to reduce this to a single table with a field per category, but I'd have to think about that. And it probably wouldn't reduce the clutter in the RG anyway.

[mikmac68]

Good Day for You All,

GReat Thx for your replay,...

I tried out all of the solutions posted here.

I started using the first solution posted (SpoonFed.fp7) and then all the others. Still there a problem.

The stick is that the progression serial ID does not goes up to more than 10 records within the categories in the listvalue.

Expl:

BSP1, BSP2,.....BSP10 (can't create BSP11,...).

BRE1,BRE2,BRE3,....BRE10 (no more record can created).

and so on for the reste of the categories

I can't create more than 10 record in each category of the valuelist.

What can I do ?

[Raybaudi]

Hi

try this one

Custom_serialMod2.zip

[comment]

I think you will find this can get very slow fairly quickly - say with around 2000 records in a category.

[Stuart Taylor]

The stick is that the progression serial ID does not goes up to more than 10 records within the categories in the listvalue.

That simply isn't true of my example "SpoonFed.fp7".

:

I have also seen no evidence of you actually trying to understand the examples posted or attempting anything yourself only a "this doesn't do it for me, i need more" attitude. This bothers me.

This will not help you develope.

[mikmac68]

Sorry Stuart, I was wron about Ur solution.

It worked finaly, I've done spmething wrong.

But for the rest it didn't work.

Thx a lot

I want to thank all of U about Ur interest. I alredy said that Ur forum is gold mine. Everybody should read what's in here.

Fopr instance, I'm going to test the second example "customSerialMod2" I think that works fine as well. I'm discovering a lot of hiding things in FMP.

Don't forget that I just started developing in FMP, I'm only a newbie.

Sorry for all the disturbs.

I'm sure that I'll post some other questions. I m doing so hard with FMP (In my point of skills).

So till that, Bye for now.

[Stuart Taylor]

As a newbie it is important to try things out yourself and listen to the advice of others with more experience.

Even as someone with experience you will often find others with more experience than yourself in specific areas, that is what makes the forum so great.

Although you seem to be close to a solution, the truth is that if you read the posts above carefully your fundimental approach is flawed.

As a newbie you should listen to this advice.

You still refer to the result as the Serial_ID which i assume you wish to use in a relationship.

This will be a big mistake.

This is a Reference at best.

If you deploy any of the solutions posted in a networked environment (especially with your level of experience). You are likely to end up with duplicate records and broken relationships.

I would suggest re-evaluating your approach or at least posting what you really are trying to achieve before proceeding.

This will help you in the long term.

[mse]

Take a look at "Independently Incrementing Serial Numbers" by CobaltSky (Ray Cologon) on the top of the fp5 sample files at

http://www.nightwing.com.au/FileMaker/demos.html

[mse]

In your file, if you delete a mid-row record in a series, e.g.

BRP2 in BRP1, BRP2, BRP3,....BRP12, and then create a new record - it wil be recreated (as BRP2 instead of BRP13) that compromises the integrity of a solution

[Stuart Taylor]

This had been addressed in earlier posts ...

"... that is the way that custom function operates, it recovers the progression"