AnsweredAssumed Answered

Nutrition Database Calculations

Question asked by chrishill on Apr 11, 2017
Latest reply on Jun 23, 2017 by chrishill

Hi everyone,

     Newbie here...

I work for a small (2 people) animal feed business. Previously I worked as a ruminant formulator for a national company that used industry specific software for this kind of thing- well everything actually.

When I started here I created some spreadsheets to do the job, which are functional but as we (hopefully) grow are not going to be scaleable.

 

I've only started working with filemaker in the past few months on and off as time allows, and so far by lots of googling and youtube watching I've managed to create a customers / contacts, contract and sales orders database, and got the bare bones of a stock control system, but my real priority at the moment is getting a formulation / nutritional database set up.

 

Hopefully my screenshots below should help to explain in detail where I have got to so far:

Following the guidance from this thread How to build a nutritional database , I have created this:

(You'll see that Materials and Products are different instances of the same table - we sell both the raw materials and the mixed products - I don't think this should affect the fundamentals of what I want to achieve.)

Screen Shot 2017-04-11 at 15.16.33.png

Which enables me to easily add new nutrients if required, like so:

The Screenshot below being based on the table 'Nutrients'

Screen Shot 2017-04-11 at 15.17.29.png

 

The screenshot below is a layout based on the table 'Materials' with a portal to table 'Nutrient Matrix' where i can enter and edit the nutritional data of my materials:

Screen Shot 2017-04-11 at 15.17.50.png

 

My final screenshot is a layout based on the table 'products' with the left hand portal being 'formulae' and the right hand one pointing to 'nutrients'.

In the left hand portal I have created a simple formulation of 50% Soya, 50% Sugarbeet. (Most formulations would have at least 4 or 5 materials, but keeping it simple)

Some materials have more nutrients, e.g. minerals. if I add a mineral to the formulation, the additional nutrient names appear in the right hand portal.

Screen Shot 2017-04-11 at 15.18.51.png

What I need to do now, is display the nutrient value of the formulation - so in the picture above, OilB would be:

(the value for OilB of Hipro Soya /100) * 50

plus

(the value for OilB for Sugarbeet / 100) * 50

And so on for the rest of the nutrients.

 

I think I must be missing something fundamental as I can't work out how to do that part - I keep getting the first record from any given table repeated, dependent on which table I enter the calculation field.

I'm pretty determined, but I think I've got out of my depth!

 

I found a thread on another forum (which I can't find now) which suggested that a portal was not the way to go here, but if I remember correctly the guidance given was to use SQL - which I have no idea about.

 

Ultimately I want to be able to then print the values and the ingredients on a product label and move away from spreadsheets completely

 

Many thanks for any guidance you can give me

Chris

Outcomes