Re: Help needed with a FMP 7 Calculation question please

On Thu, 21 Jul 2005 10:57:27 +0000 (UTC), Nick Talley wrote :

> My database includes a table that holds 100's of inventory records.
>
> I need to find a way to show the total inventory value in a portal on a
> separate layout.
>
> I cant figure out how to do this, can anyone help please.
>
> Each record has the following fields
>:Net Value
>:Items On Hand
>: Total Stock Value (Items On Hand*Net Value)
>
> So basically how to I make a calculation to add the totals of :Total Stock
> Value for each record to show a grand total in a portal?
>
> I have a need for this type of calculation for other fields. As soon as I
> know how to do this one the rest should be easy, any help would be greatly
> appreciated.
>
> Nick Talley

Hi Nick,

I assume you would like your portal to show something like :

Desc qty price total
Item A 4 5 20
Item B 6 3 18
Total Stock 38

For the explanation, I will name the table in which you want to create the
portal "Main" and the table holding the actual stock info "Stock". If you
want a portal like the above, the "Total Stock" field is best made in the
"Main" table. Also, it should not be part of the portal but placed outside
the portal.

Now it depends a little how you want to view your stock records. Do you
simply wish to view the entire list of "Stock" records from each "Main"
record ? Then you simply create a calculation field in Stock and Main, and
define the calculation as "1" (without the quotes). Now, create a
relationship from Main to Stock, based on this Constant field. This will
cause all records in Main to become related to all records in Stock
(meaning you can see them all, no matter in which record in Main you are).
Let's call this relationship "ViewStock"

Now, in Main, create a calculation field, and define it as :
Sum (ViewStock::total)

This calculation field will now return the value of the sum of the stock of
all related records (which are all records in "Stock"). Place it below the
portal and you're done.

Now, it is possible that you want to view different (filtered) totals,
depending on which record you are in Main, but then you have to tell me a
little more about how your database is set up.

HTH,

Peter
.

Relevant Pages

  • Re: Help needed with a FMP 7 Calculation question please
    ... >> Nick Talley ... > want a portal like the above, the "Total Stock" field is best made in the ... > Now, in Main, create a calculation field, and define it as: ... > Now, it is possible that you want to view different totals, ...
    (comp.databases.filemaker)
  • Re: sum and do calculation at each change for 900000 rows
    ... means there was a backorder) and stock number. ... with backorders for a stock number in the same month. ... A Totals query will do this. ...
    (microsoft.public.access.queries)
  • Re: Is this possible to SUM?
    ... I wanted to keep the stock in there showing which item was <0 but also to ... add a further column that summed the totals for each location. ... > That is probably a total query. ... > Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: ASP totals and sub-totals from an Access or SQL DB
    ... >and totals on a basic recordset where the sub-totals are to break down say ... >by stock categories in a standard oRSv, ... >This resolved my immediate problems, ... Assuming you're using a record set, you could loop through the record ...
    (microsoft.public.inetserver.asp.db)
  • Help needed with a FMP 7 Calculation question please
    ... My database includes a table that holds 100's of inventory records. ... So basically how to I make a calculation to add the totals of:Total Stock ...
    (comp.databases.filemaker)