1 2 3 4 Previous Next 59 Replies Latest reply on Jun 23, 2017 8:11 AM by chrishill

    Nutrition Database Calculations

    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

        • 1. Re: Nutrition Database Calculations
          Jaymo

          If the calculation result is repeating the same value, it could be you created the formula in the parent table rather than the child (where the portal displays information). You won't need SQL, just a calculation in the correct context. If this doesn't solve your problem, post the actual formula you are using. Thanks.

          • 2. Re: Nutrition Database Calculations
            chrishill

            Thanks for the reply.

            If i put the following formula in "Products":

            (Nutrient Matrix::Nutrient Value / 100) * Formulae::Inclusion

            I get the following result:

            Screen Shot 2017-04-12 at 10.39.16.png

             

            Where 1.3 is the OilB Contribution from Hipro Soya. (2.6/100) * 50 = 1.3

             

            Which I suppose, is what one would expect when looking at the table view of 'formulae'.

            Screen Shot 2017-04-12 at 11.12.07.png

             

            (In my fiddling I seem to have ended up with multiple records for the same product also - see 2,3,4 and 25,38.

            Perhaps I would be better off with a separate table for "materials' rather than a separate occurence in the graph.)

             

            Thanks.

            • 3. Re: Nutrition Database Calculations
              Magnus Fransson

              Hi Chris Hill,

               

              I'm glad to see that you have read and implemented what I wrote a long time ago.

               

              Since this type of "problem solving" is my favorite thing to do, I have actually been thinking about this quite alot.

               

              I hope that you have FileMaker Pro Advanced, cause I believe you will need the recursive capability of Custom functions, as well as ExecuteSQL() to solve this. And having the data viewer will prove essential for testing and debugging.

              I'm sad that I don't have more time right now to help you, but I will be back in next week (after the easter weekend, and a few more days maybe) be sure on that.

               

              With best regards Magnus Fransson.

              • 4. Re: Nutrition Database Calculations
                philmodjunk

                Hi Chris,

                 

                I've looked at this one before and even started noodling around with a solution, but realized that it would take a LOT of time to work out and test--more than I have time to do in the forum as free advice.

                 

                If you can't work this out on your own, and if someone doesn't kindly volunteer the help needed, you may need to hire the services of someone to work with you in order to produce a solution.

                • 5. Re: Nutrition Database Calculations
                  Magnus Fransson

                  Hi Chris Hill,

                   

                  Just a small list of "homework" until the time after the next time.

                   

                  • Look up the funktion: GetFieldName.

                  • Look up the funktion: Substitute.

                  • Look up the funktion: GetValue.

                  • Try the calculation: GetValue ( Substitute ( GetFieldName ( YourTable::YourField ) ; [ "::" ; "¶" ] ) ; 1 )

                  • Try the calculation: GetValue ( Substitute ( GetFieldName ( YourTable::YourField ) ; [ "::" ; "¶" ] ) ; 2 )

                  • Look up the funktion: EvaluationError.

                  • Look up the funktion: ExecuteSQL.

                  • Look up the funktion: Let.

                   

                  With best regards Magnus Fransson.

                  1 of 1 people found this helpful
                  • 6. Re: Nutrition Database Calculations
                    chrishill

                    Hi, and thanks for your replies everyone.

                    We do have Filemaker Pro Advanced - version 13.0v4.

                     

                    I will have a look at the homework over the next few days.

                     

                    It's a shame that I don't have filemaker pro at home so I could fiddle with it of an evening - although it is 'work' I enjoy this kind of thing!

                     

                    Thanks

                    Chris

                    • 7. Re: Nutrition Database Calculations
                      beverly

                      The license does allow a "home install" if you have the medium to do so (DVD, download link) and the license key. If this is set by admin at an office, you may not be able to do this. You cannot, however, 'runs' both installs at the same time...

                      beverly

                      1 of 1 people found this helpful
                      • 8. Re: Nutrition Database Calculations
                        Magnus Fransson

                        Hi chrishill,

                         

                        Sorry for taking so long time to come back.

                        Looking at your pictures it appears you have done everything correct so far.

                        Thus: what I'm about to suggest is things that I believe will be improvements. But it is up to you to choose whether to use them or not.

                         

                        • Add a calculation to "Nutrient Matrix" that turn "Nutrient Value" in to a normalized form (depending on chosen unit). To me it seems make mathematical sense.

                         

                        • In the "Formulae" table, store the "Inclusion" as some uniform unit of Mass (kg, Oz, stones or what ever, I don’t care as long it is uniform and mass). If you want to you can add a unit field and a calculation field that will normalize the value to a uniform mass. If you need % for some future use, it can easily be calculated at a later state.

                         

                        • If you are breaking up nutrients (like fat into saturated fat, monounsaturated fat and polyunsaturated fat) you might wan to add a sorting number field, and a "indent" Boolean field to the "Nutrients" table.

                         

                        A note about the two calculations I asked you to study last time. The usefulness of them only appears if you change either table name or field name while studying the result of the calculations. Create a test file (called "X") and make a table, a few fields and those calculations. Watch the result of the calculations and try to rename the table or the field. Those calculations makes the ExecuteSQL() calculation we are about to make more robust.

                         

                        As for test data, I have a few requirements:

                        • There should be more than one recipe.

                        • There should be a recipe with more than two ingredients.

                        • The amount of the ingredients should vary.

                        • More then one and less then all ingredients should share an nutrient.

                        • The concentration of the nutrient should vary between the ingredients.

                         

                        Also check out beverlys excellent “missing reference to the ExecuteSQL() function”.

                         

                        With best regards Magnus Fransson.

                        1 of 1 people found this helpful
                        • 9. Re: Nutrition Database Calculations
                          Magnus Fransson

                          Hi chrishill,

                           

                          Could you post a sample file for me/us to work on? Then I can post your first sample calculation to test in the data viewer.

                           

                          With best regards Magnus Fransson.

                          • 10. Re: Nutrition Database Calculations
                            chrishill

                            Hi, @Magnus Fransson, many thanks for your continued attention.

                            I looked at the calculations you left before and I can see what they do - remove the :: and replace it with a carriage return, then return either the first or second result from the subsequent list of values - and that the result changes appropriately if the tables are renamed.

                            Am I right in thinking therefore that an executeSQL function is not 'dynamic' like that, and that is when these functions become useful to ensure it doesn't break if the tables are renamed?

                            I have printed off @beverly s SQL reference info and will study that.

                             

                            I'll post a sample file later - I'd better remove our clients' customers' data first, before putting it on a public forum!

                            • 11. Re: Nutrition Database Calculations
                              Magnus Fransson

                              Hi chrishill,

                               

                              chrishill wrote:

                               

                              Am I right in thinking therefore that an executeSQL function is not 'dynamic' like that, and that is when these functions become useful to ensure it doesn't break if the tables are renamed?

                               

                              You have got the underlying problem and the proper solution to it right!

                               

                              But the culprit is not the ExecuteSQL() function, but how we pass parameters to it. (Or how we pass parameters to any function.) Passing the content of a field or a variable are no problem, neither is passing constants. (Though some argues strongly against "Magic numbers".) The problem can (if carelessly done) arise when either the table name or the field name them self is to be passed as a parameter. It is quite common that those are (wrongfully) sent as "hard coded" constants. Those calculations make table names and field names "dynamic".

                               

                              With best regards Magnus Fransson.

                              2 of 2 people found this helpful
                              • 12. Re: Nutrition Database Calculations
                                chrishill

                                Thankyou for your explanation, that makes perfect sense.

                                 

                                Here is a link to the sample file which I have stripped of everything that could get me into trouble with the data protection act:

                                https://www.dropbox.com/s/l1e7eq7n1ld6uyp/nutritional_calcs.fmp12?dl=0

                                 

                                From the main menu, under 'Production' are options for :

                                'Nutrients', 'Rm Matrix' and 'Formula Matrix'

                                 

                                All the nutrients that I am using at the moment are created.

                                All the RM's that are in use are populated with nutrients.

                                There is a range of Formulae which between them should fulfil all the testing criteria.

                                 

                                A few bits probably look a bit messy - there are a few calculations not actually in use where I have been trying things out etc.

                                (Also, The raw material data entry on 'formula matrix' is currently behaving a bit strangely - I recently introduced a conditional value list to only show products I define as Raw Materials in the drop down, and now I have to enter a value in the Inclusion field (or type over the raw material field then click away then back) before I can access the drop down)

                                 

                                You mentioned storing the inclusions (and nutrients) as normalised units of mass - I haven't done this yet - I can put it behind the scenes if it makes the calculation simpler - but it isn't something I would use on a day-to-day basis. Industry-wide (everywhere I have dealt with in the UK at least), formulations are handled using % inclusion - based on the principle that your formulation must always equal 100%.  - the process control software would then convert to kg according to to the physical size of the plant for manufacturing as my batches/mixing card layouts do.

                                I would opt for kg if it was necessary.

                                 

                                The nutrient units, again are industry standard, and it is also a legal requirement to declare on product labels using those units.

                                They would also be harder to decide on a unit to normalise to, e.g. 1kg of finished feed might contain 65g of Ash, but 0.167g of Iron, 0.002g of Cobalt, and I'm not sure about the conversion of 'International Units' to mass for Vitamins, but we would be talking about several decimal places. Chelated minerals (not that I am using any here) are complicated as they have variable-length chains. Then when one gets into poultry feeds and Enzyme activity, that's a different world!

                                 

                                Best Regards,

                                Chris

                                • 13. Re: Nutrition Database Calculations
                                  Magnus Fransson

                                  Hi Chris,

                                   

                                  chrishill wrote:

                                   

                                  Here is a link to the sample file which I have stripped of everything that could get me into trouble with the data protection act:

                                  https://www.dropbox.com/s/l1e7eq7n1ld6uyp/nutritional_calcs.fmp12?dl=0

                                  From the main menu, under 'Production' are options for :

                                  'Nutrients', 'Rm Matrix' and 'Formula Matrix'

                                   

                                  I downloaded and opened the file without any trouble. I have already added a portal and a few fields to better choose a good candidate for testing.

                                   

                                  chrishill wrote:

                                   

                                  You mentioned storing the inclusions (and nutrients) as normalised units of mass - I haven't done this yet - I can put it behind the scenes if it makes the calculation simpler - but it isn't something I would use on a day-to-day basis. Industry-wide (everywhere I have dealt with in the UK at least), formulations are handled using % inclusion - based on the principle that your formulation must always equal 100%. - the process control software would then convert to kg according to to the physical size of the plant for manufacturing as my batches/mixing card layouts do.

                                  I would opt for kg if it was necessary.

                                  The nutrient units, again are industry standard, and it is also a legal requirement to declare on product labels using those units.

                                  They would also be harder to decide on a unit to normalise to, e.g. 1kg of finished feed might contain 65g of Ash, but 0.167g of Iron, 0.002g of Cobalt, and I'm not sure about the conversion of 'International Units' to mass for Vitamins, but we would be talking about several decimal places. Chelated minerals (not that I am using any here) are complicated as they have variable-length chains. Then when one gets into poultry feeds and Enzyme activity, that's a different world!

                                   

                                  Do whatever works best for you! As I said, my suggestions are optional.

                                   

                                  Hoping to publish the first test calculation soon.

                                   

                                  With best regards Magnus Fransson.

                                  • 14. Re: Nutrition Database Calculations
                                    Magnus Fransson

                                    Hi Chris,

                                     

                                    I have a first calculation for you to test in the data viewer.

                                     

                                    But before that can work you have to correct two errors in the Formulae table. Since you are using UUID stored in a text field in the Products table the Formulae::ProductsIDfk field and the Formulae::MaterialsIDfk field must both be text fields, otherwise the calculation will throw an error.

                                     

                                    Once you paste the calculation in to the data viewer you will find that it gives multiple rows as answer.

                                    Let
                                    (
                                        [
                                            Formula_UUID = "04/05/2017 10:58:09 B88A7536-1A8C-425B-B1C4-7FEDC6A7A675"    // Beef Finisher 14 Blend
                                        ;
                                            Nutrient_ID = "3"    // Fibre
                                        ;
                                            Formula_Table = Quote ( GetValue ( Substitute ( GetFieldName ( Formulae::id ) ; [ "::" ; "¶" ] ) ; 1 ) )
                                        ;
                                            Formula_ProductFk = Quote ( GetValue ( Substitute ( GetFieldName ( Formulae::ProductsIDfk ) ; [ "::" ; "¶" ] ) ; 2 ) )
                                        ;
                                            Formula_MaterialsFk = Quote ( GetValue ( Substitute ( GetFieldName ( Formulae::MaterialsIDfk ) ; [ "::" ; "¶" ] ) ; 2 ) )
                                        ;
                                            Formula_Inclusion = Quote ( GetValue ( Substitute ( GetFieldName ( Formulae::Inclusion ) ; [ "::" ; "¶" ] ) ; 2 ) )
                                        ;
                                            NutrientMatrix_Table = Quote ( GetValue ( Substitute ( GetFieldName ( Nutrient Matrix::id ) ; [ "::" ; "¶" ] ) ; 1 ) )
                                        ;
                                            NutrientMatrix_NutrientsFk = Quote ( GetValue ( Substitute ( GetFieldName ( Nutrient Matrix::NutrientsIDfk ) ; [ "::" ; "¶" ] ) ; 2 ) )
                                        ;
                                            NutrientMatrix_MaterialsFk = Quote ( GetValue ( Substitute ( GetFieldName ( Nutrient Matrix::MaterialsIDfk ) ; [ "::" ; "¶" ] ) ; 2 ) )
                                        ;
                                            NutrientMatrix_Value = Quote ( GetValue ( Substitute ( GetFieldName (Nutrient Matrix::Nutrient Value ) ; [ "::" ; "¶" ] ) ; 2 ) )
                                        ;
                                            TAB = Char(9)
                                        ;
                                            SQL = "SELECT " & Formula_Table & "." & Formula_Inclusion & ", " & NutrientMatrix_Table & "." & NutrientMatrix_Value & " FROM " & Formula_Table & " JOIN " & NutrientMatrix_Table & " ON " & Formula_Table & "." & Formula_MaterialsFk & " = " & NutrientMatrix_Table & "." & NutrientMatrix_MaterialsFk & " WHERE " & Formula_Table & "." & Formula_ProductFk & " = ? AND " & NutrientMatrix_Table & "." & NutrientMatrix_NutrientsFk & " = ?"
                                        ;
                                            Error = EvaluationError ( ExecuteSQL ( SQL ; TAB ; "¶" ; Formula_UUID ; Nutrient_ID ) )
                                        ;
                                            IsError = GetAsBoolean( Error ≠ 0 )
                                        ;
                                            Result = Case( not IsError ; ExecuteSQL ( SQL ; TAB ; "¶" ; Formula_UUID ; Nutrient_ID ) )
                                        ]
                                    ;
                                        "SQL: " & SQL & "¶Error: " & Error & "¶Result:¶" &
                                        Result
                                    )
                                    

                                    The three first rows are debug info that will be suppressed in the finale version of the calculation. Analyze the calculation and its result thoroughly and if there are something you don’t understand post it here and you shall receive help.

                                     

                                    With best regards Magnus Fransson.

                                    1 of 1 people found this helpful
                                    1 2 3 4 Previous Next