Calculation with a serial # in the middle

[snatkins]

I need to create a serial # that goes up 1 according to its base.

In other words: I have an invoice # that is RM-01-AB - It is a calculation that pulls the initials of the first name (RM) + Serial # + AB (referring to the type of product)

However, I want the serial #s to go up per that person - so that the next one would be RM-02-AB. The serial #s need to be sequential for each client - I also want to have, for example, TB-02-AB. So that the # doesn't just go up with each new record - it goes up within each client's various records.

I hope that this makes sense - any help would be great!

Thanks!

However, if the next record I create is TB-01-AB

[J__]

I had this kind of problem myself. It took me a bit to get it right.

suppose you have a simple table

pk - primary key

init - the intial the user will type in (maybe)

endcode - might be a calc , i am using it just text, it contains the AB part.

InitCount - a calculation (unstored) with Count (uid) as the calculation

gInit - global variable.

The way I was able to do this was with a script and a "Save Button", which executes the script.

The way it works is that you create a global variable and create a Relation on the same table (self relation ) when you create the relation on the 2 tables, you typically might relate them on the pk. but for this you relate them on the initial field. The 'catch' is to then use gInit as your other field - yep you can use globals if you want to specify the field to use when you link the 2 tables in the relation. This allows you to then set the global when you want and the relation will filter based on that value you set for gInit.

Then in your script, you set the gInit to be whatever the user entered for Initial while they are adding a new record. Then you set the Set Field [ Serial field; Init & "-" & InitCountCalc & "-" & EndCode ]

by setting the global gInit, you are causing filemaker to do a search/query on the same table and return the count of records, which have the same initials as gInit does. The InitCountCalc is keeping count of the records for you, so that count will be the number of records matched. So, it's just a matter of putting them together.

For example, suppose you have 3 records with JS intials. The InitCountCalc - after you set gInit = "JS" will be 3, when you add another JS it will be 4 - even if you add another initial inbetween. Then if someone comes along and their init is KJ, they will get KJ-1-AB, then add another KJ and you get KJ-2-AB

A Calculation for the Serial# is a good idea, but what will happen, at least in my experience, is that when you add a new record, all the records will change to the same number. So in the example above, every record with JS would have for Serial# = JS-3-AB... so it doesn't work, at least the way i was looking at it. That's why I set the field in a script and (it's not a calc), so it won't change.

Hope that makes a little bit of sense.

I may have an example laying around here somewhere.

hope that helps,

sincerely,

J__

[snatkins]

This is a little advanced for me - but I will try to follow it - I really really really appreciate you taking the time to write such a long and involved response.

Best,

Sarah