1 2 Previous Next 19 Replies Latest reply on Oct 17, 2012 4:30 PM by hobbiesdeveloper

    Inventory Ordering Information

    hobbiesdeveloper

      Title

      Inventory Ordering Information

      Post

           Hello,

           Is there a way to have my Inventory stock keep track of orders and purchase orders for up to 1 year before not being in the on Purchase Order and Qty Sold displayed quantity.

           Not sure how others deal with having to large of a quantity displayed after a year worth of orders and purchase orders reflecting the Qty on Hand on the Inventory module but I would like to have the transactions dissapear from the Inventory Info after a year worth of Orders and Purchase Orders that reflect the Qty on Hand.

           Please let me know what is the best way to deal with keeping track of inventory for sold orders and purchase orders after a long period of time?

            

           Thanks.

        • 1. Re: Inventory Ordering Information
          philmodjunk

               Is this a starter solution file that you are working with?

               If so, please identify the file and the version of FileMaker you are using. (Filemaker 12 starter solutions are quite different from those distributed with earlier versions.)

               If not, please describe your current database design in more detail.

          • 2. Re: Inventory Ordering Information
            hobbiesdeveloper

                 This is not a starter solution file. I' currently using Filemaker 11.

                  

                 I currently have calculation fields setup for the Qty on Hand, Qty Sold and so on setup.

                 Everytime an order is created it reflects the inventory Qty Sold and Qty Total Sold, than the Re-Order Amount calculation fields automatically shows the necessary Re-Order Amount. Once the PO is received for the Re-order amount it stores the total amount from all the PO's to balance out with the Qty Sold and the Initial Level Stock.

                 I have included a pictures of the database inventory table with one order with a quantity of 20 ordered. As you can see i had to place a po for an amount of 5 to come out even. As you can see the on PO value will always increase no matter if you have 100 Purchase orders, it will show the total quantity ordered for that inventory item. Is there a way for the old purchase orders to not display on the on PO and the Qty Sold to not show high values like on PO: 10,000 and Qty Sold: 10,000.

                 Not sure how you would deal with excessive numbers once it reaches that point.

                 I hope this info helps.

            • 3. Re: Inventory Ordering Information
              philmodjunk

                   And what tables and relationships are you using to keep track of all of this?

              • 4. Re: Inventory Ordering Information
                hobbiesdeveloper

                     All i want to know if there are any ways to prevent orders and purchase orders from counting once it reaches a current time frame.

                     Could you please let me know which ways are best in dealing with orders automatically not counting inside the onPO calculations and Qty Sold calculations.

                     To go over the relationships and tabls that would take a while to get due to how big the database is at this point.

                      

                     Any helps is appreciated.

                • 5. Re: Inventory Ordering Information
                  philmodjunk

                       That is help I cannot provide without knowing what you have to begin with. There are multiple methods for tracking inventory levels and I need to know what you have in place now before I can suggest a modification that does what you need.

                  • 6. Re: Inventory Ordering Information
                    hobbiesdeveloper

                         How do you want me to provide the relationships and tables, do you need snapshots of the table relationships for orders?

                    • 7. Re: Inventory Ordering Information
                      philmodjunk

                           There are several approaches that work. A screen shot of the relationships isn't really enough by itself as you need to supply some descriptions of which table occurrence is which and how they are intended to function in terms of inventory management.

                           There's also this method for documenting tables and relationships: Common Forum Relationship and Field Notations Explained

                           But you don't have to upload everything, try to focus on the the process used to compute the totals shown in your first screen shot. You may also need to describe or post some scripts if you rely on scripting to produce the totals shown.

                           To post a script to the forum:

                             
                      1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                      2.      
                      3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                      4.      
                      5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                      6.      
                      7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
                      • 8. Re: Inventory Ordering Information
                        hobbiesdeveloper

                             I hope this helps, these are all calculation fields to calculate the Inventory. I don't use Scripts, i believe it's best to use direct calculations to show inventory results.

                             Let me know if you need anything else to determine the method.

                                                                                                                                         
                                            Quantity_on_Hand                     Calculated, Number                     Calculation:                                          Auto-Enter:                     
                                                   
                        •                               Always evaluate
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Do not store calculation results
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Orders
                        •                          
                        •                               Products
                        •                          
                        •                               Product List
                        •                     
                                       

                                                                                                                                         
                                            Qty Sold                     Calculated, Number                     Calculation:                     
                                                   
                        •                               Context table: Product
                        •                          
                        •                               Sum ( product_ORDERLINEITEM::Quantity )
                        •                     
                                            Auto-Enter:                     
                                                   
                        •                               Always evaluate
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Do not store calculation results
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                          
                        •                               Product List
                        •                     
                                       

                                                                                                                                         
                                            Qty Total Sold                     Calculated, Number                     Calculation:                     
                                                   
                        •                               Context table: Product
                        •                          
                        •                               Sum ( product_order_LINEITEM::Quantity )
                        •                     
                                            Auto-Enter:                     
                                                   
                        •                               Always evaluate
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Do not store calculation results
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                     
                                       

                                                                                                                                         
                                            onPO                     Calculated, Number                     Calculation:                     
                                                   
                        •                               Context table: Product
                        •                          
                        •                               Sum( ProductLineItem::Quantity )
                        •                     
                                            Auto-Enter:                     
                                                   
                        •                               Always evaluate
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Do not store calculation results
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Orders
                        •                          
                        •                               Products
                        •                          
                        •                               Product List
                        •                     
                                       

                                                                                                                                         
                                            Reorder Amount                     Calculated, Number                     Calculation:                                          Auto-Enter:                     
                                                   
                        •                               Always evaluate
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Do not store calculation results
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                          
                        •                               Product List
                        •                     
                                       

                                                                                                                                         
                                            Qty Received                     Calculated, Number                     Calculation:                     
                                                   
                        •                               Context table: Product
                        •                          
                        •                               Sum ( ProductLineItem::received_Quantity )
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Do not store calculation results
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                     
                                       

                                                                                                                                         
                                            Minimum Stock                     Normal, Number                     Auto-Enter:                     
                                                   
                        •                               Allow editing
                        •                     
                                            Validation:                     
                                                   
                        •                               Only during data entry
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Indexing: None
                        •                          
                        •                               Automatically create indexes as needed
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                     
                                       

                                                                                                                                         
                                            Initial Level                     Normal, Number                     Auto-Enter:                     
                                                   
                        •                               Allow editing
                        •                     
                                            Validation:                     
                                                   
                        •                               Only during data entry
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Indexing: None
                        •                          
                        •                               Automatically create indexes as needed
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                     
                                       

                                                                                                                                         
                                            Balance                     Calculated, Number                     Calculation:                     
                                                   
                        •                               Context table: Product
                        •                          
                        •                               Product::onPO - Product::Qty Received
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Do not store calculation results
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                     
                                       

                                                                                                                                         
                                            Qty Adjusted                     Normal, Number                     Auto-Enter:                     
                                                   
                        •                               Allow editing
                        •                     
                                            Validation:                     
                                                   
                        •                               Only during data entry
                        •                     
                                            Storage:                     
                                                   
                        •                               Repetitions: 1
                        •                          
                        •                               Indexing: None
                        •                          
                        •                               Automatically create indexes as needed
                        •                          
                        •                               Index Language: English
                        •                     
                                       
                                                                  
                                                   
                        •                               Products
                        •                     
                                       

                              

                        • 9. Re: Inventory Ordering Information
                          philmodjunk

                               Can you also document the relationships used in these calculations? I can make some pretty good guesses, but would hate to guess wrong.

                          • 10. Re: Inventory Ordering Information
                            hobbiesdeveloper

                                 Here is the Relationship information that is shown on the report next to the Field Name __kp_ProductID

                                  

                                  

                            Let me know know what else you would need.

                            • 11. Re: Inventory Ordering Information
                              philmodjunk

                                   I'm sorry but that really doesn't document the relationships. It doesn't list all the table occurrences and match fields involved. It just lists the occurrences for the relatiosnhips where that one ID field is used--without any info on what match fields were used for the related table.

                                   A screen shot of Manage | Database | Relationships--something I suggested earlier, would be a much more complete picture.

                                   I  can respond in a general way that if you are totaling values from a related table and don't want a total of all reated records, you need to use a relationship that matches only to a subset of the related records and if you have a total that is a summary field defined in the layout's table, you can likewise limit the total shown in it by changing what records are present in the layout's found set.

                              • 12. Re: Inventory Ordering Information
                                hobbiesdeveloper

                                     I hope this screenshot helps. I took a picture of the Products Table Relationships. Very sorry if I'm not getting the correct data to you.

                                • 13. Re: Inventory Ordering Information
                                  philmodjunk

                                       Your specific question concerned the OnPO field which is defined as:

                                       Sum ( ProductLineItem::Quantity )

                                       and uses this relationship:

                                       Product::__kp_ProductID = ProductLineItems::_kf_ProductID

                                       to determine which records in ProductLineItem to sum.

                                       If you defined this unstored calculation field, cThisYear as: Year ( Get ( CurrentDate ) )

                                       and cYear in ProductLineItems as: Year ( DateField ) ---this can't be an unstored calculation, you may have to auto-enter a date from the PO table.

                                       Then this relationship:

                                       Product::__kp_ProductID = ProductLineItems|ThisYear::_kf_ProductID AND
                                       Product::cThisYear = ProductLineItems|ThisYear::cYear

                                       will match to only records for a given product ID dated with this year's date, and then

                                       Sum ( ProductLineItems|ThisYear::Quantity )

                                       will be limited to data with this year's date.

                                       and a more sophisticated calc could be used that "rolls" so that it sums from all records from the preceding 12 months if that should be desirable.

                                  • 14. Re: Inventory Ordering Information
                                    hobbiesdeveloper

                                         That's really good information. Would you be able to describe how the rolling would work for a more sophisticated calc?

                                          

                                         Thanks for your help.

                                    1 2 Previous Next