Simple inventory database

[confusedmuch]

I am relatively new to Filemaker and am trying to make a simple database for taking inventory of fabrics. I have a table with the fields Quantity added, Quantity issued and Quantity in stock. I would like the Quantity in stock field to be  Quantity added - Quantity issued for the first record and for subsequent records I would like it to be Previous value of Quantity in stock + Quantity added - Quantity issued. I don't know how to do this in Filemaker. Here is a screenshot of my table as it stands now.

 

I can do this quite easily in Excel. In the example below, for the first Quantity in stock value, I have the cell F2 = C2. Subsequently (F3 onwards), I have the value as being F2+C3-C4 and so on.

I'd really like to replicate this in Filemaker so that I can have an accurate value in the Quantity in stock field.

 

Edited September 26, 2015 by confusedmuch
Didn't attach pictures properly

[Steve Martino]

I guess the first thing you figured was:

...am trying to make a simple database for taking inventory ...

..is not so simple.  I would look here:

https://www.filemaker.com/solutions/starter-solutions.html

at the Inventory Starter solution, or here:

http://www.fmstartingpoint.com/

for some ideas regarding structure 

[Kris M]

"..is not so simple" + 1

[Lee Smith]

Here is a very helpful video and example of inventory by Todd Geist https://www.geistinteractive.com/2010/04/21/inventory-transactions/

that you might find helpful.

[bruceR]

And for a much broader discussion about database transaction concepts, I see that Jeremy Bante found an re-tweeted a link to this presentation.

But that presentation is way beyond the notion of "simple inventory"

Transactions: Myths, Surprises, and Opportunities

https://speakerdeck.com/ept/transactions-myths-surprises-and-opportunities

[confusedmuch]

Thank you all for chiming in. What a great community this is! I haven't had the chance to go through all the helpful links but will do so as soon as I can. Meanwhile, I was wondering if it would be possible to attack the problem this way.

Problem (recap): Replicate the Excel formula from the OP in a FileMaker Pro database to track inventory

Could this be done? Have a calculation for the Quantity in stock field such that the first record is the same as the Quantity added and the subsequent records are Previous value from Quantity in stock + Quantity added - Quantity issued

Could the If and Get commands along with the recordNumber reference be used to arrive at a solution. Let's say, If recordNumber is 1 then Quantity in stock = Quantity added, otherwise Quantity in stock = getRecord Quantity in stock-1 + Quantity added - Quantity issued 

Maybe this makes no sense at all! Just wondered if this would be possible.

[comment]

Problem (recap): Replicate the Excel formula from the OP in a FileMaker Pro database to track inventory

Could this be done? 

Yes, it could be done quite easily. All you need to do is add is a calculation field cQuantity (result is Number) =

Quantity added - Quantity issued

and a summary field defined as Total of cQuantity, running. Even simpler, use just one Quantity field and enter either a positive or a negative number according to the type of transaction. Then have the summary field summarize this field directly.

The real question, however,  is whether this should be done.  Filemaker is not a spreadsheet. This method will grow slower and slower as the number of transactions increases. Moreover, summary fields summarize the current found set; if you show only some records, the summary value shown will not reflect the real situation.With a running total, the order of records is also significant.

Note also that this assumes there's only one type of material (as there is in your Excel example).

Edited September 29, 2015 by comment