Silvertop Posted August 3, 2006 Share Posted August 3, 2006 How would I script the following? I have a field 'code' [number,unique], which contains 5 digit numbers. between 10001 and 90000. At the moment less than half are being used. i.e. 10001 10002 12589 48758 56412 etc. So how can I extract a list of the numbers NOT being used? Thanks John Link to comment Share on other sites More sharing options...
Darryl W Posted August 3, 2006 Share Posted August 3, 2006 You could do it this way: Create a global text field, eg. number_list, and another global number field - current_number In your script: Use a loop to go through each record in your file start current_number at 10001 check to see if it equals your serial field if it does - set field number_list to equal number_list & a return & current_number increment by 1 do the loop until 99999 The number_list text field should then be filled with a list of all the numbers not used. Hope this helps. D@rryl Link to comment Share on other sites More sharing options...
Silvertop Posted August 7, 2006 Author Share Posted August 7, 2006 (edited) This is my first ever attempt at a loop, based on the above. Sadly, I cannot get it to work. Any help appreciated. John --------------------- Enter Browse Mode [] Show All Records Sort Records [Restore; No dialog] Go to Record/Request/Page [First] Set Field [Link::gcurrent_number; 10000] Loop Set Field [Link::gcurrent_number; Link::gcurrent_number + 1] If [Link::gcurrent_number = Link::Code] Set Field [Link::gNotUsed; Link::gcurrent_number + (a return - I can't get the return symbol to show)] Go to Record/Request/Page [Next] Exit Loop If [Link::gcurrent_number = 99999] End If End Loop Edited August 7, 2006 by Guest Link to comment Share on other sites More sharing options...
Lee Smith Posted August 7, 2006 Share Posted August 7, 2006 (edited) A [color:red]Flag just went up, and before you change anything with your numbers, we might save you some headaches if you tell us a little more about these numbers. What is the purpose of these numbers. (Serial Numbers, Client ID, Invoices, Record, etc.) In other words, how are these Unique Numbers being used? How come there are big gabs in the sequence? How are these Numbers being created? Are you using FileMaker's ability to create numbers in sequential order? Or, do you have some other schema for their creations? HTH Lee Edited August 7, 2006 by Guest Link to comment Share on other sites More sharing options...
Silvertop Posted August 7, 2006 Author Share Posted August 7, 2006 >> we might save you some headaches I'm all in favour of that. >> What is the purpose of these numbers They are basically Membership Numbers. For years a new member has gotten a new number. But they just created additional numbers all the time, by telling the printer to print membership cards with the next block of 10,000 numbers. The actual DB is about 6,000 +, but they are running out of numbers (I think some blocks of numbers just 'got lost'). >> How come there are big gabs in the sequence? How are these Numbers being created? members come, members go. These numbers are not being created by Filemaker, just entered by the user. I want to keep the membership number in the original 5 digit range, by using some of the obsolete numbers, hence the question. John Link to comment Share on other sites More sharing options...
Fenton Posted August 7, 2006 Share Posted August 7, 2006 (edited) I can think of an easy way to do it if you use a self-relationship, on the global field to the number. If the self-relationship is empty, you don't have the number. You don't have to loop through the records, as a relationship automatically looks at all the records of its target table. You just have to increment the global, and exit when it gets to the last record's number. NumbersNotUsed.zip Edited August 7, 2006 by Guest Replaced file, I think Link to comment Share on other sites More sharing options...
Silvertop Posted August 7, 2006 Author Share Posted August 7, 2006 (edited) Fenton I am getting close, but as yet cannot get it to work. 'Code' is the number field in my DB(Link). I have created fields gCode, gNumberLast, gNotUsed and the following script. I have also created the relationship self_gCode..gCode to Link..Code. Freeze Window Go to Layout ["unused" (Link)] Show All Records Go to Record/Request/Page [Last] Set Field [Link::gNumberLast; Link::Code] Go to Record/Request/Page [First] Set Field [Link::gCode; Link::Code] Set Field [Link::gNotUsed; ""] Loop Set Field [Link::gCode; Link::gCode + 1] If [isEmpty ( self_gCode::gCode )] Set Field [Link::gNotUsed; Case ( IsEmpty ( Link::gNotUsed ); Link::gCode ; Link::gNotUsed & ¶ & Link::gCode)] End If Exit Loop If [Link::gCode = Link::gNumberLast] End Loop Thanks John PS is there a way to copy/paste a script? Edited August 7, 2006 by Guest Link to comment Share on other sites More sharing options...
Fenton Posted August 7, 2006 Share Posted August 7, 2006 I see an omission in my file, which didn't seem to matter, but should be there: Set Field [Link::gCode; Link::gCode + 1] Commit Records <-- Add This is what is wrong with yours: If [isEmpty ( self_gCode::gCode )] You want: If [isEmpty ( self_gCode::Code )] In other words, check the regular field. The global field is accessible ALWAYS, even if the relationship is not valid. So it is never empty, so the Set Field never happens. Also, make sure all _g fields are global. _gNotUsed must be a text field, or it can't show the multiple IDs separately. Link to comment Share on other sites More sharing options...
Silvertop Posted August 7, 2006 Author Share Posted August 7, 2006 My apologies - I must be a bit dim. Still unable to get this to work. g fields are global. gNotUsed is a text field. No data in gNotUsed self_gCode::Code does not appear to change? Latest Script... Freeze Window Go to Layout ["unused" (Link)] Show All Records Go to Record/Request/Page [Last] Set Field [Link::gNumberLast; Link::Code] Go to Record/Request/Page [First] Set Field [Link::gCode; Link::Code] Set Field [Link::gNotUsed; ""] Loop Set Field [Link::gCode; Link::gCode + 1] Commit Records/Requests [] If [isEmpty (self_gCode::Code )] Set Field [Link::gNotUsed; Case ( IsEmpty ( Link::gNotUsed ); Link::gCode ; Link::gNotUsed & ¶ & Link::gCode)] End If Exit Loop If [Link::gCode = Link::gNumberLast] End Loop John Link to comment Share on other sites More sharing options...
Fenton Posted August 8, 2006 Share Posted August 8, 2006 Code is a regular field, not global? Did you look at the Relationship Graph? Is the relationship the same as my file? I changed my names to match yours. The script looks the same. So I deduce you must have something different in the relationship. Link to comment Share on other sites More sharing options...
Silvertop Posted August 9, 2006 Author Share Posted August 9, 2006 Working fine now. What a neat script! I have though, 2 other related questions (below). (Oh dear, I had indeed got the relationships the wrong way round, [blush]). Thanks Fenton (and others). related question 1... To be really useful, I would like to be able to export the Link::gNotUsed field to a CSV file to be used in Excel. I tried File/Export/Save as type: Comma-Separated Text Files (*.csv)/gNotUsed.csv ... but I just ended up with a HUGE (600+Meg) garbled file. I think that the carriage returns are the problem? related question 2... Is it possible to allow the user to input a range (i.e. 10001 - 15000) to search for unused numbers? Sorry to go on... Thanks John Link to comment Share on other sites More sharing options...
Fenton Posted August 9, 2006 Share Posted August 9, 2006 You could use Export Field Contents from the global gNotUsed. I don't see much use in exporting as CSV. I don't know for sure if the line endings come out right in FileMaker 7 on Windows. It's working fine for me using FileMaker 8.5 on Mac; but I seem to remember there was some problems earlier. To search for a single number not used you'd use the self_gCode relationship. You can type any number you want in gCode, then see if the relationship is valid. To get numbers for a "range", you just need the same script as NotUsed, but without the Show All Records step. You just need to Find a range first (value1..value2), before running the script. Numbers_Not_Used.zip Link to comment Share on other sites More sharing options...
Silvertop Posted August 14, 2006 Author Share Posted August 14, 2006 Finally, it's all working now. Thanks again Fenton Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6465 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