Assign new serial number

[netcode]

Maybe I can post this question within this thread and get a response, it is totally unrelated to creating a new record through, just didn't want to create another thread.

I have scripted up a script to assign a number to a record (a comp number to a person). I have the Max function (hopefully, which it is not) grabbing the last number used and incrementing it by one. However when I hit the button on the record with no comp number assigned to it the script never works. However the script would work if I ran the script from a record that already has a comp number. I think it is not working because the new record with no comp number does not have a relationship with the comp number table, however isn't Max supposed to find the last relationship?

Thanks!

[LaRetta]

...it is totally unrelated to creating a new record through, just didn't want to create another thread.

The answer is ... create a new thread (I have split it for you on this one). We do not want unrelated topics pinned onto the ends of other threads; that wouldn't help anyone looking for the same answer you are asking. :wink2:

[LaRetta]

Now to your question ... why are you not using the auto-enter serial number instead? When in multi-user mode, using Max() and most other scripted methods, will fail at one time or another.

If you wish to post your process we can attempt to tell you specifically why YOURS breaks (there are ways around some of the problems) but the real answer is not to use it at all.

[netcode]

Thank you for making the new post LaRetta, its really nice having so much help around here from everyone

So onto the dirty stuff.. maybe I will re-explain my situation in a little more detail.

Structure:

I have a table of "Films". I have a table of "Comp Numbers". The relationship is Comp Num in the Films table = Comp Num in the Comp Numbers table. Films table holds film information. Comp Numbers holds a list of roughly 300 comp numbers

Situation:

Films are submitted and entered into the database. Some films are accepted, some are not. When a film is accepted it needs to have a comp number assigned to it. So I created a button labeled "Assign Comp #" and the user (not me) will press that button and it will grab a comp number from the Comp Numbers table and assign it to the Comp Num field in the Films table.

Script (as is, that doesn't work):

*hmm I can't seem to copy and paste*

If [not isEmpty ( Films::Comp Num)]

Show Custom Dialog ["Comp number already assigned"; "This record already has a comp number. Do you want to assign a new one?"]

If [Get ( LastMessageChoice ) = 1]

Exit Script []

End If

End If

Set Field [Films::Comp Num; Max (Comp Numbers::Comp Num)]

Research:

It seems that the function Max(Comp Numbers::Comp Num) will return the last number used IF and only IF I hit the assign button on a record that already has a comp number assigned to it (a relationship exists). however when I hit the assign button on a record that doesn't have a comp number (doesn't have a relationship) the Max function doesn't return anything.

Hopefully that gives you some more information

I got to thinking that I could just assign every film a comp number in one process and the solution would be solved (as the user will ultimately in the end decide if that comp number is sent to the filmmaker or not). But my curiosity drives me to find out why this doesn't work.

[comment]

That's not very clear. What are these comp numbers, and what do they represent in real life? And what happens when one wants to accept a film after all the comp numbers have been used (IIUC, only one film can be assigned to a comp number)?

[netcode]

How is that not clear? The situation is described and what I want is described, very clearly.

but for sake of satisfying your questions, comp meaning complimentary. We comp people's payment and when they register through the online web form they use the comp number so it allows them to pay a lower cost. This part doesn't interact with the database at all and has no effect on what I am trying to do.

There is more comp numbers in the Comp Numbers table than there is accepted films. This was done so they wouldn't run out. :)

[comment]

I am not asking these questions to satisfy my curiosity. I still cannot visualize the entire process from your description. Maybe I am just not smart enough. If that's a problem for you, I'll move to something else.

[netcode]

Asking questions as to what you don't understand is productive...

Bashing yourself and acting like I have a problem with you (when I never said ANYTHING of sorts) is not...

Move on.

If anyone else can help that would be great, thanks!

[David Jondreau]

It seems that what you want to do is assign the first unused Comp Number to a Film that doesn't have one. And that it doesn't matter which Comp number gets used, as long as it is not already in use.

If a Film can only have one comp number and one comp number can only be associated with one film, then store the Film number in the Comp tabl (instead, or in addition to, storing the Comp # in the Film table).

Then create a calc field in the Comp table named Unassigned = IsEmpty(_f Film ID). You can relate that to the Films table using a constant (a calc field in the Films table =1). Whenever a Film gets Comped, you simply use the Comp ID from that new relationship.

PS It's generally a good idea to not be too sensitive when posting on a forum. comment gives more good advice than everyone else combined and his probing is based solely on good faith.

Edited December 9, 2008 by Guest

[netcode]

D J

Thanks for the suggestion.

I do have a few questions however:

what does the "_f" mean that sits inside the IsEmpty() function?

Also for that new calc field in the comp table, when does the IsEmpty() function calculate its value? During record creation?

This is certainly going in the right direction now, appreciate it!

Hope to hear back soon

-B

[David Jondreau]

The _f is just part of the name of the field; it's the way I notate that it's a key field.

The IsEmpty() calculates whenever the Film ID is created or modified. It's always current.

[netcode]

Ok... so I just implemented what you advised. I change the max function to the Min function for organizational purposes. It now pulls the next unused number. So that's great

The big thing was my misunderstanding of the use of the Max (and Min) functions and how their has to be a relationship to every unused comp number.

Anyways problem solved.

Thanks for the help DJ, LaRetta, and Comment.