2 Replies Latest reply on Dec 18, 2012 2:52 PM by lauriehughes

    relating comma separated data in a field

    lauriehughes

      Title

      relating comma separated data in a field

      Post

           Hi,

           I have a situation where we have a film and we track the playouts we send out in FMP.

           The film is broken into parts and each part has a variety of scene numbers.

           I make a record per playout and in a field called "scenes used" we have listed all of the scenes that apply to that playout. This field generally needs to contain more then 1 scene number so I separate them with a comma. eg if a playout was from scene 1 to 20 my "scenes used" field looks like this ...1,2,3,4,5,6,7 etc

           I also have the films story in FMP as well with a record for each scene and within each of these scene records I have a field that is set asside for the scene number.

           We would like to be able to look at the Films story one scene at a time and see what playouts apply to a specific scene.

           is there anyway for fmp to look at the playout records, then look at the "scenes used" field and look at each of the scenes that are separated by a comma and then relate that to the Films story scene field?

           I guess one way is to duplicate the playout record for every scene it applies to and then relate it, but that would mean a lot of records.

           Thanks in advance

           L

        • 1. Re: relating comma separated data in a field
          philmodjunk

               I think you need this set up:

               Playouts containe one or more scenes

               Stories contain more than one scene.

               This would appear to be a many to many relationship:

               Stories-----<Scenes>-------Playouts

               Stories::__pkStoryID = Scenes::_fkStoryID
               Playouts::__pkPlayoutID = Scenes::_fkPlayoutID

               See this link if the above notation is unfamiliar: Common Forum Relationship and Field Notations Explained

               With such a structure a portal to Scenes on the Stories layout would list all scenes records contained in that story and a portal to scenes on the Playouts layout would list all scenes portrayed in that playout.

               And this calculation from playouts would produce your comma separated list of scenes for a given Playout record:

               Substitute ( List ( Scenes::__pkSceneID ) ; ¶ ; ", " )

          • 2. Re: relating comma separated data in a field
            lauriehughes

                 thanks,

                 still trying to find time to have a go at this, but thanks. will let you know how I go.