9 Replies Latest reply on Apr 29, 2011 10:13 AM by philmodjunk

    Managing Records

    MatthewMackay

      Title

      Managing Records

      Post

      Morning all,

      Just wondering if anyone can give me some advice on managing records within my solution.

      I am creating a property inventory solution where the user creates a property record (Parent), several room records (Child) and multiple inventory item records (Child). 

      The inventory is created through a number of drop downs and open text fields. The dropdowns are conditional value lists based on numerous dummy records that are created within a script that runs on creating a new inventory.

      The solution needs to be as adaptable as possible so I have several buttons that allow the user to add new items etc. to the property as my dummy records won't cover all aspects of every property, they just give the basics.

      At the moment all inventory item records are stored in an inventory item table.

      I'm trying to figure out the best way of managing these records, ideally I want my dummy record set to be constant and then allow the user to build upon this.

        • 1. Re: Managing Records
          rjlevesque

          Can you be a little more specific about this area:

          --------- QUOTE ---------

          The inventory is created through a number of drop downs and open text fields. The dropdowns are conditional value lists based on numerous dummy records that are created within a script that runs on creating a new inventory

          ----------- END QUOTE ---------

          Maybe a snippet here to show us what you are trying to accomplish. I am thinking of maybe setting up a sort of template system. In other words I am guessing these could possibly fall into like 3 or 4 categories of properties. Which means you could make a sort of "dummy load" template that would insert starting info based on the users first choice.

          There are so many ways to do this...

          • 2. Re: Managing Records
            MatthewMackay

            I haven't taken in to account the category of property yet but am going to look at this in the near future.

            My conditional value lists are as follows;

            Within each room, there are Fixtures, Electrical Fixtures and Contents.

            For each item, the user slects a material or type.

            So the Fixtures, Elcetrical Fixtures and Contents are conditional on the room.

            The material/type is conditional on the Fixture, Electrical Fixture or Content.

            At the moment, I have a rather lengthy script that runs when the user creates the room record.

            The script is along the lines of;

            If room = "Bathroom"

            Go To Layout (Room Inventory)

            New Record

            Set Field (Room Inventroy::Room,"Bathroom")

            Set Field(Room Inventory::Item,"Flooring")

            Set Field(Room Inventory::Material,"Fitted Carpet")

            New Record

            Set Field (Room Inventroy::Room,"Bathroom")

            Set Field(Room Inventory::Item,"Flooring")

            Set Field(Room Inventory::Material,"Laminate")

            .....................................

            This goes on for numerous items and their different materials/types.

            Ideally I can either get rid of the script all together or reduce it greatly as it does slow things down.

            • 3. Re: Managing Records
              philmodjunk

              You might consider a set of template records so that when you select a room type such as "bathroom" your script goes and finds the "bathroom" template records. Then an import records step can pull them all into your table as new records and a Replace field contents step can assign them the appropriate key value to properly relate the new records. I don't know that this will be much, if any, faster, but it gives you greater flexibility as you don't need separate scripting steps for every room and you change the default entries just by adding or editing template records instead having to rewrite your scripts.

              • 4. Re: Managing Records
                MatthewMackay

                I had a play about with this last night, storing the records in one table seems like the way to go. It allows the user to add to the records, building their conditional value lists of inventory items as they go.

                I'm thinking of how to store each property and maintain the growing room inventory record list.

                There will be one source file that the user begins an inventory from, then once they're finished, each property will be saved as a seperate fp7 and stored on the iPad. Then they can store them wherever they want.

                Once the inventory is saved, a script runs that deletes the property record and any room records from the source file but leaves the room inventory records.

                To try and remove any unecessary duplicate room inventory records, I want the script to find any records that contain the same data in the "Room", "Item" and "Material" fields, then remove all these records but leave just 1. Is the possible?

                Cheers

                • 5. Re: Managing Records
                  philmodjunk

                  There are many ways to this. The optimal approach depends on exactly what you need to do and sometimes it even comes down to a personal preference on the part of the developer.

                  Since your "template" records have fields and data that exactly match the data your user needs each time they add a room to the inventory, I'd really take a close look at the possibility of including a set of template records in the exact same table or group of tables, but using a label field so that I can tell the Template Entries from the "real" records. That makes it easier to keep field names, options, types etc exactly the same during development and on going maintenance of the system design. The trade off, is that then you have to build in widgets that automatically exclude the template records when finds are performed to pull up these same records.

                  This is where the design of your system's layouts and their workflow may make that a large enough job that it works about better to have them in separate tables and then take on the responsibility of carefully keeping the structure of the different tables the same.

                  "To try and remove any unecessary duplicate room inventory records, I want the script to find any records that contain the same data in the "Room", "Item" and "Material" fields, then remove all these records but leave just 1. Is the possible?"

                  Yes, though I'm not clear on why this is necessary.

                  Define a calculation field that merges the three fields. Your find can then use a ! as find criteria in this field to find the duplicates so that your script can remove the extra records. After performing that find, the script might be as simple as Omit Record, Delete All Records.

                  • 6. Re: Managing Records
                    MatthewMackay

                    "To try and remove any unecessary duplicate room inventory records, I want the script to find any records that contain the same data in the "Room", "Item" and "Material" fields, then remove all these records but leave just 1. Is the possible?"

                    The reasoning behind this is that I thaught over time, as more and more inventories are completed, the room inventory table would hold thouasands and thousands of records, would this not begin to slow things down?

                    • 7. Re: Managing Records
                      philmodjunk

                      But why do you have such duplicate records in the first place?

                      And some of my tables surpass a million records and still work just fine, but I do take care in how I work with them. In some tables, we export data on a regular basis to an Archive file to keep the table "lean and mean".

                      • 8. Re: Managing Records
                        MatthewMackay

                        My dummy records sit in the Room Inventory table. These are used to start the user with conditional value lists for different items.

                        When the user is creating an inventory, every Room Inventory record has a FK_Room_ID and then all the item details. During the creation of 1 property inventory, flooring could be the same in every room eg. grey fitted carpet, but there's going to be a seperate record created for each room.

                        Within the Room Inventory table, I end up with several records that are identical apart from the FK_Room_ID, which after the inventory has been saved as a seperate file, are rendered useless within the source file.

                        I only need one of each item in the source file if that makes sense?

                        Perhaps I'm coming at this at completely the wrong angle?

                        • 9. Re: Managing Records
                          philmodjunk

                          Well, I wouldn't create the duplicate records in the first place, but avoiding this might take some work. What I perceive here is that you have a list of inventory items and a list of rooms. A room can link to many inventory items and an inventory item can link to many rooms. That's a many to many relationship.

                          I'd use a join table so that I can link one inventory item to many rooms (and vice versa) without needing to create the duplicates.

                          Rooms----<Room_Inventory>---InventoryItems

                          Rooms::RoomID = Room_Inventory::RoomID
                          InventoryItems::ItemID = Room_Inventory::ItemID

                          A portal to Room_Invetory can list Inventory Items when placed on a Rooms layout and can list Rooms when placed on an InventoryItems layout. You can include fields from the third table to add details to the portal. Example: On the Rooms layout, a description field from InventoryItems (or a table related to inventory items) can be added to the portal row so that you can see the description of each item in the portal.

                          If you haven't worked with a join table before, you might examine this demo file that matches Contracts to Companies in this fashion:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html