I'd use three fields where you have one. Season, Episode# and EpisodeTitle. You can then combine these items on your layout as merge text or use a calculation field to combine the three in cases where you want to see the "01.01 - Episode Title" format.
Assuming that you have this relationship between your main table and your episodes table:
Series::SeriesID = Episodes::SeriesID
You can add a self join relationship to track the current max value for Episode#for the same season.
Episodes::SeriesID = EpisodesSameSeriesSeason::SeriesID AND
Episodes::Season = EpisodesSameSeriesSeason::Season
Create EpisodesSameSeriesSeason in Manage | Database | Relationships by clicking episodes, then clicking the button with two green plus signs. You can double click this new occurrence of Episodes to change it's name like I have here.
Then your espisode# field can be given an auto-enter calculation written as:
Max ( EpisodesSameSeriesSeason::Episode# ) + 1
Two settings are crucial for this to work: Clear the "Do not evaluate if all referenced fields are empty" check box in this calculation. And clear the do not replace existing values..." checkbox.
To add leading zeroes like you have, add calculation fields with this expression: Right ( "0" & Episode# ; 4 )
This sounds straight forward and yes my relations look like you think. I will try and impelemt this and will give you a note with the progress.
Maybe you can give some insights to another problem I have? I have the two main tables with each having it's own date field where the dates are stored in a third table with a relationship. I would very much like to have the same date table for both main tables (All Titles and Epsiodes). I have tried to do this but only get one single occurance to work in the relationship! Or rather that each related records in the Episode table gets the same date as the relationship dictates - to the main title record in the main table - not storing the different dates that the different episodes have!
I can't quite picture the issue. Can you walk me through how the two different tables need to use the date information? Perhaps you can give a step by step example of what you need to see happen with these dates in each table.
Table 1 = "All Titles", contains Movies and Series. Where Series is only the main information about the Series it self, where I bought it, price, main genre, main plot, poster image, country of origin and so on.
Table 2 = "Episodes", contains Episodes, which is all the underlaying episodes for a the Series. Here I store specific info about each episode such as; air date, plot, guest stars, running time, the different viewing dates for each episode and so on...
Table 3 = "Viewing Dates All Titles", where all the different viewing dates for Movies are stored.
Table 4 = "Viewing Dates Episodes", where all the different viewing dates for Episodes are stored.
I would like Table 3 and Table 4 to be the same table, so I only need to store the dates in one table.
My relations are based on TitleID# in both table 1 and table 2 and the date table has it's own serial number each.
I get the relations to work as it is now with out any problems.
But when I try to use only one table I can't get the relations to work. I only get the first date entered into the first episode to appear in all the other episode belonging to the same serie!
Both table 1 and table 2 has portals to it's own date table within each "main table". Also the table containing Episodes is accessed via Portal from main layout of Table 1.
I don't know if I can make it any more clear than this! I am grateful for any assistans though.
What information do you store in these dates tables besides the date viewed?
If it's just the date viewed, you don't need either table, you can just log the date in a date field in each table.
I would like to have the opportunity to see what titles are most viewed and the likes, therefor the date table.
But I got an idea about changing the whole layout of my database so I am thinking on something other than the structure explained above...
Thanks for the help Phil :)
"I would like to have the opportunity to see what titles are most viewed and the likes, therefor the date table."
In which case you'd have three fields in your table: MovieOrSeriesTitleID ; EpisodeTitleID ; DateViewed
Thanks for your help earlier.
I have tried to figure your suggestion out but can't quite understand it!
Maybe this is because I wanted to have this calculation in my in my test database, where I have now two table looking like this;
Main Titles - containing a post for every main title in the collection (be it movie or serie or if I watched the movie at the cinema or I have then bought the soundtrack or other items associated with the Main Title).
All Titles - containing records related to the first table (the movie or/and episodes) (here main titles can have many occurances in this table - DVD, Blu-ray, VHS, Poster, Soundtrack etc...)
So I have two tables;
Main Title with MainTitleID#
All Title with AllTitleID#
It is in the the All Titles table I have fields for Season# and Episode # (it's own tables with drop-down menus).
Yet you would still need to create a record where you you record what you watched and when. The "what" will be a movie or it a Series and Episode. The "when" is your date field.
This will require a table linked to All TItles by both Episode and Title ID fields. You may find it easier to implement, if you handle movies as though they are a series with only one episode when it comes to relating it to the Viewing Log table.
With the relationship in place, you can use a portal to the "viewing log" table on your All Titles layout to log each time you view that movie or series episode.
I got the "what" relation to work, finally, thanks for that help :)
But the first issue with the 01.01 prefixes is still a bust. I have at least gotten the right data into the right fields now. I moved the "old" prefix from the Title field of the episodes (with the help of some scripting), into two new tables, season# and episode#.
Now I am stuck at making the relation work and also the calculation. But I think I can handle the calculation if I just get the relation to work. I kinda got it to work, but it only assigns the first 01 and 01 to each new record. And also when I run the script again in the same field (same record) it adds up the result - changing 01 to 02, then 03, then 04 and so on... each time I run the script!
I've suggested an auto-calculation--not a script. It requires a specific set of options to select in order for it to work. Please read that post again and make sure that you have correctly specified each option.