32 replies to this topic

[Mitesh]

Hi
I need a functionality where i need to generate a alphanumeric code based on the type of customer selected.

For e.g. if the Customer is of Type A then i need to just generate a numeric value. e.g. 5000 is the current and the new one will be 5001.

For e.g. if the Customer is of Type B then i need to just generate an alphanumeric value.
e.g. XXX C5000 is the current and the new one will be XXX C5001.


I have created a table with following fields. (Please see the attached .txt file)

Type of Customer text e.g. A
Prefix text XXX C
NeedsPrefix Boolean True
NumericValue int 1000

I have got the logic of how to go about doing this but I need help to create a function where i pass in type of customer and it will return me a new code.


In the function it will check if NeedsPrefix is true then it will join the prefix & max of NumericValue and return that and will also increment the NumericValue for next/new record.

Regards

Mitesh

Attached Files

•  1196535739-Table.txt   317bytes   560 downloads

[Matthew F]

I need help to create a function where i pass in type of customer and it will return me a new code.

Are you sure that you need to create a function? Assuming that the table that you've given is the same table where you'd like the results you could do the following:
1. Define a 'Result' field for your table.
2. Create a self-join relationship by creating a second incidence of 'table' (called 'tableSelfJoin) in your relationship graph and joining it to the first via 'typeofCustomer'.
3. Create script which does the following two steps:

Set Field (NumericValue; Max(tableSelfJoin::NumericValue) + 1
    Set Field (Result; Prefix & NumericValue)

[comment]

Beware of such methods: if two users are creating a record at the same time, you will get a duplicate serial. It's best to use Filemaker's built-in serial number generator. If some record types need an additional tag, that can be added in a calculation field - but the numbering should be consecutive for ALL records in the table, e.g.

Serial ... Type ... CalculatedID

5000 ... A ... C5000
5001 ... B ... 5001
5002 ... A ... C5002
5003 ... B ... 5003

[Matthew F]

I agree that care must be taken to avoid duplicate serial numbers. However, I must say that even Filemaker's built-in serial number generator occasionally has the same problem. For example if I open a second window a single table and create a new record Filemaker does not always increment the serial number as it should. For this reason it is important to also specify 'Unique Value' under the field validation option.

[comment]

if I open a second window a single table and create a new record Filemaker does not always increment the serial number as it should.

I've never heard of such issue. AFAIK Filemaker's serial numbering is reliable. In any case, validating on unique will not only progressively slow down a solution as the number of records grows, but more importantly, the user has no way of redressing the issue, if and when it occurs.

[Mitesh]

Thank you all for the response.

I quite agree with Comment's recommendation though
I am not very fluent with Filemaker and dont quite know how to implement this.

I have a layout called Job. User will Select the customer and as i said based on customer i need to generate the JobNo. How can i display the calculated value in JobNo field?

Do i need to run a script and pass the type of customer and display the result back in JobNo Field?

As user has just selected the customer how would field in job table know about the type of customer, as record is not saved yet?

In theory i understand it, please help me out with implementation.

Regards

Mitesh

[comment]

I am a little confused here. Do you mean you want to prefix the JobID in the Jobs table according to the type of customer - not the CustomerID in the Customers table?

May I ask what's your purpose in this? After all, these numbers are not intended for humans, and computers do not need prefixes. I suspect all you need is to do is lookup the customer's type from the Customers table into a CustomerType field in Jobs - and maybe not even that.

[Mitesh]

Sorry for the confusion, perhaps i didnt explain it very clearly.

You are right i need to generate the jobno in job table for type of customer. The reason to generate different job no based on customer type is becuase customer wants to have the same format of the jobno generated by my sytem, to theirs.


Mitesh

[comment]

Well, this was quite common in paper-based systems and is now completely obsolete. You should tell your client you can display the customer's type directly on a layout of Jobs - without them needing to decipher any codes.


In any case, to achieve this, you should have a table of CustomerTypes, with these fields:
• CustomerType - text, validate as Unique
• Prefix- text

Naturally, there would be a Type field in the Customers table too, and the two tables would be related thus:
Customers::Type = CustomerTypes::CustomerType

Then in the Jobs table you would have these fields:
• JobID - number, auto-enter serial number
• CustomerID - number (this is used in the relationship to Customers)
• cJobNumber - calculation (result is text) = CustomerTypes::Prefix & JobID

You would use JobID for any relationships etc., but display cJobNum to the client.

[LaRetta]

However, I must say that even Filemaker's built-in serial number generator occasionally has the same problem. For example if I open a second window a single table and create a new record Filemaker does not always increment the serial number as it should. For this reason it is important to also specify 'Unique Value' under the field validation option.

I haven't heard nor experienced this problem either. In fact, I would have to strongly express my disagreement with the possibility. I must speak up because I do not want newbies (only they might believe such a statement) to think it might even, in a remote chance, be true.

Setting as Unique will NOT resolve the issue even if it DID happen (which it does not) because auto-enter serials should NOT EVER be allowed to be modified so the User could never correct it anyway. Even if the User corrected it, they could not then update the auto-enter serial next serial option and it would break again when it then set the NEXT serial to the one the User changed. FM-generated auto-enter serials are the ONLY method which can totally be trusted to work right every time. To believe otherwise is to invite total disaster.

I cannot say this strongly enough. If anyone needs it repeated, just let me know.

LaRetta

[bcooney]

I've seen a couple of posts recently where clients are asking for meaningful numbers that the developer then will use as a key field. For all you novices out there, DO NOT make any key fields calculations. They are to be auto-enter serial, prohibit modification and meaningless (perhaps not even displayed).

I have never, in my 15 years of development, EVER seen a FM auto-enter serial fail.

[Fenton]

Just to expand on this a bit. We are not saying that you cannot "have" these cute "pseudo" ID fields. If a customer wants/requires it, then fine, give it to them. But in that case it is just another field, a special one perhaps, but just another field. Put it wherever you want on layouts, but do not ever use it for relationships. That way, if it fails, you will simply be able to fix whatever went wrong. You will not have orphaned thousands of related records, or worse.

Separate, in your mind and in your files, what the client sees as a "custom identification code" field from your FileMaker auto-enter serial numbers (especially if used as keys). Yes, sometimes it means you have a kind of "dual" identification, and you may need separate layouts for viewing as the developer (generally you should anyway). But you will have a safe system.

[Mitesh]

Thank you all for the input.

I am not going to use this calculated fields as the primary key. I will have a JobId as a primary key but i need those "pseudo nos."

[Matthew F]

I've never heard of such issue. AFAIK Filemaker's serial numbering is reliable. In any case, validating on unique will not only progressively slow down a solution as the number of records grows, but more importantly, the user has no way of redressing the issue, if and when it occurs.

I certainly agree with all of the comments about the wisdom of using FM generated serial numbers as primary IDs for records. I wish I could say that I have never experienced the serial numbering duplication problem but in fact I've encountered it on at least 3 occasions on solutions hosted on FM Server v8. Believe me, it can be a real pain to discover duplicate IDs after the fact.

The addition of the requirement for Unique validation, without any additional scripting, has taken care of such events on several subsequent occasions because the user is forced to reject the offending record. So far there isn't notable slowing of record generation with ~10,000 records.

[Ocean West]

http://360works.com/scriptmaster/

ScriptMaster has a function that will create a UUID

[LaRetta]

Not only have I never experienced duplication of FM's auto-enter serial but I have never read a single instance of it in reading every post on every FM Forum and Tech Talk and reading every Tech Info published. If it happened, it would be discussed a LOT. I still do not believe it.

[bcooney]

The only way I believe this could happen would be after an import where the next serial was not reset properly.

[Matthew F]

1. The serial number issue is definitely not related to imports. But you've raised my level of curiosity about this. I think I'll see how reproducible it is and whether I can narrow down the issue at all.

2. While we're on the issue of unique serial number IDs... I always wondered why not use a calcuated field: Get(RecordID)? It seems to work about the same as a serial# when I've tried it.

3. Please correct me if I'm wrong here. Doesn't the use of hidden serial numbers as a match fields (with psuedo user created IDs) have some disadvantages? I'm thinking of the situation where you have pre-existing data in two separate tables. Its nice to be able to enter the known ID in a portal to either a) pull up the related data or (if doesn't yet exist) generate a new related record. If the true match field is invisible then only option ( is possible.

[comment]

2. Because if you ever move your data to a new file, you can no longer be sure the ID will be unique.

I didn't understand #3.

[LaRetta]

RE #2 ... it also will not increment, it instead jumps exponentially. RecordID increases similar to:

1
2
3
32768
... before long, your numbers are 50 digits long and climbing. Try putting THAT on a layout for Users to view.

RE #3 ... please spare your Users from needing to either 1) memorize numbers or 2) keep cheat-sheets closeby from which to refer. Instead, provide them with a method to find the information based upon something logical that they know such as starting letters of last name, phone number, part name etc.

IDs that we Developers use should always be meaningless and don't need to be displayed if your solution is designed properly. The business may use Invoice numbers but that should NOT be the ID binding the relationship. The business might have part numbers but that should NOT be the uniqueID either. There are too many times that the business will decide they want to begin renumbering their invoices; or the manufacturer will decide to CHANGE the part number or ... the list is endless.

I highly suggest using FMs auto-enter serial, keep it hidden, and never use anything that might ever change (such as invoice numbers, part numbers, account numbers etc). Otherwise, you can be reserializing parents, children and grandchildren* when the business changes their methodology.

* corrected from grandparents