Serial Numbers

[valhalla]

I could have sworn I posted this yesterday but I cant seem to find it so if it's a dupe please disreguard, if not here is my problem:

I have a database with auto entered serial numbers, the problem is I need to be able to enter numbers out of order as well as have the auto entered serial number pick the next free number. So far the auto enter works fine and I can enter an out of order number just fine, but when the auto enter gets to an out of order number that already exists it will still pick that number, I need it to skip numbers already in use... is this possible.

Thanks.

[RalphL]

You are treading in danerous waters.

If you script the new record creation you might do something like this.

New Record/Request

If [ Count ( SerialNumber Check::SerialNumber ) > 0 ]

Delete Record/Request

New Record/Request

End If

If you had 2 in a row this will fail.

[valhalla]

There will definatly be two or more in a row most of the time, due to the way the data has to be collected.

[RalphL]

Try this:

New Record/Request

Perform Script [ "New Record Check" ]

and this

If [ Count ( SerialNumber Check::SerialNumber ) > 0 ]

Delete Record/Request

Perform Script [ "New Record" ]

End If

[valhalla]

I will give it a try thanks.

[valhalla]

Hmm, I don't quite get how the check part works, it says table not found.

[RalphL]

You have to be in the layout for the scripts to work on the table.

The first script makes a new record with an auto-entered serial number. Then it goes to the second script.

The second script checks to see if the serial number has been used. If It has it deletes the record just created and runs the first script again. If the serial number is okay the second script does nothing. The second script is only to run when the first script calls it.

It worked OK in my sample file.

[-Queue-]

I would suggest using

If [ not IsEmpty(SerialNumber Check::SerialNumber) ]

which is faster than

If [ Count(SerialNumber Check::SerialNumber) ]

[valhalla]

OK Im assuming that all instances of SerialNumber are the field name (in my case Comp_ID)?

It still will not let me create (in script maker under specify calculation for the if statement) Comp_ID unless I also specify the table like Computers::Comp_ID it will also not allow Check::Comp_ID or Check::Computers::Comp_ID.

Any Ideas as to what I'm doing wrong?

[-Queue-]

I wonder if it would be better to do something like

Loop

Set Field [table::global; GetNextSerialValue( Get(FileName); "Comp_ID" )]

Exit Loop If [isEmpty(relationship::Comp_ID)]

Set Next Serial Value [table::Comp_ID; table::global + 1]

End Loop

New Record/Request

where relationship is from table::global to table1::Comp_ID, and table1 is a new TO of table.

This way, no new records need to be deleted.

[valhalla]

I dont suppose there is a way to extract all the values of Comp_ID into an array is there?

[-Queue-]

You could create a value list of them and use a repeating calculation to extract each one into its own repetition. But what would you want to do with them afterward? The GetNextSerialValue/SetNextSerialValue loop would probably be faster than any ValueListItems calculation with a repeating field, moreso if you have hundreds or thousands of records in the file.

[valhalla]

I dont quite get the creation of a new table part of that, why is it needed and what is TO?

{EDIT}

OK I think I have it sorted out...Im still a bit fuzzy on the relationship global etc... but I think I will get it.

Thanks

[-Queue-]

TO = Table Occurrence, basically an alias of a physical table, used for creating relationships between tables/files.

In the loop, I referenced a TO called "relationship" or "table1" (you can call it whatever you like) which is a self-relationship (from the current table to itself) from a global to the Comp_ID field. When you create a self-relationship, FileMaker will force you to provide a different name for the related table occurrence so that the relationship is obviously distinct. It becomes both the alias for the original table and a distinct name for relationship from the original table to itself. Whenever you reference this new TO in calculations, on a layout, etc., you are referring only to related records based on THIS particular relationship's definition.

I used this TO in the loop to test whether the next serial value set into the global had any related records, meaning whether there were any existing records with a Comp_ID equal to the value in the global, i.e. a duplicate. If there are none, then the loop exits and creates a new record with the next serial. If there are related records, then the serial is incremented and tested again.

Does that help a little?

[valhalla]

OK i'm pretty sure I understand it thanks. But I still have trouble entering it into script maker...

so far:

Set Field [table::global; GetNextSerialValue( Get(FileName); "Comp_ID" )]

If I enter a Set Field [] it will allow me to enter either Set Field [mytable::myglobal] ro a calculation result, "table::global; GetNextSerialValue( Get(FileName); "Comp_ID" )" is not accepted as a calculation.

[-Queue-]

table::global is the 'Specify target field' portion of the step. GetNextSerialValue... is the 'Calculated result' portion.

[valhalla]

Ahhh!, (Lighbulb goes on!)

Much Appreciated.

[valhalla]

OK I think I have it working, but there are 2 bugs so far. The first is because of my stupid serial numbers that take the form of C0000 - C9999. The script will add new serials instead of say C0401 it will just put 401, I guess the easy way to fix this is to ammend my serial numbers to be regular numbers and add the C to another field.

The other problem is that if i set in the field definistions the next serial to be say 100 it will create the next record as 100 but the second will be 450 or whereever I last left it...does any of this drivel make sense?

[-Queue-]

No sweat. Instead of

Set Next Serial Value [table::Comp_ID; table::global + 1]

use

Set Next Serial Value [table::Comp_ID; "C" & Right( "000" & GetAsNumber(table::global) + 1; 4 )]

Make sure that global and Comp_ID are text fields.

As far as the 100/450 concern, I wasn't able to replicate the problem. If it continues after you make the changes, zip your file or a clone of your file and attach it.

[valhalla]

Thank you, You are a life saver! The second problem I think is sort of fixed, it seems only to happen when the first number I set it to is already taken, so there should be no problem as I will just check before I set it.