Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
1 Comment

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

577 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