3 Replies Latest reply on Aug 6, 2012 12:01 PM by philmodjunk

# How to calculate sum of a field with same value

### Summary

How to calculate sum of a field with same value

### Product

FileMaker Pro

12

Win7

### Description of the issue

Hi
The problem to be solved is like this, I have a table with:
Table: Input
SKU       Quantity
A                 1
B                 1
A+B               1
Table: Output
A outcome table is to calculate all quantity with A or B like:
SKU       Quantity
A                 2
B                 2

Is there a simple function that can be written for Table Output's Quantity= ????
Thanks

### Steps to reproduce the problem

Set a table
SKU       Quantity
A           1
B           1
A+B         1

### Expected result

another table resulting in:

SKU       Quantity
A           2
B           2

### Actual result

I tried a lot of ways but it only results in:

SKU       Quantity
A           1
B           1

• ###### 1. Re: How to calculate sum of a field with same value

This portion of the forum is intended for reporting possible bugs with the software. If you post questions of this type in the FM Pro Forum it be seen by more users than here.

A 1
B 1
A+B 1

becomes

A 2
B 2

How did the 1's become 2's? What does A+B mean?

I'd guess that you want the total count of all records where a field has a SKU of A and a count of all Records that have a SKU of B in the same field. If so, a Summary report can be constructed such that you get this result, but I am making a lot of guesses at to what you want.

• ###### 2. Re: How to calculate sum of a field with same value

Hi PhiModJunk,thanks a lot for your reply. the key problem is I want Table 2 be able to recognize "A+B" as separate "A" "B" and calculate the quantity corresponds to A+B. Think Table 1 as a sales records, Lisa ordered item A, Linda ordered item B, and Clair ordered items A and items B:

Table 1: Today's Sales Records

Record#      Item       Quantity
#0001              A                1
#0002              B                1
#0003              A+B            1
#0004              C                2
#0005              B+C            3

Table 2 intends to calculate the sum of item A and item B totally ordered respectively.

Table 2: Stock Dispatched

Item       Quantity
A                2           //to sum(1+1)
B                5           //to sum(1+1+3)
C                5           //to sum(2+3)

I guess the key is how to solve the "+" as in fact a separate record, as some customer does buy multiple items in a single record, I hope this makes it better understood the issue. Cheers.

• ###### 3. Re: How to calculate sum of a field with same value

ome customer does buy multiple items in a single record

This is the issue creating the problem.

Invoicing system generally have this set of tables and relatinships:

Customers----<Invoices-----<LineItems>-------Products/Services

A single invoices record will document a given sales transaction, but the individual items purchased are listed as individual records in the lineItems table. And you can then produce your report from the LineItems table where each item purchased is on a different record.

See this simple demo file: http://fmforums.com/forum/showpost.php?post/309136/