I am trying to solve the following business problem and I think QB may have the needed functionality but I am not a software / database programmer so am reaching out.
I run a small engineering / metal fabrication company and I have a need to produce quotations fast and accurately. I currently use an Excel spreadsheet and although this is OK for simple jobs it is very labour intensive when calculating prices for multiple items or complex jobs with lots of parts and process steps.
I want to build an App? that has the following capabilities:
1 ? A Table of Materials with the cost for each material based on ?/meter (easily done in one table).
2 ? A Table of Process ?Standard Times?. This is a list of process Types (e.g. MIG weld), process details (100mm long fillet weld), with the associated time it takes to perform this operation (e.g. 1min 30 seconds).
3 ? From the two tables I want to be able to create a ?Job Costing? query. This is where I ran out of knowledge when trialling QB.
3.1 - The query needs to be a new form that I create for a new job / quote. I want to be able to look up and select from the materials table to select a type of material and then say how much of it will be used ? e.g ? look up Steel, 50x50x3 box section ? select this material and then specify a length ? e.g. 1500mm and then select a quantity of these parts ? e.g, 10 off The resulting record in the query table would be : 50x50x3 box section; 1500mm; 10 off. The query should pull in from the Materials Table the cost per meter (a field) and then multiply it by the number of pieces; in the example above it would give 10x1,500mm = 15,000mm. And because the table also has cost/meter then it would also calculate the total cost for these 10 pieces of box section.
The query / app should be able to add multiple selections of material so that a list of parts with their corresponding material costs is generated.
3.2 ? In the same way as the Materials query works a second query that pulls in Process costs is needed. So the user would select from a pull down or similar each of the manufacturing steps needed to make the product. E.g. Operation 1 might be ?Sawing, Bar stock less than 50mm? ? this would have an associated ?time? in minutes of (e.g.) 0.75 minutes. The number of these operations would be a user selectable field in the query ? e.g. 10 operations to cut 10 pieces of material. And this would then total up for this step the time taken. The time has a standard labour rate associated with it and so the labour cost for this process step is computed.
As above, multiple selections would be added to the Job costing query until all steps needed to make the part are presented.
4 - With the two sets of information in place ? all drawn out of a database / set of tables, the overall job cost can be calculated. To this an overhead value and a profit margin can be added and the resulting number is the price to quote the customer for the job.
I have managed to set up tables that have the basic functionality for Materials and Process costs. But then I ran out of smarts. I think what I am lacking is the overall design / schema for the app? ? I can?t figure out how to get the ?Job Costing? query to pull out the data from the tables and then do the multiplication and store it in a line item of the query.
I have an Excel sheet that does the whole lot but it is labour intensive to cut and paste each line item from the Materials and Process Tables (tab in excel) into the Job Costing sheet. I tried learning Pivot Tables but could not achieve what I wanted.
Is this achievable with the functionality QB has to offer or is it beyond its capabilities?
If it is - what might a database/ table design at the high level look like to deliver this functionality?
From what I?ve seen by trialling the tool I can figure out the programming but I think without a high level design I?m just going to go round in circles.
Hi and thanks for the response.
I had a go at what you suggested but am struggling. Perhaps I need to spend more time watching the tutorial video�s to grasp the basics.
In my current App I have the following Tables / fields:
� Material � selectable list
� Shape � selectable list
� Size � selectable list
� Bar length - numeric
� Cost / bar - Currency
� Cost / meter � Currency (formula)
� Process Group � selectable list
� Process Detail � selectable list
� Labour rate (�) � Currency
� Process Time (mins) - numeric
� Standard cost- Currency (formula)
As you suggested, I then created two new tables, one called Estimate and one called Material Estimate
Initially I created no fields to either table beyond the default ones created by the system. But I then created a field in Estimate Table called �Unit Material Cost (look up)� as suggested.
I then added a one to many Relationship in the Estimate -Table to The Materials table.
The fields in the Master Table - Estimate are: Record ID (key field), Materials (report link), Add Material (URL formula). The fields in the Details Table � Materials are Cost per meter (numeric-reference) and I added the lookup field called �Cost per meter � Unit Material Cost (lookup) � numeric (reference proxy).
I saved and came out and then went into the Estimate Table to see what I had.
The Estimate Table now does something. On the Home Page for Estimate I see a table that has the following data columns: Unit Material Cost (lookup) , Materials (with a hyperlink titled Materials), and a Add Material (a button).
When I click the Add Material button it opens a new data entry form to the Materials table allowing me to create a new material record.
At the top right of the home page there is a green button called �New Estimate�. When I click I get a form with a box that says Unit Material Cost (lookup) and I can enter values to this.
Below this I see a button for Add Material. This again is a new data entry form for creating a new material record.
So it is doing something but I don�t really get what it is doing or how I arrived at it.
I think what I am missing is a clear picture of the overall schema of the database in terms of what tables do I need? What fields in each table? What relationships between tables? What lookup fields to place in the �Estimate� table.
Even with the experimenting I have done I can see the tool is capable of what I am trying to do. I just can�t get at it.