6 Replies Latest reply on May 9, 2012 2:02 PM by philmodjunk

    Calculate quantity total for items with kit of parts



      Calculate quantity total for items with kit of parts



      I am having trouble creating the right calculations to get the totals I am looking for.  My database is set up to specify furniture for commercial interiors projects. The simplified structure is below:

      rooms --- roomDetails --- catalog (furniture items) --- typicalDetails (furniture items in a typical) --- catalog2 (catalog TO)

      For ease of data entry on large projects we create typicals (kit of parts) of furniture items then enter the typical into a room on the roomDetail layout.  We may have other furniture items in that room also.  

      Example.  A typical office may have a desk, task chair, 2 guest chairs and a file.  100 offices may be the same but one needs an extra table.  In that case the room would contain the typical and the extra table.

      I need to caculate the total number of items (task chairs) for all rooms  or the total # of task chairs in the project - (# of typicals * quanity of chairs in the typical) + quantity of chairs in rooms that are not part of a typical.  The second part of this equation is simple using a summary field but I can't get the first part to calculate correctly and/or add to the second part.  It might just be the context I'm not getting right.

      1. In which table should these calcuations occur?  RoomDetail or Catalog?

      2. Should I create 3 fields...1) total assets in rooms 2) total typicals in rooms * qty per typical 3) sum of 1 & 2

      3. Should I use Summary fields and the Get(Summary) function?  

      4. What should my calculations be?



        • 1. Re: Calculate quantity total for items with kit of parts

          I somewhat recall a series of posts that dealt with setting up a database like this--probably your posts...

          How is the information listed for "typicals" in the RoomDetails table?

          If 1 "typical" is selected, do you have one record entered with a Quantity 1 and the ID of the record in Catalog?

          I think that's what you have here.

          If, instead, selecting one "typical" item from the catalog resulted in a script using the records in Typical details to make entries in RoomDetails for each item listed for that catalog item, producing this report would be a simple matter of setting up a summary report based on RoomDetails.

          There are Pro's and Cons to setting it up that way. An alternative, is to define an additional related table linked via a serial number defined in RoomDetails where you see one record if a "non-typical" catalog item is selected, but multiple records and quantities are logged to list each item in a "typical" kit. This keeps the RoomDetail table unchanged, but now your summary report can be based on this new table. The challenge to this approach is putting the necessary script triggers in place so that the correct records are created/updated/deleted as you edit records in the RoomDetail table.

          • 2. Re: Calculate quantity total for items with kit of parts

            Yes, I did post "What is similar to a union query in MSAccess?" a while back regarding this same database.  You helped me write a script to copy the typical items into the room detail table.  However, we did not write into the script the modifcation or deletion of those items when the typical changes - which will happen often.  I have disabled the script.  I figured out how to put a portal into my report based on rooms that lists the assets (or typical) then the portal lists the typical assets.  This works great since it's really easy to modify the typical in the room and also the assets in the typical.

            So, currently the RoomDetail table is set up so that the typical is listed as an asset.  The "typical" has the same ID field (AssetID) as the assets from the AssetCatalog.  There is also a quantity - there may be 10 of that typical (qty of each item in the typical * 10) in a room.  I added a field to the AssetCatalog to identify if an item was new, existing or typical so I can filter portals and identify which items are typicals.

            Rooms----<RoomDetail>---AssetCatalog-----<TypicalAssetsList>----AssetCatalog 2

            One other detail... the Upholstries for the seating are identified in the RoomDetail to minimize the # of Assets in the AssetCatalog.  The upholstery for the typicals is identified in the TypicalsAssetList.  I use a concantenated field of AssetCode + Uph1 + Uph2 to give each option a unique identifier for reports.

            I think I prefer the RoomDetail table to only have the AssetID and not script in to copy all the line items for the TypicalAssets.  However I need to report this information in a lot of different ways so the big question is what is the best way to organize this database.  I am developing the following reports:

            the green items I have figured out, the red item I have not figured out and the others I haven't gotten to so I'm not sure how the current issue will affect them.

            1. Room List - list of each room with all the assets (including the portal with the typicalDetails) - sorted by departments or room
            2. Typical list - typical with list of assets 
            3. Asset list (or Bill of Quantity) - list of assets (AssetCode+Uph1+Uph2), total quanity of each variation in the project 
            4. Asset Catalog - list of assets by asset code (AssetID) with various uph combintions identified
            5. Asset list (AssetCode+Uph1+Uph2) sorted by Manufacturer
            6. Asset list (AssetCode+Uph1+Uph2) sorted by Asset then by room - lists all rooms each item is included in.
            7. Budget Summary by department
            I have toyed with the idea of creating the additonal table with all the records from the typicalDetails, but I don't know how to write that script so that when I make modifications or deletions I am confident the data is accurate.  This was actually much easier in Access where I could use the union query to create this same list of information.
            Any help would be appreciated.  Thanks.
            • 3. Re: Calculate quantity total for items with kit of parts

              I've created a new table to hold "all" of the assets.  Since I don't know how to write the script to modify or delete the data as I'm working I created 2 scripts that I can run when I need to (will manually delete all existing records before running script).  The first script copies the data from my RoomDetail table and puts it into the AllAssets table.  Then I created (modified the script from the previous series of posts regarding "union query") a script to loop through the All assets table and create new records from the TypicalAssets table.  I put in a Not IsEmpty statement for the script to evaluate and run the loop on "typicals" only, however this is not working.  Any thoughts on how to get the data I need into 1 table?  I'm attaching the second script as an image.  Any help would be greatly appreciated.


              • 4. Re: Calculate quantity total for items with kit of parts

                Or you aware that error code 101 is not really an error here but an expected outcome of the go to record, next, exit after last?

                • 5. Re: Calculate quantity total for items with kit of parts

                  The script is not working as desired.  

                  If I enable the Not IsEmpty (room_ALL:typicalCode) the script does nothing. 

                  If I disable the Not IsEmpty statement the script does loop through a few steps but the data is not evaluated by the asset type = typical so I get undesired results since it is evaluating the new assets that do not have the "kit of parts" or typicalAssets and creating blank records.  It also does not loop through all of the records.  I am not getting anything even close to a full list of all the assets.  I'm not sure where the breakdown is...I've tried many combinations of the script steps.

                  • 6. Re: Calculate quantity total for items with kit of parts

                    Better check the value of the typicalCode field then.

                    At the time this step executes you are on a layout based on room_All so the only reason the code inside the If step will be skipped is if that field is empty for the current record.

                    Since this test is performed before the loop starts and immediately after a go to record [first] step, I would conclude that the first record in your found set has this field empty and thus nothing takes place when the script is executed.