Invoice # with Serial # going up per User

[snatkins]

I am re-posting the following query I had posted a while back - to which someone posted the answer to my question. However, now, two weeks later, I am having a problem with part of it -so here I will post my original post and then the fantastic response I got:

ME: "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."

THE RESPONSE / SOLUTION:

"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__"

Okay, so I did this - but I am having trouble with the "InitCountCalc" part. I don't know what he/she meant by: "InitCount - a calculation (unstored) with Count (uid) as the calculation" - what is "uid" here?

I assumed that the calculation will be counting how many records have the same global variable (the same Initials) - but it just keeps ending up as "1" - in other words, it isn't exactly counting (I made sure it was unstored).

I know this is very complicated - I am hoping the original person who helped me will see this - but if anyone else knows where I have misstepped that would be wonderful. HELP!!!

THANKS, Sarah

[SlimJim]

I have come on this one rather late but I see that you are now using FM7. Try the following: In your invoice table you will have fields for Initials, SerialNumber,ProductCode. In the relationship graph make two instances of your invoice table say i1 and i2 and relate then by Initials (essentially as above). When you enter a new invoice using a layout based on i1 I guess you will first enter the customer initials, this establishes the relation, autoenter into the SerialNumber field the value Max(i2::SerialNumber) + 1 (this increases by 1 the previous highest serial number for that Initials), and then input your product code. You can now construct the Invoice reference by a calculation field

Initials & " - " & SerialNumber & " - " and ProductCode

If you want your serial number to be a fixed number of digits as in your examples 01, 02 etc then you will have to convert the SerialNumber to text and format it to have leading zeroes: Right("00" & GetAsText(SerialNumber);2) will give you a two digit number. (Unless you have a lot of one-offs I would suggest that you go for at least 3 digits Right("000" & GetAsText(serialNumber);3)

[J__]

Hi snatkins,

I'm J__ - the original response I posted to you also included a sample fm7 file attachment which illustrated the solution didn't it? I thought i had included a sample there. Let me know if it's not there... that's why I might've grazed over a few details because i figured you could look at the sample and see what i was talking about.. . I'm sorry if I wasn't clear.

InitCount is a unstored calculation, which is Count ( uid ) where uid mean, in my terminology, "Unique ID" same as a primary key on the table. On all tables you should have a field which is a primary key, by doing InitCount is an unstored calc which does just this: Count(mms::UID) where mms is a table, you get the number of records.

so right when you do the self join with the gInit as the variable when that is set to the initials the second instance of the table will contain only matches to that initial and you will then find that InitCount contains the (initials count), which Count (mms::UID) . Since the table is only showing the matches you get the count of initial in gInit.

Slim Jim makes a great point about adding the padding for the middle initials.

hope that makes it a little clearer.

sincerely,

J__

[J__]

Me again, oops I did not supply a sample as i thought - sorry!

here is a sample, which illustrates (hopefully better) than what I was talking about.

Sincerely,

J__

serialInc_Sample.zip

[-Queue-]

Unless I'm missing something, here's an alternate method that doesn't require a script and should work faster because Count( ) is not involved.

It is currently set to include both the prefix and suffix to determine uniqueness. If suffix isn't a factor, then the relationship can be changed to match Prefix to Prefix instead and the prefix/suffix calc can be removed.

serialInc_Sample2.zip