2 Replies Latest reply: May 16, 2012 6:22 PM by 930781 RSS

    getting Nutrition composition data via SQL

      I'm in the process of developing a customer report program and along with other data, I'm also after the nutrition composition info that is stored with the formulation (output ingredient of the formulation). I think I'm looking at the correct tables, but I don't see any data returned for the "per Serving" fields. Strangly via Agile itself I can see everything. We have Nutrition Profile data that has come across in the migration from v5 to v6 but that is still referencing the ProcessSheetSpec table and a few Trade Specs. We don't use that anymore. We are about to start rolling up data from IngredientSpecs up, so maybe that will help fill the empty fields (although it didn't on our test system).

      Here is a simple query to get some info out:
      <pre>SELECT [ING Spec#]=spec.specnumber,[Spec Name]=sname.name,[Nutrient]=t4.name,
      [Per 100g] = t2.PerGramMeasure,
      [Per 100g UOM]=ISNULL((select t9.Abbreviation from UOM t8 INNER JOIN UOMML t9 ON t8.pkid = t9.fkUOM where t8.pkid=t2.fkPerGramUOM),''),
      [Per Serving] = t2.PerServingMeasure,
      [Per Serving UOM]=ISNULL((select t9.Abbreviation from UOM t8 INNER JOIN UOMML t9 ON t8.pkid = t9.fkUOM where t8.pkid=t2.fkPerServingUOM),''),
      [Source]=(select nutOrigin.Name from [dbo].[NutritionalOrigin] nutOrigin where nutOrigin.[pkid]=t2.fkSource),
      [Serving Size]=ServingSize,
      [Serving UOM]=(select t9.Abbreviation from UOM t8 INNER JOIN UOMML t9 ON t8.pkid = t9.fkUOM where t8.pkid=t6.fkServingSizeUOM),
      [Servings per Pack]=t6.Servings
      FROM IngredientSpec t1
      INNER JOIN gsmNutrientItem t2 ON t1.pkid = t2.fkSpecID
      inner join specSummary spec on t1.pkid = spec.SpecID
      INNER JOIN SpecSummaryName sname ON spec.pkid = sname.fkSpecsummary
      INNER JOIN comStandardNutrientProperties t3 ON t2.fkNutrient = t3.pkid
      INNER JOIN comStdNutrientPropertiesML t4 ON t3.pkid = t4.fkStandardNutrientProperties
      INNER JOIN gsmFormulationOutput t5 ON t5.fkReferencedMaterial = t1.pkid
      INNER JOIN dwbPackageConfigDO t6 ON t6.fkDWBSpec = t5.pkid
      order by spec.specnumber,t3.SequenceNumber</pre>

      Am I on the right track? Normally I can nut out the connections etc between the tables and find what I'm after but this one has me stumped.


      see ya
        • 1. Re: getting Nutrition composition data via SQL
          amy c - oracle
          hi Stephen,

          Writing reports for formulation specs can be tricky, especially when dealing with the properties of the output material. If you are viewing the data through the formulation spec with the output popup window, those values are a combination of theoretical (or calculated values), manual overrides and what is currently saved to the material spec. All of which are stored differently in the database. For reporting, it's better to pull the data from the output material spec because that would represent the final values. However if you look at the nutrition data on the material spec, we do not have "per serving" values.

          Your query appears to be looking at the output material spec, but then you tie it back to the formulation for the serving size. The "per serving" values that display on the formulation output popup window are calculated, but not saved to the database with the material spec. It sounds like you may be looking for a value that is only calculated for display and not actually stored in the database.

          For "per serving" values, the nutrient profile spec would be the place to find them.
          • 2. Re: getting Nutrition composition data via SQL
            Thanks Amy. I now do my calculations on the fly within my application. So from the database I can get per 100g and I then calc per serving. We also wanted % Daily Intake (Agile has no way of doing this that we can see) and that's a simple calculation from the per serving value.

            The end users have been using my application for the past couple of weeks and are wrapped with how it all functions. Now they don't have to spend time doing manual calculations and sifting through Agile to get other data they required for the report.