Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

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

Recommended Posts

Posted

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

Posted

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

Posted (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 by Guest
Posted (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 by Guest
Posted

>> 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

Posted (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 by Guest
Replaced file, I think
Posted (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 by Guest
Posted

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.

Posted

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

Posted

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.

Posted

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

Posted

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

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