AnsweredAssumed Answered

How to Calculate a field using specific records

Question asked by wendel7 on Jan 12, 2019
Latest reply on Jan 15, 2019 by wendel7


Alright I am stumped. I am building a little proof of concept database for my business. This program will help us estimate projects without having to reach out to all of our subs and ask for estimates. The idea is to use old costs and measurements to make multipliers for each individual cost code that we have. These cost codes are things like air conditioning, countertops, tile, etc and then they are wrapped up in categories. There are about 200 cost codes and we usually use between 80-120 per project. So there are a decent amount of calculations. Probably nothing near what some databases could have but I want to make this efficient and something that I use for years to come.

I am going to try to be concise but my brain is racing in many different directions trying to figure out this issue.

 

So, here is my issue. I have a table called HomeDetails and inside I have all of my home measurements as fields. We have somewhere around 50 of these measurements anything from SQFT to Cabinet Linear Feet. Currently these are stored as individual fields (SQFT = 5000) instead of in a relation (key = 1, value = 5000 -> key = 1, label = SQFT) because I could not think of a good way to combine multiple of these with a calculation (first floor cooled + second floor cooled = total cooled) using that system. In any case, I want to use these values to evaluate my costs every time that I upload a new home with actual costs. Once evaluated I want to store it in a new database (lets say Multipliers) and then make a summary field to average all of those to be used for the future.

This is where the issue begins. I have 80-120 cost codes that need to be evaluated against the particular measurement that will yield the best multiplier. So for example, I want to divide the cost of 3213 (HVAC) by the TotalCooled field in HomeDetails and store it in the Multiplier field in the Multiplier Table. However, I don't want to divide 3213 by CabinetLinearFeet because the value wouldn't mean anything and it would be a wasted calculation. I need to do this 80-120 times with 50 different measurements I will take on the house. Then I want to store all those values in and make an average multiplier per cost code. Then when we get a new house we just plugin those measurements, multiply it against the respective multiplier and boom goes the dynamite.

 

So how can I tell filemaker which measurement to use to make the multiplication so that it doesn't have to run a calculation for each measurement and waste a ton of processing on something that will never be used.

 

Also, any tips if you notice anything else wrong with my design (I know it isn't great but just doing this in my spare time and new to filemaker)

 

I am including a sample file with some of my numbers so that you can visualize my database and maybe even run some tests if needed! Thanks everyone!

Outcomes