FruitFul Posted March 19, 2005 Author Posted March 19, 2005 Greetings, I have a need for a five-digit number, that always displays all 5 digits. So I have it initially generating as a serial number (00000 +1), which is working great. However there are occasions when I want to duplicate records, and keep this value the same. I made a global field to store the old value, and then just set the field on the duplicate via a script - but it is dropping the zeros when it displays it on this new record (which is not ok). Question 1 - Is there a way to force it to display any front end zeros if it is not 5 digits on its own? Secondly... It is quite common that these numbers are being changed to other values that already exist (this is ok) - but I realize that the serial generation will hit 100,000 all to soon. Question 2 - Is there a way to script something like: *There is a hole between 00031 and 00033, so lets make the next number 00032 instead of 01769* Thanks!
FruitFul Posted March 19, 2005 Posted March 19, 2005 Greetings, I have a need for a five-digit number, that always displays all 5 digits. So I have it initially generating as a serial number (00000 +1), which is working great. However there are occasions when I want to duplicate records, and keep this value the same. I made a global field to store the old value, and then just set the field on the duplicate via a script - but it is dropping the zeros when it displays it on this new record (which is not ok). Question 1 - Is there a way to force it to display any front end zeros if it is not 5 digits on its own? Secondly... It is quite common that these numbers are being changed to other values that already exist (this is ok) - but I realize that the serial generation will hit 100,000 all to soon. Question 2 - Is there a way to script something like: *There is a hole between 00031 and 00033, so lets make the next number 00032 instead of 01769* Thanks!
FruitFul Posted March 19, 2005 Author Posted March 19, 2005 Greetings, I have a need for a five-digit number, that always displays all 5 digits. So I have it initially generating as a serial number (00000 +1), which is working great. However there are occasions when I want to duplicate records, and keep this value the same. I made a global field to store the old value, and then just set the field on the duplicate via a script - but it is dropping the zeros when it displays it on this new record (which is not ok). Question 1 - Is there a way to force it to display any front end zeros if it is not 5 digits on its own? Secondly... It is quite common that these numbers are being changed to other values that already exist (this is ok) - but I realize that the serial generation will hit 100,000 all to soon. Question 2 - Is there a way to script something like: *There is a hole between 00031 and 00033, so lets make the next number 00032 instead of 01769* Thanks!
BobWeaver Posted March 20, 2005 Posted March 20, 2005 You can add the leading zeroes back in with the following calculation: Right("00000"&SerialNo;5) I don't recommend using a script to fill in gaps of missing serial numbers, for a number of reasons. If you are running out of serial numbers, then I suggest that you just go to a 6 or 7 digit serial number. You can use a formula similar to the one above in the Replace command to update all your serial numbers to more leading zeroes all at once.
BobWeaver Posted March 20, 2005 Posted March 20, 2005 You can add the leading zeroes back in with the following calculation: Right("00000"&SerialNo;5) I don't recommend using a script to fill in gaps of missing serial numbers, for a number of reasons. If you are running out of serial numbers, then I suggest that you just go to a 6 or 7 digit serial number. You can use a formula similar to the one above in the Replace command to update all your serial numbers to more leading zeroes all at once.
BobWeaver Posted March 20, 2005 Posted March 20, 2005 You can add the leading zeroes back in with the following calculation: Right("00000"&SerialNo;5) I don't recommend using a script to fill in gaps of missing serial numbers, for a number of reasons. If you are running out of serial numbers, then I suggest that you just go to a 6 or 7 digit serial number. You can use a formula similar to the one above in the Replace command to update all your serial numbers to more leading zeroes all at once.
bikergeek Posted March 20, 2005 Posted March 20, 2005 Q2: The first thing that comes to mind to check if a value is in use is to make a value list that is based on that field, and then use a script to create new records and populate that field with a loop that increments a number starting at one and exits, using that number, only if it does not appear in the value list: New Record Set Field [ global; 0 ] loop Set Field [ global ; global + 1 ] Exit Loop If [ position ( right("0000"&global;5) ; valuelistitems(get(filename);"myserials") ; 1 ; 1 ) > 0 ] End Loop Set Field [ serial; global ]
bikergeek Posted March 20, 2005 Posted March 20, 2005 Q2: The first thing that comes to mind to check if a value is in use is to make a value list that is based on that field, and then use a script to create new records and populate that field with a loop that increments a number starting at one and exits, using that number, only if it does not appear in the value list: New Record Set Field [ global; 0 ] loop Set Field [ global ; global + 1 ] Exit Loop If [ position ( right("0000"&global;5) ; valuelistitems(get(filename);"myserials") ; 1 ; 1 ) > 0 ] End Loop Set Field [ serial; global ]
bikergeek Posted March 20, 2005 Posted March 20, 2005 Q2: The first thing that comes to mind to check if a value is in use is to make a value list that is based on that field, and then use a script to create new records and populate that field with a loop that increments a number starting at one and exits, using that number, only if it does not appear in the value list: New Record Set Field [ global; 0 ] loop Set Field [ global ; global + 1 ] Exit Loop If [ position ( right("0000"&global;5) ; valuelistitems(get(filename);"myserials") ; 1 ; 1 ) > 0 ] End Loop Set Field [ serial; global ]
comment Posted March 20, 2005 Posted March 20, 2005 Quote ... made a global field to store the old value, and then just set the field on the duplicate via a script - but it is dropping the zeros ... Which indicates that the global field is of type number. Change it to text type, and it will stop dropping the zeros. Ideally, the initial serial number field should be text type as well.
comment Posted March 20, 2005 Posted March 20, 2005 Quote ... made a global field to store the old value, and then just set the field on the duplicate via a script - but it is dropping the zeros ... Which indicates that the global field is of type number. Change it to text type, and it will stop dropping the zeros. Ideally, the initial serial number field should be text type as well.
Oldfogey Posted March 21, 2005 Posted March 21, 2005 I agree with Bob, (who would dare not to!?) Trying to find gaps in a large block of numbers is not very efficient. I think biker's script is going to take so much time it's not worth the trouble. When you specify just five digits, I suspect a 'system' for races or lotteries or whatever. In which case you probably won't tell us what you are trying to do. This is a pity because I feel your basic approach might be a little off-track. You could set up a separate table with the same set of serials, related back to the base table by serial. In the second table, create a field called 'Fred' = Case(Relship, 0, 1) Then Fred contains 1 if its serial is not in the prime file. In your prime table, set up a relationship to Fred, using a constant field, valued at 1. You can then display, in a portal in the prime file, a list of serials that exist in the second file but not in the prime file - i.e. the 'missing' serials. Sorry that's not as clear as I'd like. As I said, I think you really need to re-think the basics.
Oldfogey Posted March 21, 2005 Posted March 21, 2005 I agree with Bob, (who would dare not to!?) Trying to find gaps in a large block of numbers is not very efficient. I think biker's script is going to take so much time it's not worth the trouble. When you specify just five digits, I suspect a 'system' for races or lotteries or whatever. In which case you probably won't tell us what you are trying to do. This is a pity because I feel your basic approach might be a little off-track. You could set up a separate table with the same set of serials, related back to the base table by serial. In the second table, create a field called 'Fred' = Case(Relship, 0, 1) Then Fred contains 1 if its serial is not in the prime file. In your prime table, set up a relationship to Fred, using a constant field, valued at 1. You can then display, in a portal in the prime file, a list of serials that exist in the second file but not in the prime file - i.e. the 'missing' serials. Sorry that's not as clear as I'd like. As I said, I think you really need to re-think the basics.
FruitFul Posted March 21, 2005 Author Posted March 21, 2005 Here is the general concept of what I am trying to do: I have a bunch of clients, and I currently track them with a 5-digit number (has worked great and I don't want to change this). Within Filemaker I want to be able to have a seperate record for each family member (or member of a business) in a contacts table (previously the whole family has been one record, less than ideal). At the same time, I want to keep family (business,etc) records as part of the same 'group' with the group carrying this 5-digit number (vs a seperate one for each individual) for invoices etc. I also have a portal at the bottom of the contact page showing just records of that family 'group', so that you can jump back and forth easily. Still lots of quirks, and I am wondering about how to bring it all together (also some concern about duplicate records).
FruitFul Posted March 21, 2005 Author Posted March 21, 2005 btw - Thank you comment, that fixed that issue
Recommended Posts
This topic is 7280 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 accountSign in
Already have an account? Sign in here.
Sign In Now