6 Replies Latest reply on Aug 4, 2015 3:25 PM by philmodjunk

    Need help about finding error for my simple database

    leonhart1981_1

      Title

      Need help about finding error for my simple database

      Post

      I made a simple database with following tables: customers, invoices, invoicelineitem and products. 

      The image below is the relationship diagram that I did. 

      I made this database because I want to test myself how much I am familiar with the relationship and the portal. 

      What I wan to accomplish is when I create an invoice, and type the item name in the portal, it should look up its price from the product table by itself and when I type the QTY in the portal, the stock availability in Product table should be reduced.

      However, I think I must have done something wrong because when I did type the item name in the portal field, nothing happened. Here is the link for the video that I recording for reference.

      If anyone know what I did wrong, please help me out~~~  

      Relationship_4.png

        • 1. Re: Need help about finding error for my simple database
          philmodjunk

          Did you set up an auto-enter field option on the _fkProductID field in LineItems to look up the product ID from the look up table occurrence shown below the LIne Items table occurrence?

          • 2. Re: Need help about finding error for my simple database
            leonhart1981_1

            Did you mean the image shown below? If so, I did. 

            • 3. Re: Need help about finding error for my simple database
              philmodjunk

              If you are going to use the Item name to look up the product ID as suggested by the relationship linking Invoice Line Items to Products | Looked up, the above dialog would refer to a field from Products | Looked Up, not Products.

              Once you are getting the ID correctly looked up, we can see if you have the correct auto enter settings to look up the unit price and then we get to see if there might be a "timing issue" as each line item record has to look up the product ID before it can use product ID to look up a unit price.

              • 4. Re: Need help about finding error for my simple database
                leonhart1981_1

                After changing Product to Product | Looked Up, it did look up the unit price from the Product table. Thank you very much~~

                I also have a question regarding to the unit price in the Invoice Line Item. Currently, the unit price is lookup from the unit price in Products (see image below);  but if I change the related table from Products to Product | Looked Up, it will still look up the unit price. So I'm wondering if there is any downside when looked up the unit price from Product | Looked Up instead of Products? The "invoice" template from Starter Solution is using Products to look up the unit price instead of Product | Looked Up

                 

                Moreover, what should I do to make the QTY in Invoice Line Item to affect the Stock Availability in Products? So when I type a number in the QTY in Invoice Line Item, the Stock Availability in Products will reduce; and when I delete the related records from the portal in the Invoice, the Stock Availability will be added back. 

                • 5. Re: Need help about finding error for my simple database
                  leonhart1981_1

                  I spend some time looking into the "Invoice" template from Starter Solution and made some adjustments to see if I could come out a solution for my stock availability to be varied based on the QTY from both orders & invoices that I issued. 

                  However, I failed to do it. Below image is the new tables and their relationship that I created for your reference. 

                  And for the calculation formula for the stock availability from the Products table, here is what I typed: 

                  If ( Sum (OrderLineItem::QTY) - Sum (InvoiceLineItem::QTY) = 0 ; 0 ; 

                  Sum (OrderLineItem::QTY) - Sum (InvoiceLineItem::QTY) )

                   

                  Can anyone please help me to find the error? 

                  • 6. Re: Need help about finding error for my simple database
                    philmodjunk

                    Let's tackle one thing at a time. I don't know if you've seen it, but I have a demo file of what I call "enhanced value selection" techniques. It includes a working example of using an auto-complete enabled value list of names to link records by ID.  I'll include a download link for it at the bottom of this comment.

                    The main problems to look out for when using a product's name rather than an ID in a relationship is that a product's name may not be unique and might change at some point in the future. The best design approach is to use such "name" based matching cautiously. The trick is to base all of your relationships on the ID except for those specifically added to facilitate a more user friendly interface. You can use scripts to handle situations where the entered name does not match to any product or matches to more than one product.But always make sure to keep your records correctly linked by ID.

                    So using the name to look up a price is probably OK, but not, say to manage inventory. Please note that each version of FileMaker has it's own and different set of starter solutions. All of them are horribly lacking in documentation to tell you how they were put together, how to use them or how they work.

                    Here's a thread on how I'd manage inventory: Managing Inventory using a Transactions Ledger

                    And the demo file: "Adventures in FileMaking #2 - Enhanced Value Selection"