13 Replies Latest reply on Oct 28, 2009 2:39 PM by TSGal

    New user. Inventory List

    Gburg

      Title

      New user. Inventory List

      Post

      I am having trouble summarizing fields. We use an inventory list and need to summarize by lot number. My problem is that our warehouse may have the same lot number in 2-4 different rows and in order to get the total number of bags per lot number we have to look at each entry and add the rows together. Is there a way to summarize the inventory list to sort and give totals for lot numbers?

       

      Example

       

      Lot No.       Aisle Units 

      4100            520

      4100            520

                         TOTAL???? How do I get this number?

       

      4200             300

      4200             300

                         TOTAL???? How do I get this number?

        • 1. Re: New user. Inventory List
          TSGal

          Gburg:

           

          Thank you for your post.

           

          FileMaker Pro has several different field types.  One field type is "Summary".  This will add/count/average a found set of records.

           

          When you create a Sub-Summary report, records are grouped together by a common key.  By placing this Summary field in the grouping returns the value for that grouping.

           

          1. Create a new field, Total Units, that is a Summary type equal to a Total of Aisle Units.

           

          2. Create a new layout that includes a Sub-Summary when sorted by Lot No.

           

          3. Put the summary field "Total Units" into the Sub-Summary part.

           

          4. Sort by Lot No., and go to Preview Mode.  You will see the different totals for each Lot No.

           

          You can also place this field in the Grand Summary to get a Total for all records.

           

          Let me know if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: New user. Inventory List
            Gburg
              

            1. Create a new field, Total Units, that is a Summary type equal to a Total of Aisle Units.

             

             

             

             When I set up the new field it will not let me select Aisle Units as an option to total.

            • 3. Re: New user. Inventory List
              comment_1
                 Aisle Units needs to be a Number field.
              • 4. Re: New user. Inventory List
                Gburg
                  

                When I change it to a number it is giving me the total number of units in the warehouse and not aisle.  Any ideas?

                 

                Thanks for yout help!

                • 5. Re: New user. Inventory List
                  comment_1
                     Put it in the sub-summary part. You can have several instances of a summary field on the layout, and each will return a different result based on its placement.
                  • 6. Re: New user. Inventory List
                    Gburg
                       Please explain #3.
                    • 7. Re: New user. Inventory List
                      TSGal

                      Gburg:

                       

                      With your database file open, perform the following steps:

                       

                      1. Pull down the View menu and select "Layout Mode".

                       

                      2. Pull down the Layouts menu and selelct "New Layout/Report..."

                       

                      3. Select "List/Report" and click "Next > "

                       

                      4. Select "Report with grouped data", check the option "Include subtotals",  and click "Next > "

                       

                      5. Double-click on the fields "Lot No.", "Lot No." (again- we want two listings), and "Aisle Units".  Click "Next > "

                       

                      6. Under "Organize Records by Category", double-click the field "Lot No."   Click "Next > "

                       

                      7. Your sort order should now show "Lot No." ascending.  Just click "Next > "

                       

                      8. Under "Specify Subtotals", just below "Summary field" click on "Specify..." and select "Total Units".  The Category to summarize by should already be populated with "Lot No."  Under "Subtotal placement", select "Below record group".  Click "Add Subtotal", and the information will appear in the Subtotals box below.  Click "Next > "

                       

                      9. Under "Select a Theme", select "Default" and click "Next > "

                       

                      10. For "Header and Footer Information", just click "Next > "

                       

                      11. No need to create a script, so just click "Next > "

                       

                      12. Click "Finish".

                       

                      The report will look close to what you want, but it is easy to change.  Pull down the View menu and select "Layout Mode".

                       

                      On the left side, you will see Header, Sub-Summary when sorted by Lot No., Body, and Sub-Summary when sorted by Lot No.

                       

                      Click the first Sub-Summary part and press the delete key.  An error message will appear "Delete this part and all objects in it?"  Click "Delete".

                       

                      In the remaining Sub-Summary part, click on the far left field "Lot No." and delete it.

                       

                      Pull down the View menu and select "Browse Mode".

                       

                      This will show the report as you described in your original post.

                       

                      If you run into any difficulty or need clarification for any of the above steps, please let me know.

                       

                      TSGal

                      FileMaker, Inc.

                      • 8. Re: New user. Inventory List
                        Gburg
                          

                        This worked great. One issue I am still having is getting all of the lot #'s to sort out. Some lot #'s are similar.

                         

                        Example:

                         

                        2SA8 and 2SD8 do not sort out seperately. Any solution to get them to sort out?

                         

                        Thank you!

                        • 9. Re: New user. Inventory List
                          TSGal

                          Gburg:

                           

                          If 2SA8 and 2SD8 are not being separated, then perhaps that field is of type Number; not Text.  This is because in a Number type field, all alpha-characters are ignored.  Therefore 2S18 and 2SD8 both equal 28.

                           

                          Pull down the File menu and select "Manage -> Database...".  Click on the Fields tab and look for the field in question (Lot No., I presume).  If it is set for type Number, then change it to type Text.  Now, sort again, and the report should look correct.

                           

                          TSGal

                          FileMaker, Inc.

                          • 10. Re: New user. Inventory List
                            Gburg
                              

                            I have another issue. I have 2 entries in my warhouse summary that have the identical information except number of UNITS.

                             

                            Example:

                             

                            WH ID Loc.   Lot No.   HYBRID    UNITS       NET SHIPPED   LEFT TO SHIP

                                 511         15541    4T985        450             500                  -50

                              

                                 511         15541    4T985        250             500                 -250

                             

                             

                            My issue is, when I enter 500 NET SHIPPED it automatically takes 500 units from both rows. Is there a way to distinguish between the two?

                            • 11. Re: New user. Inventory List
                              TSGal

                              Gburg:

                               

                              Thank you for your post.

                               

                              It sounds like you are entering 500 NET SHIPPED twice.  You may want to enter this as a separate entry as a negative number, or a separate field for shipping.  You can then create a calculation field that equals UNITS - SHIPPED.  Then, summarize this calculation field.  Therefore you would have:

                               

                               WH ID Loc.   Lot No.   HYBRID    UNITS       SHIPPED   LEFT TO SHIP

                                   511         15541    4T985        450             0                  450

                                

                                   511         15541    4T985        250             0                  250

                               

                                   511         15541    4T985          0              500              -500

                               

                              Total                                            700             500               200

                               

                               

                              This should give you a good start.

                               

                              TSGal

                              FileMaker, Inc. 

                               

                              • 12. Re: New user. Inventory List
                                Gburg
                                   We enter it only once, but since we are sorting by lot number, it takes 500 from each location with that lot number. Is there a way to get around that?
                                • 13. Re: New user. Inventory List
                                  TSGal

                                  Gburg:

                                   

                                  Sorry for the late reply.

                                   

                                  If you are only entering 500 once, then you should make sure you are only accessing the field once.  In my previous posting, I gave you an example of three records; two records for UNITS and one record for SHIPPING.  This way, you will only display the record once.

                                   

                                  TSGal

                                  FileMaker, Inc.