4 Replies Latest reply on Oct 3, 2011 4:25 AM by fmchris

    large amount of fields in a table is it a good practice?

    fluffyone

      Title

      large amount of fields in a table is it a good practice?

      Post

       Hi group

      I am planning a new system and I want to store lots of different data from the same forms but rather than getting too complicated linking loads of tables I am thinking about one main table with loads of fields in.

      then in the form all the fields will be present but depending on the selected data in the first couple of fields to what is actually displayed on the form.

      I was then thinking about using conditional formatting to hide or show the relevant fields based on the data selected in the first couple of fields.

      I am not sure how big this will be yet but I think it could have a lot of fields to work well.

      Is this a good practise?
      will it run slow due to loads of fields?
      will it still run ok and fast via my Ipad APP?

      I would appreciate your comments.

      Regards Fluffy

        • 1. Re: large amount of fields in a table is it a good practice?
          fmchris

          I'm unfamiliar with the iPad version of FMP but in general -

          If your fields all belong to the same file structure (i.e. all logically 'hanging off' a unique identifier), then that is what you must have. However, if your table contains fields reflecting different structures - for example, "Customers", "Products", "Invoices", "Orders" - it would be much better practice to have multiple tables. In fact, without repeating a lot of data unnecessarily, it wouldn't even be possible.

          • 2. Re: large amount of fields in a table is it a good practice?
            bumper

            One large table with loads of fields is seldom, if ever, a good idea. Doing so almost always results in duplicated and redundant data, that is difficult to gather reports from and a bear to update. However, without additional information as to the use and intentions of your solution. Layouts and tables don't really take up that much space, but data does so if you have duplicated data then you are going the opposite of where you say you want to be.

            With regards to the iPad, it can handle a number of tables and layouts with ease, but again until we know what you want/need to do with the data it is difficult to give advice. I can say that for networked FileMaker Go I create separate layouts within the file and send the user to the layout for that platform.

            • 3. Re: large amount of fields in a table is it a good practice?
              fluffyone

               Hi Chris and Bumper

              thankyou for your replies.
              An area that may use this is for Stock control / stock information, where there will be all sorts of different products and items entered and a wide range of information is required for all the different items. from the entered information calculations will be made in forms with the different data.

              So for instance:

              Item a- a small part, data required= description, weight, unit quantity, pack quantity, cost in

              Item b- a sheet of material, data required= description, weight, unit quantity, pack quantity, cost in, length, width, thickness

              Item c- a liquid part, data required= description, weight, unit quantity, pack quantity, cost in, flamible y/n, litres, shelf life

              Item a- a tube material, data required= description, weight, unit quantity, pack quantity, cost in, length, diameter, wall thickness

              Etc...

              Basically I will want to have a simple screen for entry for all types of items, but I will want the screen to change depending on the item selcted, which will prompt for the correct data. I have not fully thought out the project as yet so I am unsure of how many fields there will be at this stage.

              what do you think?

               

              regards Fluffy

              • 4. Re: large amount of fields in a table is it a good practice?
                fmchris

                Ok, it's looking clearer now. Assuming that each different type of part has its own unique identifier, then you could define a series of tables (small, sheet, liquid, tube, etc). Each table would have a relationship with the main table based on its part number.

                In the main table, you would prompt the user to enter a part number (or part type if the number doesn't make the type clear) - a script would then take the user to the appropriate layout ready for data entry. In this layout there would be a portal to the related table (you must make sure that "Allow creation of records in this table for this relationship" is checked when you create the relationship) with all the relevant fields. The user would enter the data and this would dynamically create a new record in that related table.

                That way you can keep the table sizes smaller, but just have one master table that the user sees for data entry (and modifying and deleting - whatever you decide). The care you especially need to take is with 

                1. The overall design (work it out on paper first)
                2. The relationships
                3. The script(s) to take the user to the correct layout
                Hope that helps.