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.
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.
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.
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.Sherri
- Room List - list of each room with all the assets (including the portal with the typicalDetails) - sorted by departments or room
- Typical list - typical with list of assets
- Asset list (or Bill of Quantity) - list of assets (AssetCode+Uph1+Uph2), total quanity of each variation in the project
- Asset Catalog - list of assets by asset code (AssetID) with various uph combintions identified
- Asset list (AssetCode+Uph1+Uph2) sorted by Manufacturer
- Asset list (AssetCode+Uph1+Uph2) sorted by Asset then by room - lists all rooms each item is included in.
- Budget Summary by department
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.
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?
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.
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.