Looking up next available serial number

[deego55]

Hi. I have a solution where i need a to add an authorization number field in a table. the valid numbers are stored in another table. when i create a new record in the master table i need to assign the next available number in the related table that has not been used. this needs to be scripted so i can create a 'Add Authorization Number' button.

i assume this is easy to do, but i cant figure it out.

[comment]

How many authorization numbers can a record in the main table have?

[deego55]

a record can have multiple authorization numbers. probably never more than 4 would be required.

[comment]

Try it this way.

AuthNumber.fp7.zip

[deego55]

thanks comment. this is very close to what i need. the difference is that my auth number table will already be populated with the numbers (and they are not necessarily sequential). so i need my main table to look at the auth table and choose a number that has not been added to a record yet. does that make any sense? thanks!

[comment]

That should be perhaps even simpler.

AuthNumber.fp7.zip

[deego55]

I think that is it! Thank you so much!

[deego55]

One more question. how would i set it up so that if a user adds a number to a record in the main table and then removes it, it will not be removed from the authnum table, but the authnum table will reflect that the number is available to use again?

if i delete an added number in the main table, it also deletes it from the authnum table. i dont want to delete the number from the auth table, just change the available count back to 1.

thanks again!

[comment]

Define a script:

Freeze Window

Set Field [ AuthNumbers::MainID; "" ]

Commit Records/Requests

Create a button to run the script, and place it inside the portal. Clicking the button will free up the authorization number, making it available for future asignments.