Jump to content

Simple inventory database


This topic is 3067 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Table_view.thumb.jpg.7650f6cf8567e6ce547

Manage_fields_view.thumb.jpg.7cd9b9cea18

 

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.

Excel.thumb.jpg.a9a105f32ffb43fe179e8317

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

 

Edited by confusedmuch
Didn't attach pictures properly
Link to comment
Share on other sites

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

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

  • Newbies

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

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 by comment
Link to comment
Share on other sites

This topic is 3067 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.