Script or calc. field for "kitchen conversions"

Posted on 2003-02-23
Medium Priority
Last Modified: 2010-04-27
I have a database of products and cost of goods sold. At this point I would like to break the numbers down further. Ideally I would like to have (either in a different layout, or seperate database) a way to take e.g. 5 lbs. and $23.00 and push a button which could break those numbers down into cost per teaspoon, cost per quart...etc. I will use this to get my idea cost of goods sold for menu items at my coffee shop. Any ideas or suggestions? Should I make it a seperate database using portals to connect the two? How do I build the calculations? I was thinking of a pop up list of measurements with a script for calculating by whichever measurement I chose. How could I build such a beast?
Thank You,
Question by:sberebit
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

KungFoolio earned 200 total points
ID: 8035518
You can use a conversions table (database) to do this.

Create a new database with the following fields:
StartingUnit  (text)
EndingUnit    (text)
Conversion    (number)
UnitConcat    (calculation, StartingUnit&"_"EndingUnit)
You will have to define each of your conversions with each record. Ex. StartingUnit = gal, EndingUnit = tbsp.

In your main file, you will need the following fields:
BulkSize   (number) ex. 5
BulkUnits  (text)   ex. gal
BulkCost   (number) ex. $23.00
ServingSize (number)ex. 1
ServingUnit (text)  ex. cup
UnitConcat (calculation, BulkUnits&"_"&ServingUnit)

Now, we need relationships:
In the main file, create a relationship to retreive conversion factors. MainFile and ConversionFile, match UnitConcat with UnitConcat. Create another relationship called BulkUnits between MainFile and ConversionFile between BulkUnits and Starting Units. this will be used later to be sure make comparisons between units that you have conversions for.

Now, on to calculations in MainFile:
-Create a Conversion field, type number, Options=Auto-enter looked up value, using ConversionFile, copy Conversion.
-Create a ServingUnitsPerBulkQty field to tell you how many serving units you get from the bulk box/drum/whatever. Make it a calculation,unstored, (BulkSize*Conversion)/ServingSize.
- Now the final calc to tell you how much each serving costs ServingCostPerUnit, calculation,unstored, BulkCost/ServingUnitsPerBulkQty.

Now, on to the layouts, and to make this all work:
Add to your layout: BulkSize, BulkUnits, BulkCost, ServingSize, ServingUnits, ServingCostPerUnit.
In Layout mode, right click on BulkUnits, choose field format, select Popup list, define a new list of Bulk Units, use values from field, specify ConversionFile, and choose StartingUnit. OK, Done. Then right click on Serving Units, choose field format, select Popup list, define a new list of ServingUnits, use values from field, Only related values, using the relationshipt BulkUnits, and using values EndingUnit. This ensures that you can only create conversions for unit conversions that you have defined.

Try it out. Email david@cloutier.cc if you would like a copy of the example.


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question