11 Replies Latest reply on Aug 16, 2011 12:28 PM by philmodjunk

    Adding running number to a record from value list?

    FarfelKnabe

      Title

      Adding running number to a record from value list?

      Post

      Hi all,

      Since I am novice when it comes to scripting I have no clue on where to start at. But here is my idea!

      I want to add a running number, a prefix if you will, to new records based on the last used prefix from the previous record.

      I have a table with tv series episodes (my collection of tv series) and whenever I am adding a new series (or a new season to a existing series) to the database I am manually adding all the episode titles to the this table. I have a system of a prefixes to every episode title based on season and episode number - e.g. Season 01 and Episode 01... (This table is then linked with a portal to the main table. The post for the Series is in the main table and only the episodes is in the sub table.).

      01.01
      01.02
      01.03
      01.04
      and so on...

      02.01
      02.02
      02.03
      02.04
      and so on...

      this prefix is followed by the Episode Title so the whole field would look like this

      01.01 - Episode Title

      Now I manually enter the prefixes and seperator on every new record before I enter the Episode Title.

      I would like to have some kind of calculation that automagically fills out the prefix for Season# and also have the prefix for Episode# filled out based on what the last record used.

      But the Season# only use the same value from the last record while the Episode# uses the next value from the prefix list!

      Can this be done or is this to newbie for me?

       

      /Andreas

        • 1. Re: Adding running number to a record from value list?
          philmodjunk

          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 )

          • 2. Re: Adding running number to a record from value list?
            FarfelKnabe

            Hi Phil,

            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!

            Thanks

            /Andreas

            • 3. Re: Adding running number to a record from value list?
              philmodjunk

              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.

              • 4. Re: Adding running number to a record from value list?
                FarfelKnabe

                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.

                 

                /Andreas

                • 5. Re: Adding running number to a record from value list?
                  philmodjunk

                  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.

                  • 6. Re: Adding running number to a record from value list?
                    FarfelKnabe

                    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 :)

                     

                    /Andreas

                    • 7. Re: Adding running number to a record from value list?
                      philmodjunk

                      "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

                      • 8. Re: Adding running number to a record from value list?
                        FarfelKnabe

                        Hi Phil,

                        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#

                        and

                        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).

                         

                        /Andreas

                        • 9. Re: Adding running number to a record from value list?
                          philmodjunk

                          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.

                          • 10. Re: Adding running number to a record from value list?
                            FarfelKnabe

                            Phil,

                            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!

                             

                            /Andreas

                            • 11. Re: Adding running number to a record from value list?
                              philmodjunk

                              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.