Serial numbers are integers. If your table requires serialisation for the main events and sub-events, consider using a number field with its own serial number for each.
LEading zeros are a visual aid for human reading. A calculated text field can hold the serial number with leading zeros, formatted as desired.
Your increment is 1, not .0001
The function use right most numeric char's part as integer. Period is ignored, not used as decimal pointer.
The Serial Number (and any other "straight" serialization method you use) won't work for what you're wanting.
It sounds like you might have:
With sub events for that event, numbered as follows: 1001.0001, 1001.0002
And your next Event might be:
With sub events numbered as follows: 1002.0001, 1002.0002
The way I read it, each event could have a sub event which was the first sub event (.0001) for that event.
Are you using an Events table, and a Sub Events table? Or are you using the Events table for both, and some events are actually "sub" events?
Either way, you'll need to know the count of the sub events for an event. Assuming you have a script of some sort which adds a sub event to an event, the script would get the count, set a variable, then the script would set the serial number as you describe. Assume you have Event Primary Key = SubEvent Foreign Key, and you want a field called "SN" to be populated in whatever table you're using for SubEvent.
Your script does:
Set Variable $SubEventCount = Count ( SubEventTableOccurrence::PrimaryKey )
Set Field SubEventTableOccurrence::SerialNumberReferenceField [ MainEventTable::EventNumber & "." & Right ( "0000" & $SubEventCount + 1 ; 4 )
The part with "Right ( "0000"..." first creates a text string such as "00005", then takes only the four rightmost characters (0005). I'd point out that after 9999 subevents for a project, this method will fail, but I doubt you'll ever have that many subevents.
You're making it too complicated. Primary Key fields - the auto-enter serial numbers - should be absolute. They should not have meaning. In each table, use a standard integer serial number or a UUID as you prefer. Then do as dtcgnet said above - use a script to get the count and create the display number that you want. Storing the "next available" sub-number in the main event record is an option that prevents duplication in the event of deleted, voided or cancelled records in the sub table (if you allow any of that).
You are correct
Main Event - SN- 900
Sub-Event would be 900.0001
Think an explanation might help..
This is for school so imagine the main event is a course for the semester (two semesters per year). Professor wants a projector every class (SN 900). Then three weeks into the semester he wants a microphone for one class (900.0001). Then later still he wants a widget (900.0002) and so on.....
By going out to four places (.####) I am ensuring that we don't roll over to have SN become 901.
And for clarity- I have a SN for the record that handles all of the other stuff(relationships ect..) just trying to make it easier on the faculty and staff to remember.
I like rivet's method quite a bit, and the example file is a good example, too. My formula used the count of related records, which could cause a problem if, for example, a sub event record got deleted. For example: sub events 0001, 0002, 0003, 0004 would have a count of 4. The next number should be 0005, but if one of 1, 2, 3, or 4 got deleted, then the next number would be 0004 instead of 0005. The same problem could happen to the rivet method, but only if the LAST sub event record got deleted. Brian Winslow Smith's method of keeping track of the next available sub-event number in the main record would solve that OR you could just de-activate sub-event records rather than deleting them (which would then mean any of the three methods would work).
question- in the example you provided, and looking at the relationships tab, you have the subevent linking to itself (subevent_related). Why?
And I have it sort of have it working the way you outlined but the numbering sequence is not restarting with each new event.
So instead of 1002.001 I have 1002.004 (if I had three sub-event records in another event)
event is the parent
subevent is the child
subevent_related are the siblings
when a new subevent is created it needs to know if it has any 'siblings' and if so what is highest serialN amongst them.
If there are any siblings it finds the max sibling serialN and adds 1
if there are no siblings is starts at 1
think i understand, but still having issues. I took your auto creation calculation for the serialN in subevent
Right ( "0000" & Max ( subevent_related::serialN ) + 1 ; 4 )
and swapped my field in its place
Right ( "0000" & Max ( AV Project_subevents related::av_projects_subevent_event_sn ) + 1 ; 4 )
when i generate a new sub event it populates everything correctly (and shows up in the portal) except the "subevent_event_sn" field which is empty.
I verified that its a number field all the way through and for my own sanity i got rid of the calculation and just had it integer up and it worked. But again the numbering transgress into the other events/sub-events.
Post a screen shot of your relationship graph. It'll be worth 1000 words.
Sent from my iPhone
in the calculation window bottom left make sure "do not evaluate…" is unchecked