Jump to content

How to generate a unique alpha numeric code based on condition?


This topic is 5902 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Table.txt

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.B)

Serial ... Type ... CalculatedID

5000 ... A ... C5000

5001 ... B ... 5001

5002 ... A ... C5002

5003 ... B ... 5003

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Edited by Guest
Link to comment
Share on other sites

Based on all the suggestion i tried to write the code.

All i can say is i have managed progressed a bit.

I am attaching a test system and also an excel file where i have explained with example of what i am trying to achieve.

I would really appreciate if any can have a look at the test application.

I have written a script that does the job but when i attach it to Division Popup menu on Job layout it generates the new job no but doesent show the popup, that is the first problem.

Second problem is i would really appreciate if someone can help me in any way to generate the Code No.

Thanking you in advance.

Mitesh

Forgot to attach the file.

Test.zip

Edited by Guest
Link to comment
Share on other sites

ROFLMAO! Well, Soren, I didn't particularly mean THAT kind of renumbering but it would certainly fit into the list! Here are some other reasons I've been given:

1) Invoices: Owner didn't want anyone to know how many invoices they generated a year.

2) Invoices: The numbers just get too long for people to easily reference and the owner caved into constant complaints from his staff and customers.

3) Product Parts: Manufacturer decided their part numbers weren't logically identified so they changed all their codes.

4) Customer code: It confused customers because all codes started with first 3 digits of the person's last name but woman remaried and changed name and couldn't understand why the code couldn't be changed.

5) Business changed financial advisors who assigned different account codes.

6) Product UPC codes were changed to meet state regulation changes.

... heck, even a person's Area Code can be changed so trusting a phone number isn't safe. Every one of these codes had meaning to the business or was generated OUTSIDE of FileMaker. Only trust your own Developer generated IDs. And since they may get very large because they NEVER will change; and since they have meaning to NOBODY, don't even display them. The reasons listed above are valid. And businesses have a right to adjust their numbering how they wish. But it can drive a Developer mad (I know). :hair:

As for Lay? Some people will do anything to get out of taking responsibility and paying their own consequence. :crazy2:

Edited by Guest
Link to comment
Share on other sites

I still don't get your point, probably because I would never use either one of the shown methods.

Note that in the "named" portal, if you enter the wrong name, it creates a join record that is orphaned on the other side. And if you rename a part or a product, all their joins are lost.

Link to comment
Share on other sites

I don't understand your file at all. First you said you have Jobs and Customers, and the JobID needs to be prefixed according to the type of the customer. But your file has Companies, Divisions and Jobs. And it is not clear what the relationships between them should be. Your graph shows that a Company has many Divisions, and that a Company also has many Jobs. So there is no relationship between a job and a division - yet there is a field DivisionId in Jobs. That just doesn't make sense.

Link to comment
Share on other sites

I did mention in previous post that I will have JobId as auto enter but Job No and Code No will be calculated fields.

I apologise again for all the confusion that i have caused and probably if i had expalined this properly in the beginning this issue would have got sorted.

Anyway, this is the requirement.

A Company has many Division. Many Jobs can be created for one division of one company. And based on the Company you select in the Job screen it should generate unique job No and Code No.

Say for example for "Test company" the first Job No will 1 and will be incrementing by 1 for this particular company and for "Test Company B" it will start from 1 and increment by 1.

For Code No, based on Company Id and Division Id it will generate an alphanumeric code.

Job Table just stores the Division Id(Selected Division) for the Company.

You can see in the Excel file attach here with filemaker fiel the example of Job No and Code No.

From reading on the forum i think i need to use free Script plugins to execute the scripts to generate the Job No & Code No field.

I would really appreciate if someone can provide the solution using DOScript.

Regards

Mitesh

Link to comment
Share on other sites

Say for example for "Test company" the first Job No will 1 and will be incrementing by 1 for this particular company and for "Test Company B" it will start from 1 and increment by 1.

As I said in my first post, I don't think that's a good idea.

I cannot see your new attachment (some problem with the forum's software again), but based on what you said, the relationships should be Companies --< Divisions --< Jobs. A job is assigned to a division - not to a company. A job "knows" who its grandparent company is through the relationship to its parent division.

Link to comment
Share on other sites

As I said in my first post, I don't think that's a good idea.
I know, but customer wants it and other then just a counter it means nothing.

Ok Michael, I see your point. What happens if any Company doesent have any Division? How would that get handled?

I will try to attach the file again otherwise its the same file that i have attached before(Test.Zip)

Link to comment
Share on other sites

I have a feeling we are going in circles here. My point is, I don't know how to do this reliably. If you want to do this unreliably, follow the first advice you have been given - or the one given here:

Come to think of it, you might find the entire thread interesting.

Any method you choose, you should think carefully what will happen if a job is deleted, or re-assigned to another division/company, and any other scenario that can mess up the scheme.

Quote
What happens if any Company doesent have any Division? How would that get handled?

That is a tough problem. I don't think there is an easy answer for it - and probably not a single correct answer either. Roughly, I would say you have two choices: either assign jobs directly to a company (as in your first file), with a sub-assignment to a division - if one exists. Alternatively, put divisions and companies in the same table, with a self-join indicating who's the parent of whom - see here for something very similar:

Each approach has its pros and cons, and only someone like you who is fully familiar with all the aspects can weigh them. Perhaps other forum members might come up with more ideas.

Link to comment
Share on other sites

  • 4 weeks later...

Please be patient with me.

I just need expert eyes to spot what i am doing wrong. I just came back to this system and i have managed to nearly achieve what i want to do, but not quiet yet.

In Job layout when i select Company it shows me all the Division for that company.

It will genereate the Code No if I select "Division-1 of AAA" , but i cant understand why it does not generate the Code No when i select "Division-2 of AAA".

You will need to use DataViewer and Script debugger to see what is happening.

Why does it select only the first Division Id and not the Division Id i select from the drop down.

I really appreciate everyones time and patience.

Test.zip

Link to comment
Share on other sites

  • 4 weeks later...

This topic is 5902 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.