8 Replies Latest reply on Feb 20, 2013 11:33 AM by philmodjunk

    Problems with Charts/relationships



      Problems with Charts/relationships



           I think this is more of a relationship problem than a charting issues.  But here is where I am having problems.

           I am building a database to measure vehicle fuel consumption on historical data and then new data after trails of different conditions.  My Goal is to chart any increase in fuel consumtion of these two conditions

           So far the db is very simple I have tables



           I have a link table with global fields "yes" and "no" for relationships if the data was historical or after the trail had started.

           So my relations ships are

           1) Vehicles Historical

           pKeyvehicles (in the Vehicles Table) = fKeyVehicles (in my link Data table)


           fKeyVehicles (in my link data table) = fKeyVehicles (in my final Data table) AND gNo=Traildata (which is a yes/no field)

           This creates the data only for historical data where the yes/no Trail data field is set to "no" (i.e. it is historical and NOT trial data)



           2) Vehicles Trial

           pKeyvehicles (in the Vehicles Table) = fKeyVehicles (in my link Data table)


           fKeyVehicles (in my link data table) = fKeyVehicles (in my final Data table) AND gYES=Traildata (which is a yes/no field)

           This creates the data only for trail data

           I have managed to create two portals which reports performance for each vehicle  1) historical data and 2) during  the trail.  I have also managed to create two charts for 1) Historical data 2) Trial data.

           My problem is  that I want to create a comparison graph which shows BOTH sets of data each as a line graph showing the improvment in performance.

           I know this is a simple relationship issue, but I can't get my head around it.  

           Help Please!!


        • 1. Re: Problems with Charts/relationships

               It's more than JUST a relationship issue.

               First, It appears that your "link" tables are occurrences of your trail data--which is rather odd. Plus, you do not need the link table occurrences at all. You can define the gYes and bNo fields in your Vehicles table and link directly to your two occurrences of the Data tables and get exactly the same data.

               Your relationships would be:

               Vehicles::pKeyVehicles = Vehicles_Historical_TrailData::fKey Vehicles AND
               Vehicles::gNo = Vehicles_Historical_TrailData::TrialData

               Vehicles::pKeyVehicles = Vehicles_Trial_TrailData::fKey Vehicles AND
               Vehicles::gYes = Vehicles_Trial_TrailData::TrialData

               This doesn't alter your database function except to simplify your relationship graph.

               To chart two lines on a line chart in FileMaker requires charting two fields of return delmitted data.

               If you place your chart on a vehicles layout and define two calculations in your Chart set up for the y-Axis data, you can get what you want.


               To chart MPG for a given vehicle, use these two calculations:

               List ( Vehicles_Historical_TrailData::Calculated MPG )
               List ( Vehicles_Trial_TrailData::Calculated MPG )

               And be sure to specify the Delmitted Data option as the data source for your chart.

               PS. Your Vehicles::pKeyVehicles fields currently permits duplicate values and it not an auto-entered serial number. Ideally, it should be either an auto-entered serial number or (if using version 12) an auto-entered calculation: Get ( UUID ) with the unique values validation field option specified.

               If you are using a VIN number or a license plate number as your vehicle ID, you risk potential issues should you ever enter such a value incorrectly and then have to correct it after the fact. You can include such a field in your Vehicles table, it's just not the ideal field to use as a primary key.

          • 2. Re: Problems with Charts/relationships

                 Sometimes we get the "cart before the horse".   Meaning it apears you want a database to graphically show you the differences between trial conditions.  I would suggest you should think out the "science" of comparison then design the database to save and analyze your trials.

                 For example....

                 The historical data is what might be call "base line" performance.  A chart might show a variation to the eye, but normally you would do statiscal work on historical data to show statistics such are average, median, variations from average or expected error from historical data.


                 You would set up a Trial data and link the trial data to historical by things as Phil suggested.  Next you would perform statisical analysis on the Trial data.

                 Now the science...

                 Simple analysis would be the difference between Averages,   No chart needed.


                 Mulitple Trials at different conditions might be trying to create a funcionality of Trials and the conditions between each trial, each trial linked to Historical data per Phil.

                 Once you decide on the science behind the trials, the DB can now be designed to use and recall, compare, do statistics and correlations for the proof.


                 PS: Charts normally show data versus time but can be used to show functionality like X vs Y scatter charts.

            • 3. Re: Problems with Charts/relationships

                   Hi Guys

                   Thank you very much for the information,  it makes perfect sense.

                   A family member has just set up a company selling fuel additives and he is having difficult collecting,  collating and reporting data,  as I have used Filemaker in the pasted I (stupidly) volunteered to help him build a database which might make the process easier and more effective.

                   We are testing fuel additives which boost fuel economy of commercial vehicles and of course this involves extended trials to prove the product works. The customer normally provided historical data (in Excel format),  and I have found it easy to import into fmpro as long as the format is pretty constant..  During the 1-3 month trail of course it is very important that the mileage and fuel economy  of each vehicle is recorded then compared to the historical data.

                   Each customer may have multiple sites and each site have multiple vehicles.  The data will need to be reported by vehicle, by site and by customer.

                   I have a little experience with Filemaker (9) and suggested that a web based database could be the answer and with the charting data in FMP 12 this is an ideal solution.  At the moment I am putting together a proof of concept  (hence some of the shortcuts correctly identified below).  I would describe myself as a bit above a beginner in FMP.  I can use the product and understand the key functionality.  One area that I have really struggled in the past in planning our projects in advance and also understanding how relations ships work when it is more than a basic one to one.

                   So here is a key outline of what is required from this project and any advise in advance would be greatly appreciated:-

                   * Each trail will have historical and Trial data

                   * Each Trail will be considered as a customer but a customer might have multiple sites. Each site will have multiple vehicles

                   * Each trail should be able to report data as follows

                   - Per vehicle analyzing changes in fuel consumption

                   - Per site as a whole offering statistical analysis of any improvement

                   - Per customer trial

                   * The plan is to have a database hosted on the Internet (I have done this before and it worked well)

                   -  Each site should be able to enter data via browser &/or iphone/ipad.

                   - The operators should only be able to see simple date entry screen where their login will automatically filter data only from their local trail.  i.e. Bill Smith logs in from site X and is presented with a basic screen where he can select a pull down list ONLY for the vehicles at his site.

                   - Once he selects the Vehicle he can enter "Date (maybe automatically set to today)",  "Start Mileage", "End Mileage" and "Liters of fuel used" -  but that is all he can see or edit.  If he enters data that produces mpg figures out with the expected for the vehicle he will be presented with a warning.  This is easy and I have done already I just need to decide whether this is a range entered manually when the trial is set up (I think this would be best)  or whether the database calculates an on-going average. One of the main problems that we have is that operators sometimes incorrectly enter mileage/fuel used figures and it is not uncommon to see results from a truck which averages  8-10 mpg returning over 1000mpg one week!!! 

                   * At the end of the trail there should be the opportunity to produce a canned report with summary data of the findings i.e. what vehicles produced the best and worst improvements in mpg, what was the overall results of the trial across all vehicles and then an appendix with data from all vehicles.

                   As I mentioned above most of the nuts and bolts of this project I can do but it is how I set up the tables and relationships from the start that always ties me in knots.

                   Thanks in advance for any advise you can offer.






              • 4. Re: Problems with Charts/relationships

                     My "hat off" to you!yes

                     In your last post you have done what many don't.  You are gathering what is needed as an INPUT and some OUTPUT, before you begin to program in FMP.

                     I have a background in testing and analysis of experiment data.  This is what I suggest....

                     1) Refine the data actually needed for each trial.  ie., engine type, age, maintenance history, tire size, vehicle weight [note: weight is a key]

                     2) Decide what analysis will acutally prove the enhancement { data processing }

                     3) Make sure the data is good data, and not trash. {weighting data }

                     4) Data manipulation {analysis]

                     5) Publish the results  [OUTPUT]

                     Refine what is needed and then it will be clear how to encode into FMP


                     It wll be Ready.... Aim... Fire  and not  Fire... Ready... Aim

                     Sounds like a fun adventure.


                • 5. Re: Problems with Charts/relationships

                       Hi Folks

                       A wee update on my database for analysing the results of changing fuel ecomony from customers vehicles.  The project has begun and I started by watching about ten hours of fmp tutorials on www.lynda.com!!

                       I have decided that I need the following tables

                  •           Companies
                  •           Contacts
                  •           Sites
                  •           Vehicles
                  •           Data (i.e data, start mileage, end mileage, fuel used, calculated mpg etc)
                  1.           Each company can have many contacts
                  3.           Each company can have many sites 
                  5.           Each site can have multiple vehicles 
                  7.           Each vehicle can have multiple data

                       It is all working fine and I have tested with a Company layout with tabs for contacts, sites, vehicles and data.  each with the relevant portal

                       BUT the last tab is for data and obviously I need to report the total results for the company but also list what vehicle each set of data is for. Although my local fkey_vehicles (from the data table) is displayed correctly - the vehicle description field (from the vehicle table) only lists the first description for all results in the portal.

                       I sort of understand what is happening here as I am trying to "go the wrong way up the relationship graph" but how do I deal with this type of thing effectively  do I need a completely seperate relationship table starting with data??  If that is true how do I then get the results as they relate to the company

                       I am appreciate this is a dumb question,  but it has me stumped.







                  • 6. Re: Problems with Charts/relationships

                         A layout based on Data can list fields from all of your tables.

                         Thus a report based on that layout can show the vehicle, site, contact and company data that is linked to that data record.

                         Summary reports can be set up that group your records in Data by a common value such as the Vehicle, site, Contact or Company and a sub summary layout part can be added to serve as a subheader to show that data once with the multiple data records for that group listed below it.

                    • 7. Re: Problems with Charts/relationships

                           Hi Phil

                           Thank you for your explaination!

                           There seems to be a difference with Portals that I can not seem to grasp.  If I have a portal on a COMPANY layout and the portal is based on the DATA table,  I can not reliably show the VEHICLES that the DATA came from.

                           I get an text shown in the field but it just reports the first value? Why is that?  

                           I have attached a screencap which shows the correct fkey value for the vehicle but an incorrect description for all but the first record

                           Best regards



                      • 8. Re: Problems with Charts/relationships

                             This will seem a bit weird, but you'll need to add a new occurrence of Vehicles "downstream" from the data occurrence:

                             Company----      other table occurrences here       ------<Data>--------Vehicles 2

                             Then you can add the vehicle name field from Vehicles 2 instead of Vehicles.

                             For this new occurrence of Vehicles, you'll use the same matchfields that you have used for the first.

                             If you don't know how to duplicate a table occurrence:

                             In Manage | Database | relationships, make a new table occurrence of Vehicles by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                             We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.