1 Reply Latest reply on Feb 19, 2012 11:52 AM by bumper

    Calculate a value by its ITEM name



      Calculate a value by its ITEM name


      Hi, I am using a layout to present the total calculation of a item's value which is entered in different records. Like lets say I sold 10KG APPLES in 5 different records. Now I have a drop down box which shows the item names and when apple is selected the field of Total sold KG should show 10. 

        • 1. Re: Calculate a value by its ITEM name

          This can be done with self-relatiohship (the same table on both sides of the TO) or you can make it more like an invoice to line items relationship.

          I'll describe the one table method, for clarity I'll call it Fruits and assume you already have created the Item and Value fields.

          1. Create a global text field "Item_g"

          2. In the relationship graph duplicate the table, Fruits, and make a relationship from Item_g in "Fruits" to Item in "Fruits 2"  

          3. Go back to fields and create a calculation field, results number, named Total, FROM the context of "Fruits" and enter the formula, Sum (Fruits 2::Value)

          4. Create a vaule list of Items of the field Item.

          5. Create a layout from the context of Fruits, put the Fruits_g field (put a drop down value list Items on this field) and a portal of Fruits 2 with the fields Item and Value. Put the Total under the portal.

          Now go into browse mode select the Item you want in the Item_g field, your portal should show only the matching records and your Total should add up to the sum of all the values in the applicable records.

          The above is a different spin on an invoice and line items with a different filter and field being summed. You may also want to add another predicate, Date. to the relationship if you want to see what the Value is for a specific date or range of dates, assuming you already have a date created field in the Fruit records. table.

          Note the same results can be achieved in a sub summary report or with summary fields in table view of Fruits; using finds and sorts.