5 Replies Latest reply on Feb 12, 2012 6:40 AM by philmodjunk

    Inventory by lots



      Inventory by lots


      I'm of moderate fmp skill, designed about 10 solutions for my own business's but this ones driving me crazy.


      I am trying to make an inventory database that will allow me to view by lot. I purchase large quantities of items by the pallete, up to 150 items per pallet. So while I need to keep track of every individual item, how much it sold for and what shipping was, I want to be able to look at the overall lot and see my profit margins for that purchase.


      In addition I want to have my most frequently purchased items per entered for a pull down (hundreds of items) so that it streamlines my estimation process.I understand I'm going to have to make separate tables and then link them together but that's been a bit over my head (novice coder).


      Any suggestions, know of any templates that have this functionality?  



      **** I'm just using numbers spreadsheets and grouping the cells together but I would MUCH rather be using fmp. i have uploaded a screen shot, as you can seel i have a "band lot in blue" with the subtotals for all the expense, revinue, ebay fee, paypal fee, shi cost and then proffit. Then each item is itemized under neith. Then you see another blue bar with another lot below, rinse wash repeat.

      The database would display the same info however i would be able to view it as im showing here, lot by lot so i can see the overall picture of my purchase, but then easrch ALL dj hero 2 cases for example and see what each one sold for across multiple lots. This would give me a good idea of how the item sells over time


        • 1. Re: Inventory by lots

          Would a purchase order consist of a single "lot"? or might a single order be made up of multiple lots?

          If one lot = one purchase order, you might take a look at the Purchase Order starter solution that comes with FileMaker. It will have layouts and tables already set up for you.

          You can also take a look at this much simpler invoice demo: http://fmforums.com/forum/showpost.php?post/309136/

          Invoicing systems and Purchase Order systems are set up with the same structure, you just change the name of one table from invoice to purchase order and your money travels in the opposite direction, so this simpler demo makes a good example file to understand the basic structure and function (which is also used by the more complex invoice and purchase order starter solutions.)

          • 2. Re: Inventory by lots

            thanks for the direction.


            I downloaded your simple example and started to customize it, im running into the same hichups as always however. (see attached pic). The view im shoing is the lot view. this would show each item that the lot has that i purchased. i really like being able to pick a item from a pull down and all the info populates, that makes estimating lots before i purchase fast and easy. However once i own the lot, i may need to change number, just in this case, like for example this item has a big scratch so i need to discount it over what i would normally charge.


            1. at the bottom i am looking to have the total rev show, however it will not update with the altered revenue as shown on the page, it always stays on the origonal ammount.

            2. in the ebay, paypal and profit columns i used a calculation to come up with the answers, however in the ebay colum, some times i need to change it but since its a calc it wont let me.

            3. what im showing is hte lot view. its made up of products, which are just quick fills. lets say i add 6 of the same product here, but each one is a bit different (new, used, sold in a different matter etc) i want to make a master view showing all of the ENTERED products in one giant list. (shows all entered products from all lots.) im at a huge loss on how to do that one.

            • 3. Re: Inventory by lots

              However once i own the lot, i may need to change number, just in this case, like for example this item has a big scratch so i need to discount it over what i would normally charge.

              That shouldn't be a problem. If you use a drop down list for this field, you can also edit the field to change it and if that causes other fields in the portal to go blank, there are options you can specify in the looked up value auto-enter settings that prevent this. It will then require you to delete items instead of chaning them if you select the wrong item by mistake, but I wouldn't expect that to be too difficult to manage.

              1) what type of field is Total Revenue? in what table is it defined? (Is this a number field with an auto-entered calculation? Is it a summary field from line items?)

              2) Is this the field inside the portal for ebay or the total shown at the bottom? If you want to change the ebay amount on an item by item basis, you can define the field as a number field with an auto-entered calculation. That will compute the original value, but it is then possible to edit the field after the fact. Make sure the "do not replace existing value..." option is selected if you want this capability.

              If you want the total at the bottom to be editable, you can use an auto-entered calculaiton field for this as it won't update correctly when you edit a field from the portal, so that option won't work for the total. You can still modify that total, by either adding an extra line item record to adjust the total or by adding a number field to the parent record for entering adjustment amounts and then including this field in the calculation.

              3) Set up a list view layout based on your portal table instead of Lots. Take a look at the layout used for printing invoices in Comment's invoice demo for an example. A similar layout can be set up where you can list all records instead of just those for a given Lot and you can also pull up sub sets of the total such as all items purchased during a given date range.

              • 4. Re: Inventory by lots

                1) the total rev field is a calc     sum (products::listprice)  its pulling from the actual product listing table


                2) changing this to have an auto calc workd, i can now edit it, however when i edit it in the lot view (in the portal) it changes it back on the product table, which i dont want to have happen. if i am altering it, its just for that item only in that table, i dont want it to change universally.


                3) how am i able to base it off the portal data, when i go to create one it only allows be to use table data.

                • 5. Re: Inventory by lots

                  1) You should set up a unitprice field in the line items table that looks up the listprice value. Then have you calculation sum the extended cost field that computes quantity * unitprice. This will allow you to make changes to the looked up unit price, the Quantity or to add/remove line item records and the total will update to raflect all of these changes.

                  2) Like #1, this should be a field in line items, not products. This way you are working with a copy of the data instead of the original value in Products.

                  3) a calculation can reference data in any related table in your database and also global fields from non related tables. In your case, I think resolving 1 and 2 above need to take place first, then you can revisit this one if it still doesn't work the way you need. (I recommend using the sum calculation for portal totals when you use the portal to edit values that affect the totals computed. The total shown updates much more smoothly than other methods.)