8 Replies Latest reply on Nov 18, 2011 4:20 PM by dbarnard1

    Strange behavior with SUM() on related field

    dbarnard1

      Title

      Strange behavior with SUM() on related field

      Post

      Hello, 

      I'm having a strange problem performing a sum on related fields, and I'm not sure whether it's a bug or if I'm doing something wrong.

      I work for a company that does specialized installations of A/V and computer gear. We keep a small inventory of miscellaneous connectors & cables, and I'm trying to build a database to track it. I have 3 tables. "Inventory" keeps track of the individual items, using an existing alphanumberic part number as the primary key. "Job Codes" keeps track of a list of codes from the accounting system that the items get charged to. "Stock Transactions" keeps track of individual transfers in and out of the inventory, and relates items to jobs.

      Keeping track of individual stock transfers is important because at the end of the month this information has to be entered into the accounting system. I'm looking at ways to do an import, but for now it's a manual process. In order to keep track of how many items are in the inventory, I have started with an "Initial_Quantity" field that is set when the record is created. There is also a Quantity_In_Stock field that tracks the amount of each item on hand via a calculation, which I'll get to in a moment.

      Each transaction record has a "Qty_Start" field that stores the Quantity_In_Stock at the time the record is created (via a lookup). It also has a Qty_Transferred that is entered by the user (either + or -). This results in a Qty_Remaining field that serves as a check for the user, but is otherwise not used. 

      The calculation for Quantity_In_Stock is as follows:

      Initial_Quantity + Sum ( Stock Transactions::Qty_Transferred )

      My intent is that for any selected Inventory record, the Quantity_In_Stock will be the start quantity plus the sum of all transfers from records that are related by that Item Number. And it works, to a point. If I select a Item Record in the Inventory table, the Quantity_In_Stock field calculates correctly. 

      Unfortunately, when I create a new Stock Transaction record and remove a certain number of items from stock, the Inventory record that was last selected before switching to the Stock Transfer view will have that same quantity removed as well - even if it's a different item number. 

      So for example, if I have Item ZAUDCAB001 open in a form view of the Inventory table, and I switch to a Stock Transfer view, and then subtract 50 ZMBOOT002 items, then go back to the Inventory view, both ZAUDCAB001 and ZMBOOT002 will show a Quantity_in_Stock that is 50 less than what I had before.

      Can anyone tell me if this is a bug, or whether I've done something horribly wrong in my design?

      Screen_Shot_2011-11-17_at_9.35.00_PM.png

        • 1. Re: Strange behavior with SUM() on related field
          mfs.nishant

          Hi   David,

                  I performed similar operation for test your problem, But it is working fine as saw at my end, I am sure that you have maked "Quantity_In_Stock" as Unstored, not global, and "Stock Transactions::Qty_Transferred"  number return type.

            I come across above conclusion By your below Statement. This behaviour only happening when I make Quantity_In_Stock Global.

              "So for example, if I have Item ZAUDCAB001 open in a form view of the Inventory table, and I switch to a Stock Transfer view, and then subtract 50 ZMBOOT002 items, then go back to the Inventory view, both ZAUDCAB001 and ZMBOOT002 will show a Quantity_in_Stock that is 50 less than what I had before."

          Thanks

          Nishant Kumar

           

           

           

           

           

          • 2. Re: Strange behavior with SUM() on related field
            dbarnard1

            Nishant, 

            Thanks very much for your response. I can confirm that it's Unstored, not Global, and has Number as the return type (see additional screenshot below). I don't think that the issue would be related to storing globally in any case, as it only affects the Inventory record that was last active when the Transaction record is created. if it were a global field I would expect it to be affected across ALL records. 

            I appreciate your help, and confirming that it's not something obvious I should be doing that I'm not. I think my next step will be to try building a simplified version from scratch, only creating the minimum necessary fields and relationships to produce the desired result. Then if that works, I'll start adding back functionality until something breaks. I'll be sure to report on the outcome. 

            Thanks again,

            David

            • 3. Re: Strange behavior with SUM() on related field
              philmodjunk

              I don't see any issues with your relationships and field definitions. (Like Nishant Kumar, I thought global storage was specified as a global calcualtion refers back to the last field modified and this sounded like what you have here.

              That being the case, it may be a good idea to take a closer look at design of the layout where you log these transactions.

              • 4. Re: Strange behavior with SUM() on related field
                DavidJondreau

                Transaction::Item Code is a Text field, not a Number field?

                • 5. Re: Strange behavior with SUM() on related field
                  dbarnard1

                  David J - that's correct. The database uses existing Catalog item numbers as its primary key; these are actually alphanumeric codes. the people who check items out will know them by that code, so that's what they'll use to log what they're taking. My understanding is that shouldn't present a problem, but I'd welcome any information to the contrary. 

                  • 6. Re: Strange behavior with SUM() on related field
                    philmodjunk

                    Using externally gnerated primary keys, whether text or number, can be a problem. It leaves your system vulnerable to issues should the externally supplied ID numbers be changed by whatever source generated them and also an error on the part of whatever system produced the ID that isn't promptly identified and corrected can make correcting the error without loosing the "connections" between related records a bit of a pain to fix. You may want to consider using an internally generated serial number instead.

                    You can still keep this field in the parent table--just don't use it for linking to other tables in your database. That way you can display this value, use it in value lists, sort on it, search on it, but issues that require changing this value after the fact become simple data entry tasks.

                    • 7. Re: Strange behavior with SUM() on related field
                      DavidJondreau

                      I was thinking that if Transactions::Item Code was inadvertently defined as a Number field instead of a Text field, then ZAUDCAB003 and ZMBOOT003 would make a match, causing the problem you have.

                      • 8. Re: Strange behavior with SUM() on related field
                        dbarnard1

                        David J - you nailed it. That's exactly what the problem was, and in hindsight it's obvious. Thank you!

                         

                        PhilModJunk - I see your point about using an external primary key. I'll see if I can figure out how to make it work with an internal one. Thank you as well!