Difficulty with expression for calculation

[TeoC]

Hello, beginner level here, and I'm hoping this is maybe something easy? I'm grateful for any help.  In a basic scheduling database, I have a table of unique Events, that each each record has a unique ID and Date (EventDate), and other fields connecting to another table of possible "Names of Events".  I want to create an additional field in this Events table with an auto-entered serial number, starting at 1, that represents each event (record), that occurs on a new (unique) date.  I keep thinking, it's a conditional statement that says "If the date in EventDate is Unique, then the auto enter value is 1. If it's the first duplicate found of EventDate, then the value is 2, and so on... I'm calling this the DailyEventID.

If there's 29 events in a given day, the DailyEventID for the last record with that date is 29 and then on the next day, sets back to 1. 

I'd be grateful for any help!

[comment]

5 hours ago, TeoC said:

I'm hoping this is maybe something easy?

Unfortunately, it is not - at least not the way you present it. The question is why you need this, and where. 

It is very easy to number records in a group within a report; all you need for this is a summary field defined as running count with restart. However, such numbering is produced "on-the-fly" and depends on the current found set and sort order.

It is also possible to define a relationship that would allow each event to count the preceding events occurring on the same date. Such numbering would work at the data level, independently of the current found set and sort order. However, if you delete a record, the remaining records in the same group will renumber themselves in order to maintain a consecutive series - unlike the auto-entered serial number you describe.

For a permanent numbering within each group you would need to use a scripted mechanism - a mechanism that would prevent the generation of duplicates. But I doubt you really need this.

 

[TeoC]

Hi Comment, thanks for your reply. Since these numbers only represent the number of events that happened on a given day– the serial number could be something subject to change, and that would be fine. Basically, in a layout called "ThisDay", I want to show the user a list of the events planned for that day, and list them in order of start time.  For example:  #1: "Book Discussion with Bob" at 2pm;  #2: "Film Analysis with Fran" at 2:30pm; #3: "FileMaker Basics at 3pm".

If The 2:30pm event gets cancelled and deleted, then it would actually be ideal if the DailyEventID #2 gets reassigned to the 3pm event and so on for everything forward of that. So the option you described, to provide a count of how many other events are scheduled for the same day, and then number them according to creation order, sounds like it would work. I realize that basing it on creation order, would not consider the scheduled times, but that's ok. This is a pretty low-level tool for internal use that's just for assisting in planing event. 

Any help you could offer would be very much appreciated!

[comment]

45 minutes ago, TeoC said:

I want to show the user a list of the events planned for that day

If you are only showing events for one day at a time (i.e. no need to restart the numbering at day boundary) then you can simply insert the record number symbol onto the layout:
https://help.claris.com/en/pro-help/content/inserting-variables-on-layout.html?Highlight=record number symbol
This will number the shown records dynamically according to their sort order. No need for any calculation or even field.

If you want to show more than one day, then use the summary field I described earlier.

 

[TeoC]

And... That was beautifully simple! The RecordNumber symbol works great for this simple need. Thanks so much!