14 Replies Latest reply on Jan 27, 2010 11:18 PM by vmims

    Some records do not appear on form view or on table view

    vmims

      Title

      Some records do not appear on form view or on table view

      Post

      Hi, I have looked for this answer everywhere. On FP 10.3, I imported data from Excel. I have 451 fields, and approximately 75 records (participants in a psychological study).

       

      I can see all the records, but only the first 422 fields show up. In the form view, field 422 is at the very bottom of the body area. I can't pull down that line to make more room for the last 30 records to appear.

       

      In the table view, I can only see the first 422 columns no matter how narrow I make them. I am new to FP, so if you can help me, I would appreciate it.

        • 1. Re: Some records do not appear on form view or on table view
          RickWhitelaw
            

          It's difficult to know what to say. 451 fields and 75 Records? There is a limit to layout size and table view has limits as well. The limit you've likely reached is "how narrow can a column be?". Are all these fields in the same table? Even if you had 75 fields and 451 records I'd guess 75 fields would be "a bit wide" as a legacy designer friend of mine used to say. "Skinny tables are good" was his modus operandi. If you started this database now's the time to redesign. You don't have too much data to manage. You could almost write down 75 records by hand but don't. Back up what you have and then read about what a Relational DataBase is. It's available in the FM documentation. You'll probably break up your fields into several different tables and the whole thing will be more efficient (for 10 or 10,000 records). 

           

          Continue to ask questions here and to browse. It can be invaluable.

           

          RW 

          • 2. Re: Some records do not appear on form view or on table view
            vmims
              

            Hi Rick,

            Thanks for taking the time to answer. Here is the problem: I have 75 subjects in my study, which is now closed to enrollment. I have collected 451 data points on each subject (hence the 451 fields). That's over 33,000 data points -- 0bviously too much data to write by hand. Then I need to find various associations and patterns, etc., in the data.

             

            All my data is already entered in an Excel spreadsheet with 451 columns, which I imported into FP. That's how I ended up with 451 fields and 75 records.

             

            When I look at the form view, it is quite easy to read -- right up to the point where it just stops!

             

            Any ideas for this? I am a neophyte, but have spent the last three days trying to figure this out. Thanks in advance.

             

             

            • 3. Re: Some records do not appear on form view or on table view
              philmodjunk
                

              There's a tool in excel that swaps columns for rows that may help. I think it's called "Transform", but may not be remembering correctly.

               

              I'd try importing the data more than once. Once to import name and other data specific to one individual, then import the data points into a separate table related to your patients/subject? table so that one record in the patient table matches multiple data records in the second table.

              • 4. Re: Some records do not appear on form view or on table view
                vmims
                  

                Hi,

                Thanks for the suggestions. I "transformed" the data and imported it, but that didn't work for me since each record now contains only one of the 451 data points for all of the 75 subjects, instead of all the data points for a single subject. I need each subject's data to be visible on a single record so that team members can see the whole picture for any given patient by browsing to a single record.

                 

                I think your suggestion about multiple tables sounds like a good solution but, being new at this, I am not familiar with how to make multiple tables. I am guessing that I would have to create relationships between them by having an ID field in each one? I think I am out of my depth here.

                • 5. Re: Some records do not appear on form view or on table view
                  philmodjunk
                    

                  The idea was to use transform to break up your data into individual records that could be viewed as a group on your screen in either list or table view. They could also be used to populate a second table and you'd use a portal to view the data point records for a selected individual's main record. You can learn more about that method by looking up "Portal" in the filemaker help system.

                   

                  Let's keep it simple for now. After you transform your columns for rows, is their one column of data that can be used to identify which data points belong to which individual? If not, you can insert a new column and add a name or number to identify the individual for whom the measurements where recorded. Now import your data. To view the data for a given individual, perform a find to find all the records for a given individual by searching the field that recieved the data from this newly added column in your spreadsheet. Select view as table from the view menu and you should see all the data for one individual presented in a view very similar to your original spreadsheet type view where each row shows a different data point for the same individual.

                  • 6. Re: Some records do not appear on form view or on table view
                    vmims
                       Ok, I am getting closer. I have added a patient number field, with the numbers 1 - 75 as unique identifiers. Now, when I search for patient number 2, for example, it returns records for all the patients with 2 in their number field (e.g. 12, 22, 42, etc.). I tried typing in "2" (within quotes), but that did not change the result. What am I doing wrong? This seems like an elementary question, but I can't find the answer in the help section. It seems as if it should be under "Making a Find Request," but if it is, I don't see it. I also tried using the number sign before and/or after the number -- same result. Help?
                    • 7. Re: Some records do not appear on form view or on table view
                      philmodjunk
                        

                      I suspect your patient number field is a text field and entering a "2" is finding 2, 21,20,200 but not 42,52 ... In text based searches, entering text without any operator is telling filemaker "find all records where a word in the field starts with these characters."

                       

                      I'd change your field to type number. (Find the field definition in Manage | Database | Filelds; select it; use the drop down on the right to change the type from text to number and click "Change".)

                       

                      If you want to find just the number "2" in a text field, you can enter ==2

                      • 8. Re: Some records do not appear on form view or on table view
                        vmims
                           Yes, I read in "help" that ID fields should be formatted as text. But I changed the format to number and it solved the problem. Thanks for your help!
                        • 9. Re: Some records do not appear on form view or on table view
                          philmodjunk
                            

                          Do you know where you found that "help" item?

                           

                          It doesn't appear to be sound advice and I'd like to check the context. I always make serial number fields type number.

                          • 10. Re: Some records do not appear on form view or on table view
                            vmims
                               I will try to find it again, but I searched so many different ways that I am not positive I can retrace my steps. I do remember that it said the reason for defining it as text was that you would never use an ID field in a calculation and it should never be changed, so it should be text instead of number. That made sense to me at the time.
                            • 11. Re: Some records do not appear on form view or on table view
                              philmodjunk
                                 That's truly odd, since making a field a text field does not prevent changing the value nor does it prevent using it in a calculation. And I do from time to time create calculations that refer to a and ID field and can't imagine why that would be a problem...
                              • 12. Re: Some records do not appear on form view or on table view
                                RickWhitelaw
                                   Auto-entered serial values, often used as PK (parent table), should be number. It's safe for a FK, which is the PK being "passed along" as a match field by the creation of records in a related (child) table, to be stored as text. I don't see any reason to store as text though. At least that's my understanding.
                                • 13. Re: Some records do not appear on form view or on table view
                                  LaRetta_1
                                    

                                  vmims wrote:

                                  I can see all the records, but only the first 422 fields show up. In the form view, field 422 is at the very bottom of the body area. I can't pull down that line to make more room for the last 30 records to appear.

                                   

                                  In the table view, I can only see the first 422 columns no matter how narrow I make them.


                                  Layouts have limits of 8000 px. whether width or overall height.  So in form view, if you add header, body, footer together, they cannot be taller than 8000 px. combined.  And in table view, you cannot have width more than 8000 px.

                                   

                                  But as indicated, 422 fields suggests it might be better to split the data.  Do you have any fields which are alike?  A giveaway are fields such as Subscription1, Subscription2... or Jan, Feb, Mar etc?  Your Users cannot view that many fields at one time anyway; it is best to group the data; tab panels and portals make it easy. :smileyhappy:


                                  • 14. Re: Some records do not appear on form view or on table view
                                    vmims
                                       OK, that makes perfect sense. But I can't believe that I couldn't find that piece of information in "help." Thanks for clearing this up for me. This was very helpful!