Jump to content
Guy_Smith

Engine Exhaust Emissions Database ERD is Circular!

Recommended Posts

I am trying to set up a database to calculate and report exhaust emissions from diesel engines and am stumped at Step #1:  The infamous Entity Relationship Diagram!

I am looking at a few thousand engines and need to calculate how much of what kind of pollutants they kick out into the air.  I have spreadsheets that list the Year of Manufacture, the horsepower, the serial number, how long the engine ran during a given year and how much fuel it consumed during that year.  I also have some spreadsheets that list what the Emission Factors, usually expressed in pounds per hour operated or pounds per gallon of fuel consumed, for each of the two dozen chemical compounds I’m interested in tracking.

Those emissions are relatively simple to calculate:  Multiply the Emission Factor times the number of hours operated (or times the gallons of fuel consumed) and we’re done.  Except for the fact that the Emission Factors are based on the Year of Manufacture and, in some cases, the horsepower rating of the engine, so I have to circle back around to the Engine table:  I don’t know how to connect the emission factor for, say, Arsenic to a specific engine, say a 154 hp Chevy made in 2014,  based on that engine’s year of manufacture and hp rating.

So my ERD keeps looking something like this:  Engines --->Emission Factors--->Engines   or   Engines ---<  Emissions >---Emission Factors--->Engines

My computer keeps puking ones and zeroes all over my keyboard when I try either setup in my Relationship Graph!

The only other way I can think of accomplishing these seemingly simple calculations is to write a boatload of scripts with variables, but I’m not even sure I could come up with all of the combinations/permutations involved.  And, unfortunately, I can’ even spell SQL, let alone know how to execute one of ‘em!

I have attached a (simplified) copy of the spreadsheets I currently use to better explain my problem.  I hope one of you will find this to be mind-numbingly simple and explain how it should be done.  Thanks very much for your time and consideration.

Sincerely,

Guy

Engine Emissions Calc Sheet MOD for FMF.xlsx

Share this post


Link to post
Share on other sites
On 03/22/2017 at 1:13 AM, Guy_Smith said:

the Emission Factors are based on the Year of Manufacture and, in some cases, the horsepower rating of the engine

You have to start by splitting this into individual records in an EmissionFactors table that has fields for:

• Substance
• YearOfManufacture
• MinimumHorsePower
• EmissionFactor
• Units

Then for your report generate a record in a third Emissions table for each Engine/Substance combination, and let it calculate the total emission. So your structure would be:

Engines -< Emissions >- EmissionFactors

and the relationship between Emissions and EmissionFactors would be defined as:

Emissions::Substance = EmissionFactors::Substance
AND
Emissions::YearOfManufacture = EmissionFactors::YearOfManufacture
AND
Emissions::HorsePower ≤ EmissionFactors::MinimumHorsePower

with the records on the EmissionFactors side sorted by MinimumHorsePower, descending.

 

On 03/22/2017 at 1:13 AM, Guy_Smith said:

how long the engine ran during a given year

I don't see a given year in the example. If you have  year-by-year data, this may affect the structure.

 

 

 

 

 

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites

Comment:  Thank you so much for the help on this!  And, of course, you're a step ahead of me already - I am currently only collecting data for 2016, but I anticipate that it will turn into an annual event and I will need to modify my database to include multiple reporting years.  Any helpful hints on how to tweak the solution to handle that?

Thanks again,

Guy

 

Share this post


Link to post
Share on other sites
1 hour ago, Guy_Smith said:

I will need to modify my database to include multiple reporting years.

Again, you will need a table where the yearly engine consumption data is stored using an individual record for each engine/year combination. I don't know how you're getting your data, but ideally you would have:

Engines -< EngineYearlyConsumptions -< Emissions >- EmissionFactors

Note that a record in Emissions table must lookup the YearOfManufacture and HorsePower values from its ancestor in Engines, in order to link to the correct record in EmissionFactors.

Share this post


Link to post
Share on other sites

That actually makes sense to me - who'd have thunk it?!

Thanks again, Comment - your willingness to help us neophytes is a true blessing!

Share this post


Link to post
Share on other sites

