11 Replies Latest reply on Dec 30, 2012 5:27 AM by LyndsayHowarth

    Identify Serial numbers to the Customer in Invoices

    MarkNZ

      Hi all,

      After creating a one to manyrelationship - The one being Products table & the many being Units table with serial numbers.

      As each type of product have multiple serial numbers, how can i identify each serial number to A) the product in the products Table & B) the Customer in the Invoice Table?

      As it stands I have the product discription & pricing etc in the PRODUCTS Table..My Invoice Table has a portal to a Line Items table to retrieve the product discription and price..Once the invoice is committed the stock quantity in the PRODUCTS table reduces accordanally.

      Maybe my PRODUCTS table in set up incorrectly, being that its in here that keeps count of the stock quantities...

      How am I best to deal with the Serial numbers# to have them allicated with the product type being Invoiced?

      Can this be dealt with a Value List.. or Lookups..

      I am at a lose end as what to do here..so I would be greatful for your help ..

        • 1. Re: Identify Serial numbers to the Customer in Invoices
          wimdecorte

          Your products table has a "product ID" and so does your Units table.  The relationship is built on that product id to product id, correct?

           

          This way when you're in the products table you can either build a portal to the units that share the same product id and show their serial #s.

           

          For customers: do you want to be able to see what serial #s they have bought or do you want to be able to pick from your inventory when you create an invoice?

          • 2. Re: Identify Serial numbers to the Customer in Invoices
            LyndsayHowarth

            There are many ways to do this kind of thing... and to some extent I'm just restating some of what Wim already said.

             

            Just suppose you are selling iPads... each with it's own serial#.

            You could record each iPad when you take delivery as a separate product and add a field for the serial# in the Products table... in which case stock would be irrelevant.

            You could also add a serial# field to the lineitems table and just record it when the product is sold... in which case the Products table would have one record for iPads and the stock would be incremented down each time one is sold.

             

            - Lyndsay

            • 3. Re: Identify Serial numbers to the Customer in Invoices
              MarkNZ

              Hi Lyndsay, Thanks for your comment.

              I have thought about that idea as well, the issue I would need to overcome is..when Im creating an invoice and adding line items (through a portal) One of the portal fields would be serial

              # which is as you suggest could pull from the Line Items table.

              How would I get around excluding that line item ( Product Record with serial# )  next time I create, look up either a new Line Item or Invoice. As that particular Item is always in the list when adding a new line item to an Invoice..as it stands

              I could easily add the same product with the same serial# again by mistake. How could I remove that Line item from my list...

              I realise im still learning my way around..so thanks for your patience.

              - Mark

              • 4. Re: Identify Serial numbers to the Customer in Invoices
                MarkNZ

                Thanks for your comments Wim..

                Q. Your products table has a "product ID" and so does your Units table.  The relationship is built on that product id to product id, correct?...YES Correct Wim

                 

                Q. For customers: do you want to be able to see what serial #s they have bought YES a history of all serial# numbers relative to the Customer would be good..

                  or do you want to be able to pick from your inventory when you create an invoice? YES also

                Regards Mark

                • 5. Re: Identify Serial numbers to the Customer in Invoices
                  MarkNZ

                  Just a couple of additional thoughts to help clarify my end a little..

                  My PRODUCTS table holds the pricing..so if I add all individual product items with there unique serial# in here I would need to change the pricing on all simular items when theres a price change..

                  My Stock Level Counter is currently in the PRODUCTS Table..and is triggered when an Invoice is committed..However the origanal stock level is manually altered.. and not triggered when a new Product item is created or Duplicated..Can I improve this?

                  Hope this helps  - Mark

                  • 6. Re: Identify Serial numbers to the Customer in Invoices
                    wsvp

                    Hi Mark

                     

                    In my opinion, The best way to handle this would be the addition of a purchasing module.  I would keep the serial numbers at the line level of the invoice and purchase transactions.  This allows for the product table to have a single record and price structure while the purchased and sold items can each have their own serial number.  Relationships can be built between the line items to select and track the items when purchasing or selling.  There are many ways to accomplish this, but I think that an purchasing engine of some type is going to be the cleanest.

                     

                    Inventory tracking can also be a bit more involved in many situations.  The package or units that you sell in will often be different than how they are purchased, thus it is best to establish an inventory count method for products that will track inventory.  Once a purchasing module is in place, your inventory can always be established.

                     

                    Hope this helps.

                    • 7. Re: Identify Serial numbers to the Customer in Invoices
                      LyndsayHowarth

                      Mark,

                       

                      I am assuming you are using the Products or Invoices databases which have been based on the FM starter documents (certainly prev12). The way the stock is being dealt with in these is not ideal... and fraught with potential for errors. There is no record of the stock level going up or down, just a current status.

                       

                      If you think about it... the Items table is the record of the stock going down... and there really should be another table which records the stock going up when received.

                      (the Items table could actually perform both functions as Items could have a status field with "stock received" which changes to "Sold"... but you might get youself in a muddle trying to manage that.)

                       

                      In your scenario, I would have the following tables:

                       

                      Products

                      StockIn (with status Available/Sold/Returned)

                      Invoice ( - needed if your customers are buying more than one item per sale)

                      Invoice_LineItems

                       

                      When you choose a product in the Invoice_LineItems portal, the Serial# field would have a valuelist filtered by product ID of the Available_StockIn Serial#s. A calcuation on the Status field in StockIn would calculate that the serial# now exists in the Items table and should therefore be marked as Sold.

                       

                      The product record could look at the StockIn table and tally the Available to get the current stock level.

                       

                      Don't think of relationships as being only in one state per table. There is an excellent white paper in the Resources section on Relational Theory Fundamental Concepts of Relational Theory (Last Updated: 2003) but also look at the powerpoint presentation on Kevin Frank's site http://www.kevinfrank.com/anchor-buoy.html

                       

                      HTH

                       

                      - Lyndsay

                      • 8. Re: Identify Serial numbers to the Customer in Invoices
                        MarkNZ

                        Hi wspv,

                        Yes your comment is helpful..it now seems obvious that I need an additional table like you suggest Purching Table or one call Stock In as Lyndsay suggests...I feel I also need to change the way the pricing is done..because as it stands I have to change the pricing on every PRODUCTS Record when the Item is the same..but I only want to change the price in one place..would you have any thoughts on this?

                        • 9. Re: Identify Serial numbers to the Customer in Invoices
                          MarkNZ

                          Hi Lyndsay,

                          Thanks for your thoughtout answer..

                           

                          - A calcuation on the Status field in StockIn would calculate that the serial# now exists in the Items table and should therefore be marked as Sold.

                          At what point does Sold apply in the Status in Stock in ..once it has been entred in Invoices I presume?  What sort of Calculation would apply the Sold option in the Status Field from the portal in INVOICES ? If you feel up to it...

                          I have downloaded your suggested reading material & powerpoint..Quite an interesting site..I see he had a method of reducing a value list as its entered, which was Interesting..so thanks for that..

                           

                          Would my PRODUCTS table draw its information from The Stock IN Table ie: Discription Price etc..

                          - Mark

                           

                          PS: Serial# field would have a valuelist filtered by product ID of the Available_StockIn Serial#s.

                          I just created the Value list for Stock In, created a Serial# field in Line items table in Inv portal..applied the Value List..but cant seem to find where I filter the Value List by the Product ID..?

                          • 10. Re: Identify Serial numbers to the Customer in Invoices
                            wsvp

                            Hi Mark

                             

                            Basically the Price is a single point in the Products along with the Product Name and ID.

                             

                            Lets assume for a moment you are a reseller of 13" Macbooks (MB)...

                             

                            In the product section you would have one record with the Product Title, Price (Purchase as well as Sale Price) and ID...  When you purchase the MB from your supplier you would generate a Purchase with a separate line item for each MB which holds (at a minimum) each "MB name" the "product ID" from the Products Table to relate (to the Products) and its "serial number"...  When you sell a MB you will generate an invoice with the same structure.  You can create a relationship and portal to select the MB from a list and insert the Serial Number into the Invoice Line Item.  In the Product context you can always track the sales and purchases for this product relationally, you can also view what you have sold and have in inventory by serial number.  The product inventory should always remain dynamically up-to-date.

                             

                            Regarding a "Purchasing Table"... Technically you do "not need" to create a new table at all... You can use the same table you use for Invoicing and its line items, for Purchasing and its line items, as they will generally share most of the same parameters.  This is a bit more of an advanced development technique, but it tends to give you a lot more power and flexibility in reporting down the line.  It also eliminates an enormous amount of redundancy.

                             

                            Building a "good" solution of this type will require good knowledge of the FileMaker relational structure and a fair bit of planning.  This is not something I could easily express or explain in a forum thread,  but hopefully it can steer you in the right direction.

                             

                            Hope this helps.

                            1 of 1 people found this helpful
                            • 11. Re: Identify Serial numbers to the Customer in Invoices
                              LyndsayHowarth

                              Hi Mark,

                               

                              I really think you are starting to conceptualise the elements correctly... so to respond to what you've asked:

                               

                              Relationships are very important here. If a product has a record in the Stock IN which has not been sold it is Available. Similarly Stock IN serial#s are Available when they have no matching serial# in LineItems. You can create calculations that say if(product_StockIN::status="Available"; "Available";"") to mark the product codes as Available. You can also calculate Status in StockIN using if(StockIN_Lineitem::serial#; "Sold";"Available").

                              Relationships can then be used to filter the Available Serial#s for the valuelist which is used on the lineitems popup so that when you choose a product ID, only the serial#s Available will show. These relationships might be where you create a calculation in the Product Table which just has the word "Available" as it's result and and also the ProductID on the LHS =  with Status and pf_ProductID on the RHS from the StockIn Table. The valuelist for Available_Products would use this relationship to filter the values of the serial#s. The controls for this are in the valuelist dialog when you choose to get the values from fields... under where you choose the fields.

                               

                              Some Relationships will not work with calculations from other tables because the values can't be indexed or won't update. In these cases you might look at using Script Triggers. You could easily create a script that marked a StockIN record as "SOLD" and attach the script-trigger to the Serial# field to trigger when the field is exited. You might also look at using the Evaluate() function to force calculation values to update only when other field values change. These might be used with auto-enter calculations in text fields which will be indexable.

                               

                              HTH

                               

                              - Lyndsay