Jump to content

Changing a Unique Key


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

Recommended Posts

Before converting to FileMaker, our Invoice numbers were generated in PeachTree as 000001 (text field). I saw no reason to have both an invoice number AND a serial so I continued with the existing InvoiceNumber (and left it as text). We are now at 256250. I have been asked (begged) by Owner if there is a way to re-start our invoice numbers AND to drop the leading zero stuff. As you know, this Invoice number holds many relationships together and is (what I consider) a major table. I also want to make that Invoice a number field instead of text (I think).

Invoice 1 (text) will not relate to Invoice 000001 (text) so I'm fine there but if I make Invoice a number they DO RELATE! Also, I created a test file and created (10) 0000# records and then created (10) # records (same numbers). Sorting this mis-match incorrectly sorts ascending (number) as:

00001

1

00002

2

00003

3

I want it to sort as:

00001

00002

00003

1

2

3

So one problem will be sorting Invoice found sets if it contains both types of data. I decided I could use a calculation for our 'real' invoice numbers (which makes them consistent) but THEN I'd have duplicates! Am I nuts for even considering this? Do you see other problems that I can't see? What would you all do in this situation? And yes, I have learned that - anything a business might POSSIBLY EVER want to change even if they SWEAR they won't - don't count on for uniqueIDs. But this numbering system was in place before FileMaker and we had payments coming in against them! So now what do I do?

UPDATE: I realize I can assign and set a serial number through them. And just sort by that. But this subject makes me VERY nervous and I need 'combined experience' input on this one. I don't want to discover other Gotchas down the road.

LaRetta :blush2:

Edited by Guest
Added update
Link to comment
Share on other sites

Hi Laretta,

A couple thoughts:

I realize I can assign and set a serial number through them.
- I do not understand what you mean here. What are you referring to by 'them'?

Invoice 1 (text) will not relate to Invoice 000001 (text) so I'm fine there...

Yes, but this is only true for the first 99,999 new invoices. Invoice 100000 will relate to the new invoice 100000, and then the fun begins...

As far as the issue with finds and duplicates go, I suppose it is up to the owner on how to handle that. It is his bright idea to repeat invoice numbers, does he want both to show up when he keys in the number, or just the new one? How does he want to distinguish between the old and new?

I would say the way to go is keep the numbers running but create a new text field for display that is set to increase from 1 (replace with the original invoice number for the old records). You could still sort by the original key. It might be a pain to have to go back and reformat all those layouts, but I would be strongly against reseting your main key while leaving the current relational structure in place.

HTH

-Raz

Link to comment
Share on other sites

Hi Raz, :wink2:

Thank you for the input. I meant I could create a new serial field (and serialize them) to use in sorting etc. Oh. I surely see your point about the 100000. And that will only be in another 3-4 years!! I can always sort by other fields instead. I would have some scripts to change.

Alright ... confession ...

Owner (few weeks ago) had said (when I told him no on something he wanted) that, "Well, Alpha-5 can do it." It twirked me a bit because so could FM - but it wasn't a good idea in the overall solution (which I then explained to him). So, when he asked me in Management meeting, I said I would consider the implications and let them know. Uhm, but then I said, "FileMaker CAN start the numbers over. And FileMaker can even remove the leading zeros for you. HA! I'll betcha Alpha-5 can't do THAT."!!

:blush2:

So here I am ... [color:green]REALLY wanting to pull it off now. :giggle:

L

Link to comment
Share on other sites

I can always sort by other fields instead. I would have some scripts to change.

I am afraid I wasn't too clear in my first reply. I meant that I would keep the invoice number field the same, and keep counting from 256251. You could still sort by this field, and most likely keep all your existing relationships and scripts. Just create a new display field that is reset to 1, so the owners can play with it all they want and not interfere with the structure.

"Well, Alpha-5 can do it."

Love it. I think that will be my new reply to all posts that ask how to implement poorly thought out ideas.

Link to comment
Share on other sites

"Just create a new display field that is reset to 1"

Except our Reps give this number out as our invoice number. All reference throughout the business uses it. And they search for invoices. InvoiceNumber is used to filter many portals. I would need to change everything (relationships, searches) to include BOTH fields, right?

:idunno:

Anyone want to come to my crow-eating party? :smile2:

Link to comment
Share on other sites

mmmmmmm, crow.

Except our Reps give this number out as our invoice number. All reference throughout the business uses it. And they search for invoices.

Unless I am missing something here, this should not matter at all. This new field would in fact be your invoice number (NewID from here on), just not your relational key. The old invoice number field (OldID from here on) would remain your key (so you do not have to adjust all of the relationships). Since NewID would have the value from OldID replaced in it for the old records, all your users could search it using a single field.

As far as I can imagine, most relationships could still rely on OldID alone, as it will be unique - still serial incrementing from the old value.

You are right, filtered portals are another matter. However, you could keep them in place and just change your filter key into a stored calc that returns the OldID for whatever value the user enters in a newly created FilterByNewID field (or just keep the original setup and use a value list that shows newID but returns oldID in the filterkey).

Hard to say how much of a hassle it would be without knowing the details, but I think it might be easier than you suspect.

Link to comment
Share on other sites

This topic is 6514 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.