Rats! Phooey!  I'm still stuck - I can't get the emissions join table to populate.  I split the emission factor data into individual records, I've verified that I have the box checked to allow creation of records in the Emissions table and I've tried using a portal from the Engine table to display the emissions, but still nothing.  I maybe shouldn't be pulling the data for the Engine bhp and Tier from the Engine table or the CAS and Pollutant Names from the Emissions Factor table (for the Emissions table), but I don't know how else to do that (I also tried to do automatic lookups for those fields, but it didn't work, either). Any chance of looking at my file and seeing where I went wrong?

And, by the way, you will see the word "Tier" used in place of Year of Manufacture:  Tiers are basically groups of years used by the EPA. Using them in this instance saves me some time as I only have to enter emission factor records for one Tier instead of three or four records, one for each year in the Tier.

 

Thanks again for all of your assistance,

Guy

HARP DICE EmsInv Calc Sheet.fmp12

Share this post


Link to post
Share on other sites

I am afraid I cannot look at your file. In case it wasn't clear, you need to run a script that will create a record in the Emissions table for each combination of  EngineYearlyConsumptions and Substance (i.e. every row in your report). There is no other way to populate the table (unless you want to do it manually, or you have an outside source you can import)).

 

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites

Yeah, I sorta missed the "generate a record" part of your first response :B

I got the script to work and my database is up and running thanks to your guidance - thanks so much and have a great afternoon!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Hproth
      I'm sure there is a simple solution to this problem, but I can't seem to figure it out!
      I have a custom app that allows the user to write letters amongst other things. 
      Each patient is linked to a GP and a Consultant, when writing a letter to a patient there is the option to CC both the linked GP and Consultant. However, occasionally another GP or Consultant might need to be CC'd in the letter (in addition to the ones already linked to the patient). So I would need the address of this additional consultant/GP to also be CC'd in the letter. For the life of me I can't work out a simple way to do this. 
      Does anyone have any suggestions?
      Thanks in advance.
       
    • By fumblewinter
      The Problem:
      I have a filterable portal (1) displaying contact names from a staff table. I need to click on a contact to add them to a separate portal of invitees (portal 2)  on the same layout (Events) . I then need the original portal to either hide (or in some other way denote) the people already added to the second portal. In other words, it needs to be clear that a person already invited can't be invited again.
      The layout is from the table: Events
      Portal 1 is showing records from Staff (filterable via a global search field)
      Portal 2 is showing records from EventInvitees
      I need Portal 1 to only filter through those staff who haven't been invited yet.
      Any help would be gratefully received.
      I am struggling around using 'conditional formatting', 'hiding when…' options and non-matching field relationships!!
      Thanks in advance!
      Fumblewinter
    • By Lowermountain
      Hello,
      New to Filemaker and new to this forum. Hoping someone can help me out.
      I am working on a personnel planning system with, among others, the tables Employees, Shifts and Availability. 
      tblEmployees has fields like Employee ID
      tblShifts has the fields Employee ID, Start Timestamp, End Timestamp
      tblAvailability has the fields Employee ID, Start Timestamp, End Timestamp and Status
      When in the layout Shifts I want to open a portal in a popup and I want this portal to be sorted by Availability and I want the portal rows with employees to be 'greyed out' when status = 0, green when status = 1 and normal when no Availability is given.
      I basically need to know which records from tblAvailability match records from tblShifts and then which records from tblEmployees match records from tblAvailability.
      How do I go about this? Do I need SQL?
      Hope this is a somewhat clear question. Many thanks!
    • By HJS
      L&G,
      I have read the guidelines for posts in this forum. Therefore I did not enter the Post title that came first in mind: Do I understand portals (aka do I understand Filemaker) and I do not emphasize now on being new in Filemaker issues.
      I am trying to achieve something simple at a first step of a big plan: Entering recipes in a Filemaker testversion database. 
      The longterm goal should be a database in which I can enter my collected recipes.
      Followed by tool for a weekly menu plan for my family and a shopping list coming out of the database after taking care of my fridge's stock => so far so good, but currently this is a plan for the next ten years I realized starting with Filemaker.
      I searched different apps and database programs, and Filemaker seems to be my solution as I did not find anything that fits exactly my expectations.
      So back to the first problem: how to enter my recipes?
      I created a table of recipes with an unique ID_pk (I learned already about pk and fk), Name and How-To-Make-it as well as IDs(fk) from the other tables which are Zutaten (Ingredients) with the name and the amount, Form_Zutaten (how the ingredients are used i.e. chopped, sliced, etc.) and Einheit_Zutaten (unit i.e. cup, liter, etc.). Why I have choosen this structure: because all ingredients must be combinable with different units and how the are used. otherwise I have to enter e.g. avocado sliced, avocado mashed, etc. or make rules like liquids can only be liter or mililiter or cups while flour can only be gramms etc.
      So a combination of all three tables should be possible.
      What I though might be an easy task is to create a recipe layout than with 
      recipe ID
      recipe name
      and a dynamic portal depending on the number of ingredients constisting of:
      Zutat - Form - Menge - Einheit
      in english: Name of the ingredient - how is it, which kind/form - how much (qty) - which unit
      so I related the tables via pk and fk, created the layout and bam: complete disaster.
      after needing a couple of days to make the values appearing in the dropdown (learned about the value list), some fields in the portal do show the all entries of the ingredients, while the kind/form and unit fields do always show the first value of the table?
      and although I locked the fields, the first entry is always shown and is getting overwritten by the choice I choose from the dropdown...
      and nothing is dynamic, meaning that 7 ingredients will make 7 lines and it always starts with one empty line, etc.
      I am not sure if my descriptions do make sense, therefore I am attaching my work, hoping someone could help me with how to use the portal for a convenient data entry.
       
      thx in advance!
      br
      HJS
       
       
      2017_09_Essensplan.fmp12
    • By 123
      Hey,
      I have the following problem. I'm working on a solution for the owner of several restaurants. Each restaurant has meetings once or twice a month. How would I structure the tables so that I could have a layout on which I select a restaurant and then inside a portal I get a list of all employees. On the portal for each row I want to have a button or checkbox to mark if an employee is present or not.
      I already have the following tables:
      Restaurants, Employees and Meetings
      So far I have a relationship between the Restaurants and Employees table which I use to assign employees to a restaurant, and a relationship between the Meeting and Restaurant table, which allows me to show all employees that work at a selected restaurant using a portal on the meetings layout. How do I proceed to solve my problem? Do I create another table MeetingAttendees or something similar that I use to keep track of people attending a meeting? How would I create a relationship then to allow me to mark certain employees as present and absent?
      Thanks in advance,
      Mike
×

Important Information

By using this site, you agree to our Terms of Use.