7 Replies Latest reply on May 12, 2013 5:13 AM by Jade

    Question about tables and repeating fields



      Question about tables and repeating fields


           I'm trying to set up a layout that I think is probably fairly simple, but I've hit a wall.

           This layout is to help me organize parts bins in an inventory system and there are only two fields. I have a room full of parts bins that have a two letter code (AA through ZZ), and a description (such as Transformers, for example). I want to create a layout that resembles a spreadsheet, and contains each of the possible two letter codes - there are 676 of them - along with the label text.

           I could easily do this as a simple vertical list with 676 vertical line items, but since the codes and descriptions are pretty short, the layout would have a lot of wasted space. So I want to divide my 676 groups of 2 fields into say 6 horizontal columns of 2 fields each, to reduce blank space and scrolling. The result would look something like this:


           AA Transformers   AB Resistors  AC Capacitors  AD Diodes  AE Lamps  AF Sockets

           AG Terminals        AH  Tubes      AI  Insulators    AJ Wires      AK Screws  AL Solder

           And so on...


           When I try to do this with repeating fields I get all the 2 letter codes grouped together, and all the descriptions grouped together, which is useless. I've tried a few other things, but have not gotten the result I need.

           There must be a way to do this, but it escapes me. Table format seems ideal since it is spreadsheet-like and I can adjust column sizes, etc....if I can only get it to work. Any help would be greatly appreciated. Thanks in advance!

        • 1. Re: Question about tables and repeating fields

               Repeating fields are advised against by most FMP develpers, with a few specific exceptions.

               If you do not want to avoid repeating fields, a calculation field that makes "AA" and "Description" fields into a "AA - Descriptions" calc field might serve your purposes.

               If you Google "repeating fields site:forums.filemaker.com" the links will present arguments for avoiding repeating fields.

               These links are found to be useful by FMP beginners....


          • 2. Re: Question about tables and repeating fields

                 Data from related records can be arranged in to columns as well as rows with portals and a bit of creativity.

            • 3. Re: Question about tables and repeating fields

                   David, thanks for your post. I'll read the link you gave and will take a shot at the calc idea that you mentioned. And Phil, I will into portals, though that is a new term to me.

                   Maybe I should add, while not an expert, I'm past the beginner stage. I've been using FMP for at least 15 years and have developed some pretty complex files.

                   I get that repeating fields might be a bad idea in this case and that is fine. But before letting go of this topic, any other ideas about how I might accomplish what I want? I know that one option would be to have 6 pairs of fields (code 1, description 1, code 2, description 2, etc.) but that seems a little clumsy and inflexible, I can't help but think there's a better way.

                   (Another thought, would one of you experts be willing to set this layout up for me for a fee? I could email the file to you with just a few records, then you could email it back. With the basic structure set up I could fine tune sizes and such by myself.)

                   Thanks again for your replies.

              • 4. Re: Question about tables and repeating fields

                     Hi David,

                     You may find it easier to work down columns (vertically) in Form view instead of across rows (horizontally) in Table view.

                     For example, try a matrix of 26 rows down by 26 x 2 columns across (odd columns for codes and even columns for descriptions):

                AA Transformers    BA …   CA …   … …   ZA …

                AB Resistors           BB …    CB …   … …   ZB …

                AC Capacitors        BC …    CC …   … …   ZC …

                …  …                      … …     … …   … …   … …

                Use one repeating field for Codes and the second repeating field for Descriptions.

                Start each column by setting the Inspector's Data tab 'Show repetitions' to 1-26; 27-52; … etc.




                • 5. Re: Question about tables and repeating fields

                       Jade, your advice did the trick. I now have the layout I want. Thanks so much!

                       I ran into a little hangup with setting the tabs, but finally got it right. I may beg one or two follow up questions here, but for now I'm off and running. Thanks again.

                  • 6. Re: Question about tables and repeating fields

                         Uh oh. I may have made a tragic mistake here, and maybe shot myself in the foot by using repeating fields. In my initial post, to keep the question simple, I did not mention that what I was creating was actually a second table, residing in a much larger more complicated file.

                         So my problem now is that even though my table looks very nice, I seem unable to use my new entries in my primary table. When I try to, I see the error indication <unrelated.

                         I know that I can set up my table as a second file, or maybe keep this as one file with the two tables linked by a portal, but will my use of repeating fields in setting this up kill either or both of those possibilities? Of course, I've already gone through the time consuming task of entering all 676 of my codes. David, maybe I should have taken your warning about repeating fields more seriously.


                         Any thoughts? Will I need to start all over again? Sorry to be such a bother.

                    • 7. Re: Question about tables and repeating fields


                           Like everyone else, I recommend that you use a related table rather than repeating fields.  There probably will be other issues that cause you problems down the road using repeating fields.  As PhilModJunk suggested, you can setup 26 portals displaying code and description fields across the Form—each portal with 26 rows and a different 'Intial Row': 1; 27; … etc.  This should give the same general appearance as the repeating fields.

                           If you must persist, then try defining the 2 repeating fields as global fields (Manage>Database…>Fields>Storage) in the other table.  This should resolve your immediate problem of <unrelated …>.