Newbies confusedmuch Posted September 26, 2015 Newbies Share Posted September 26, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
Steve Martino Posted September 26, 2015 Share Posted September 26, 2015 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 Link to comment Share on other sites More sharing options...
Kris M Posted September 27, 2015 Share Posted September 27, 2015 "..is not so simple" + 1 Link to comment Share on other sites More sharing options...
Lee Smith Posted September 27, 2015 Share Posted September 27, 2015 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. Link to comment Share on other sites More sharing options...
bruceR Posted September 27, 2015 Share Posted September 27, 2015 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 Link to comment Share on other sites More sharing options...
Newbies confusedmuch Posted September 29, 2015 Author Newbies Share Posted September 29, 2015 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. Link to comment Share on other sites More sharing options...
comment Posted September 29, 2015 Share Posted September 29, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3266 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now