4 Replies Latest reply on May 26, 2014 1:17 PM by DanielShanahan

    Inventory relationship


      I have two issues.

      The first one is that in my "Invoice Detail layout" I had a value list with the colors and the sizes related to each product, now is not working any more. I dont know if I'm doing something wrong because it tells me "List without values", and also I have the field of "description", were it suppose to appeare when I select the product Id.


      The secund issue is of the inventory by store.

      1.- I have my inventoriy of clothes with a value list of multi colors and multi sizes, related to each product. Now I need to add the location (store) there the inventory is located, so I can see were I have the product, so If I need that product in the store 1, I can see in whitch store i can take it from.

      The question is how can i do to make a formula, so when I pass a product from the store 1 to the store 3, It sums in the store 3 and to substract it from the store 1.


      2.- How can I relate a field so I can only make an invoice of what i have in stock? If I dont have in stock I dont want that it lets me choose the product.

        • 1. Re: Inventory relationship

          Issue one

          Sounds like something changed in the value list.  If you can post a .zip file of your solution, that would help.


          Issue two


          You could do several things.  Have your company listed as a customer and do an internal sales order.  Then you simply fill out a SALESORDER and SALESORDERLINE.  If you don't want to list your company as a customer, you could create two tables such as TRANSFER and TRANSFERLINE that would work the same way as SALESORDER and SALESORDERLINE (or INVOICE and INVOICELINE for that matter).


          Some potential fields:



          TRANSFER::preparedBy (employee who filled out the transfer)





          The TRANSFERLINE would have pretty much the same fields as an INVOICELINE:






          TRANSFERLINE::lineTotal (qty * cost)



          It can get more complex than this.  You could also have a RECEPTION and RECEPTIONLINE table for receiving goods (you may already have this for receiving POs).  You may also want to track DISTRIBUTION, which can be helpful if you want to fill back orders before putting items on the shelf.


          Issue two


          Does your INVENTORY table have a quantity field or fields (e.g. onHand, available, allocated, onBackOrder, onOrder)?  If the available quantity is 0 or NULL then use script triggers (e.g. onObjectSave) to prevent that item from becoming an INVOICELINE or SALESORDERLINE.

          • 2. Re: Inventory relationship


            Thank you for answer.

            I woud like to add all the fields you say on issu two, but i dont know how.

            I attache the zip file so you can se the relations, but I dont know how to add it here so I'll send you by private mail.

            Thank you again


            • 3. Re: Inventory relationship

              Here I attache the file .

              Thank you again



              2014-05-19 21:08 GMT-05:00 DanielShanahan <noreply@filemaker.com>:


                   <https://fmdev.filemaker.com/index.jspa>   creato da DanielShanahan<https://fmdev.filemaker.com/people/DanielShanahan>in *Advanced

              Discussion* - Visualizza la discussione completa<https://fmdev.filemaker.com/message/148253#148253>


              • 4. Re: Inventory relationship

                Thanks for the file.  I think you could probably get by with something simple such as the Stock Transfer table on the Inventory starter solution.  With few modifications, you could keep a simple transfer log for each item.  Attached is a screenshot and the fmp12 file.