12 Replies Latest reply on Jan 6, 2009 9:16 AM by GreaterThanData

    guidance needed please...

    LightningAd

      Title

      guidance needed please...

      Post

      I have been reading books and watching hours of tutorials on Lynda, but i am still very unsure of how to design my database to do some rather specific things.

       

      The intention is that the database will catalogue all our tv commercials, and will also provide a way of keeping tv stations updated with transmission instructions (via email).

      All that is someway off in the future - i still need to make the database!!

       

      Because each finished commercial we make has several versions, each with a different store tag (eg "Available at Toys R Us") They all have a unique clock number. ( I have already had some excellent and very helpful guidance from the forum regarding those).

       

      I want to figure out a way to input data for new commercials with as much auto entry as possible. The idea being you enter the product, campaign name, actors, music data etc, once and then use a check box so you can select which store tags are being used. Upon commiting, the records are created with the unique clock number for each version.

       

      I feel like this should be relatively simple, but i cannot seem to get my head around a solution!  Please can someone point me in the right direction?

      I'm using FMP 8.1

       

      thanks

      Adam 

       

        • 1. Re: guidance needed please...
          GreaterThanData
            

          Good morning Adam.  What you are describing sounds QUITE simple.  We have built many 'clip tracking' solutions, so, we could definitely help.  I don't know if you are looking for someone to actually build the solution for you OR just want some suggestions ... either way I am happy to give you some guidance.

           

          Looking at your description of the goal, we could get pretty fancy with options including direct web-based forms for the tv stations to view OR download the data OR even direct connections to their databases.  Lots and lots of integration tools ... after we get the initial basic data collection working.

           

          Let me know a little more and we will go from there.

           

          Feel free to email me offline as well if you'd like.

           

          Randall Mauro

          Greater Than Data / Happy Mac & PC

          rm@greaterthandata.com

          • 2. Re: guidance needed please...
            LightningAd
              

            Thanks for your reply Randall.

             

            I am hoping to build the database myself...as i will be the main user, and also the administrator, then it makes sense if i know its inner workings intimately.

             

            I have no need of those fancy options you mention. The needs i mentioned are specifically to meet the operating methods currently employed by tv industry - ie, specific info sent via email.

             

            My current situation is that i have a basic understanding of how the relational database should work, and i could get a basic version working quite quickly, but rather than do that and then have to change it all, i would rather learn the more advanced techniques that i think i need so they are built-in from the outset.

             

            What i mean by basic is a totally manual set-up...everything gets hand entered for every new record.

            My first obstacle is that I want to have a system that will take certain data units and then upon choosing a number of options will create new sub-records that each have a unique clock number but would retain all the info specific to its initial record.

             

            Sorry if that sounds confusing...its not an easy concept to describe!  I'll try work it backwards so you can see what i am aiming for.

             

             

            Each TV Commercial (TVC) has a unique clock number (15 digit alphanumeric - in format ABC/WXYZ123/789 - ABC is the ad agency code, WXYZ is the Product Ref Code, 123 is a 3 digit numeric serial, 789 is the duration in seconds).

             

            Every clock is unique because it is used by the Broadcast approvals agencies and the TV Broadcasters to determine if a commercial has officially been sanctioned for broadcast. Any change to a commercial requires a new clock number to be generated and added to the start of the commercial, and it needs re-submitting for re-approval. 

             

            Each commercial can have a number of variants (Tags) and in our case, the tags refer to an "Available at...." graphic on the end of the advert. Therefore, a tag is classed as a separate commercial and such has to have a unique clock number.

             

            If I have a generic TVC with 4 tags (5 tvc's in total), most of the data will be relevant to each variant (campaign name, duration, actors, composer etc) so it makes sense to only enter that portion of data once. The differences will be in the Tags name and clock number. (My naming convention is to add the tag name in parenthesis after the campaign name on the video clock).

             

            My current stumbling block is this....how do i enter the generic data first, then upon choosing the required tags have FMP automatically create x number of new records each with its unique clock number and name, as well as the generic data?

             

            if you've got this far...thanks for your patience!  I actually edit the commercials and its taken me several years to get my head around how the database would need to function to be useful.

            regards

            Adam 

             

             

            • 3. Re: guidance needed please...
              Jens Teich
                 Hi Adam,

              it is perfectly fine that you want to build it up by yourself. This is exactly what the designers of FileMaker had in mind when they invented it.

              But your project is too big for this forum. You are always welcome with detailed problems but these sort of data modelling is a problem which can't be solved here.

              If you need guidance for the first steps hire an experts for some hours or some days. The money is a good investment if your own time is worth anything.

              Another helpful source is the official training material sold in the shop on this site. The authors did a good job in explaining the first steps towards your own solution.

              Jens




              • 4. Re: guidance needed please...
                LightningAd
                  

                Hi Jens,

                 

                too big?  really?  I have been looking through this forum and seen many problems that looked far more complicated. Perhaps its my description that is at fault - i tend to give too much detail sometimes.

                 

                I have been reading manuals and watching tutorials but have not found anything that suggests a direction to pursue in search of a solution to this problem. I have also begun looking into one to one training, but as i am not the one controlling budgets, it may prove too costly in the current economic climate. 

                 

                I have given it much thought and think i can simplify the question -

                 

                How do i create a new record for each selection chosen from a Checkbox list?

                 

                thanks

                Adam 

                • 5. Re: guidance needed please...
                  Jens Teich
                     > How do i create a new record for each selection chosen from a Checkbox list?

                  That sounds better. You gave too many details leaving the impression that you are at the very beginning.

                  Create a script like this

                  Set Variable[ $count ; 1 ]
                  Loop
                  Perform Script[ Create New Record; ScriptParameter; getvalue( checkbox; $count ) ]
                  Set Variable[ $count ; $count + 1 ]
                  Exit Loop if [ $count > ValueCount( checkbox )]
                  End Loop

                  This is only the basic pattern, but it shows the way to go. 'Create New Record' is a subscript which creates a new record and goes back to original layout.






                  • 6. Re: guidance needed please...
                    LightningAd
                      

                    many thanks Jens,

                     

                    that is just the kind of guidance i was hoping for. 

                     

                    regards

                    Adam 

                    • 7. Re: guidance needed please...
                      LightningAd
                        

                      I have been trying your suggestions Jens, but wondered if you could clarify what you mean by "ScriptParameter". I can't find any reference to suggest  what this actually is.

                       

                      Would you agree the script would be best attached to a button specifically to generate the records once all options had been entered?

                       

                      thanks

                      Adam 

                      • 8. Re: guidance needed please...
                        Jens Teich
                           The idea using a button near the checkboxes is fine.

                        Script parameters can be defined while attaching a script to a button. At the bottom of the dialog. You enter the calculation I gave.

                        In the subscript you can refer to this value with the function get(scriptparameter). This is a smart way to tranfer the values of the checkboxes into the subscript.

                        Jens


                        • 9. Re: guidance needed please...
                          GreaterThanData
                            

                          good morning (in Los Angeles :smileyhappy:) ...

                           

                          on idea is to hire an expert to build just the base, so you get started on the right foot, THEN you take over on the easier, layout setup, etc.  This is a great way to save some $$, but not pay too much.  Jensteich said it right ... that FileMaker is designed to dabble until you get it right, it is VERY forgiving, but you might want to consider either hiring someone for 5 hours to sit with you for an hour, then build the base for the other 3 - 4 OR ... sit with you for 5 hours and give you an intense training on FM.  Otherwise, FileMaker has an AMAZING book that is worth its' weight in gold to get up to speed on the product.  it starts off assuming you are a beginner and slowly gets more advanced as you go.  It is the best FM training book I have seen ... It is their training series and can be purchased at the FM website.

                           

                          I am happy to give you some more suggestions, but if your time is as tight as mine ... paying someone for a few hours will save you probably hundreds of hours!  My first database ever took 6 months to build, I could now build that same database in about a week.

                           

                          Regards,

                           

                          Randall Mauro

                          rm@greaterthandata.com

                          • 10. Re: guidance needed please...
                            LightningAd
                              

                            Jens - thanks...i see what you are saying.

                             

                            Randall - bet LA is a bit warmer than Manchester today!  I am probably going to go down the route you suggest...however, i think the more familiar i become with the software before i do that will mean more time can be spent on more advanced concepts.

                             

                            Would you happen to know the title of the book you recommend, please?

                             

                            regards

                            Adam 

                            • 11. Re: guidance needed please...
                              Jens Teich
                                 See here.

                              Jens
                              • 12. Re: guidance needed please...
                                GreaterThanData
                                  

                                I think it is great that you want to dig in.  That's how most of us got started! 

                                 

                                It really is a great book ... well worth it's 99.00

                                http://www.filemaker.com/support/training/fts.html

                                 

                                by the way, the built-in FileMaker Help menu isnt too bad either ... for things like "script parameters" ... you can look there and get up to speed pretty quickly.

                                 

                                with that said ... feel free to throw me questions ... happy to answer!  feel free to email me.

                                 

                                Randall Mauro

                                rm@greaterthandata.com