7 Replies Latest reply on Feb 5, 2017 12:58 PM by siplus

    Noobie needs help on 1. design (calendar like database?!)

    log3

      Hello Filemaker community!

       

      I started developing my first database with FM14 and I don't know if Im going in the right direction with my concept.

       

      Lets imagine I have a car-leasing company. The deal is, that a car can only be leased for a period of years, atleast 1 year, or more.

       

      I have a table for customer 'accounts' that holds the basic customer infos, name, adress, etc.

      I have a 'car' table holding all the info on my cars (model, color, mileage etc.).

      I have 'leasing' tables that hold the infos of who leased which car and when (basically where the data from the 2 previous tables comes together).

      I also have an 'Overview' table which is mainly used to display all those data in an organized matter in 1 single layout.

      The thing is, I want to display who is leasing a car this year, and also who leased it the previous year and who will lease the car the next year.

       

      So basically, I created an Overview-layout with 3 tabs to switch between the years so I can search for a car and see who leased it last year, next year etc.

      My approach was to create 3 different leasing tables, one for each year (leas-prev, leas-current, leas-next) The records in those tables have a unique ID which incorporates the year, i.e. car001-2016 and relates to the car and accounts tables.

      So the idea is that I have 1 record for each car in my Overview-layout and then I would be able to switch between the years by clicking on the corresponding tab.

       

      My questions (concerns) now:

       

      1. Is this a 'good' approach by seperating the leasing tables by year? (I don't want to use portals and I don't know how else I would display multiple records of the leasing infos in my overview.)

      2. I had to create 3 different overview-layouts, one for each year, looking the same except the year they are currently displaying. Thus I have to maintain 3 layouts and any changes to the layout I have to do on all 3. Is there a better way? (Maybe a script which changes the displayed data when a different year is chosen?)

      3. Is there any way, I could automate the creation of a new leasing table once a new year hast started? For example, once 2017 is over and 2018 starts the system would scrap/archive the not needed 2016 table, rename the 2017 table to "leas-prev", the 2018 one to "lease-current" and maybe copy the 2018 table to "lease-next" but changes year to 2019 and deletes the leaser-data.

       

      I'm thinking there must be an easier way to this. Basically it's like your own calender-database, except that its split by years since I don't want to bloat the database by adding every year from 2016-2050 in advance.

       

      Thanks for your help and comments,

      hope I did this right, its my first post here