1 Reply Latest reply on Oct 7, 2012 12:15 PM by davidanders

    Help on db planning for new project

    JonathanWilber

      Title

      Help on db planning for new project

      Post

            

           I am starting a new project and could use some direction.

           I am familiar with FM "tutorial" basics and have built databases in the old DOS days.  So I'm not totally new to the basic concepts

            

           The project is for the company I work for.  I am a regional sales manager and am trying to develop some tools to manage business better.

           We are a very large plumbing company and our database is an old old green screen type program.  

           I get a weekly excel sheet with the commercial jobs we did that week.  About 200 records per week.

            

           Here are the critical fields:

            

           Ticket number :  The job number for that one job     ( we replaced a faucet at the Dunkin Donuts on 12, Main St, Appleville, PA)

            

           Account number :  A number for that one  location       ( e.g.  Dunkin Donuts, 12 Main St, Appleville,  PA )

           Customer name : The name of that location  ( Dunkin Donuts #12 )

           Address

           Total cost

           Company branch  Which of our branches did the work

            

            

           Parent name:   The company or individual who owns this dunlin donuts,  they may own 10 of them  (e.g. Appleville Coffee LTD )

            

           ------------------------------------

            

           The customer numbers are always there and accurate ( altho they do duplicate occasionaly)

           The Parent Name is hit or miss, sometimes accurate sometimes missing, sometimes wrong

            

           -----------------------------------

            

           I have created a table for:  Jobs,  Accounts,  Parents and a new table for what I call "BroadGroup".  It is the widest marketing label for this group regardless of ownership.  For example Wendys accounts can have different "parents" such as  "Corporate store" or "Smiths Franchise group"  etc however the "BroadGroup" for each would be "Wendys"

            

           At the end of the day I want to be able to run month over month with Year to date sales by  1) Broad group  2) Company branch  3) Parent

            

           Step one is to have a way to "clean up" the inbound weekly excel spreadsheets.  That process would do things such as:

            

                1) Is this a new account number.  If yes we need to add the account to the accounts table as well as its parent and broad group.

                2. It is an existing account number, so bring in the correct Parent and Broad group from the accounts table, discard what is there.

                

           It seems like I need a "cleaning" function for the data each week as well as some "maintenance" functions to identify accounts without parents etc.  Once parents are identified then I need to be able to group update the tables.

            

           Lots of stuff here… sorry for the scope.

            

           Anybody able to start me in the right direction ?  I really appreciate input.

            

            

           Jonathan

        • 1. Re: Help on db planning for new project
          davidanders

               I would become familiar with  Self-Join Relationships, Anchor Buoy, Dashboards

               If you search for "self join site:forums.filemaker.com"  you will see the threads on this forum about the subject.


          http://www.filemaker.com/11help/html/create_db.8.2.html#1027557
          Home > Designing and creating databases > Creating a database > About planning a database
                    A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.           
                         Follow these general steps to plan a database:
          <SNIP>

          http://help.filemaker.com/app/answers/detail/a_id/3248/related/1
          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

          http://help.filemaker.com/app/answers/detail/a_id/3247/related/1
          Relational Database Design 101 (part 2 of 3)

          http://help.filemaker.com/app/answers/detail/a_id/3246/related/1
          Relational Database Design 101 (part 3 of 3)



          MY STANDARD LIST OF FILEMAKER LINKS

          The White Paper for FMP Novices is useful  - 
                                                  http://foundationdbs.com/downloads.html
                                                  
                                                  Filemaker Free - Listing of free resources  - 
                                                  http://filemakerfree.com/


          Free unlocked templates
                                                  By Richard Carlton Consulting
                                                  Starting Point -  Contacts | Accounts | Calendar | Estimates |Invoices | Projects |Products | Staff etc.
                                                  http://fmstartingpoint.com/
                                                  Donations -  Accounts | Contacts | Donations | Reporting | Prefs
                                                  http://filemakerdonations.com/
                                                  Recruiter -
                                                  http://rccrecruiter.com/alt-index.html/
                                                  Data4Life -  Contacts | Calendar | Projects | Prospects | Journal | Goals
                                                  http://data4life.net/

          RCC Blog  - 
                                                  http://filemakerbits.wordpress.com/
                                                  
                                                  By yzysoft.com  Contacts | Products | Invoices | Letters
                                                  Sample Database -
                                                  http://yzysoft.com/printouts/yzysoft_SampleDatabase.html

                                                  
                                                  A free calendar is available and can be integrated into your database
                                                  Seedcode Calendar Free -
                                                  http://seedcode.com/cp-app/prod/calendarfree10/
                                                  
                                                  Filemaker's  Online Help Pages
                                                  FMP 12  http://www.filemaker.com/12help/index.html
                                                  FMP 11  http://www.filemaker.com/11help/index.html
                                                  FMP 9    http://www.filemaker.com/9help/index.html


          FMP business database demos - some are fully useable
                                                  The Excelisys Business Tracker V3.0  - 
                                                  http://excelisys.com/exbiztracker3.php/
                                                  
                                                  Filemaker released a FMP runtime database in 2007 for students called "Campus Productivity Kit" and is available online, notably at CNet
                                                  Google "Campus Productivity Kit"    https://www.google.com/search?q="Campus+Productivity+Kit"

          WIN -  http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577579.html/
                                                  Machttp://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577567.html/

                                                  You can rename the database from a .usr file to a .fp7 file and open in FMP 7 thru 11

          Lynda dot com has Filemaker Videos (portions are free)
                                                  http://lynda.com/FileMaker-training-tutorials/116-0.html
                                                  
                                                  YouTube - Filemaker Videos

          FMTutorials Channel
                                                  http://www.youtube.com/user/FmTutorials/
                                                  
                                                  UK FileMaker Channel
                                                  http://youtube.com/user/UKFileMaker/
                                                  
                                                  FileMaker, Inc. Channel
                                                  http://youtube.com/user/filemakerinc/
                                                  
                                                  Lynda.com Channel - Filemaker videos
                                                  http://youtube.com/user/lyndapodcast/videos?query=filemaker
                                                  
                                                  Dwayn Wright's Channel
                                                  http://youtube.com/user/FileMakerThoughts/


                                                  Soliant Consulting Channel
          https://youtube.com/user/SoliantConsultingTV/

          Filemaker Academy Channel
                                                  https://youtube.com/user/filemakeracademy/
                                                  
                                                  Skeleton Key Channel
                                                  http://youtube.com/user/SkeletonKeySTL/


                                                  Accelerate Computer Training Channel
          http://youtube.com/user/AccelerateTraining/
                                                  
                                                  Filemaker News

          Scoop.it is an information curation site - free option available.
                                                  Filemaker Info  -  http://scoop.it/t/filemaker-info
                                                  Filemaker News  - http://www.scoop.it/t/fm-news

          Filemaker Wikipedia  - 
                                                  http://en.wikipedia.org/wiki/FileMaker
                                                  
                                                  Filemaker - Version By Version  - 
                                                  http://tokerud.com/filemaker/


          Search specific Filemaker sites with this Custom Google Search
          http://www.google.com/cse/home?cx=001044389222327874554:vi8it1bulm8