9 Replies Latest reply on Jun 10, 2009 4:44 PM by philmodjunk

    How do I list all records in another table?

    coni

      Title

      How do I list all records in another table?

      Post

      This should be easy to figure out but I can't seem to... suppose I have a Budget database, and I have a table for Purchases that holds detailed information about all my purchases. In another table I want a list of all these purchases with their corresponding prices, so I can do calculations with the total of all these prices in this table. Every time I create a new record for Purchases, I want it to automatically be entered into the 2nd table with its corresponding table. How do I do the relations for this? thanks!

        • 1. Re: How do I list all records in another table?
          philmodjunk
             You haven't described the structure of your first table. Just going on what you describe in your post, there doesn't seem to be any need for the second table as you can get your "list of all records" from your first table simply by selecting Records | Show All Records.
          • 2. Re: How do I list all records in another table?
            coni
               I want the second table so I can get the totals of all the purchases and do further calculations with them. Not just a simple list view. Does that make more sense?
            • 3. Re: How do I list all records in another table?
              philmodjunk
                

              You should be able do all of that in your first table with calculation and summary fields, thus no need for a second table that contains the same data as your original table.

              • 4. Re: How do I list all records in another table?
                coni
                  

                that kind of makes sense, but I don't want all the same data - just parts of it. if I do it in the same table would I just have one field that keeps updating in every record and every time a new record is added? can you be a bit more specific on how I'd do this - it seems like it'd be a bit crowded since I wanted a detailed invoice for each purchase andthen another thing where it summarizes all the purchases.

                 

                also, what if I want the other table to find all the purchases from say like "June" and get all the totals from those records? how would I do that? sorry I'm new at this relation stuff and I can't seem to get it to work. thank you!

                • 5. Re: How do I list all records in another table?
                  coni
                     To be a bit more specific I am using the Home Budget template on FileMaker Pro 8 and instead of having the list of Other expenses and Other Budget fields be manually entered I would like them to be looked up from other records of detailed invoices.
                  • 6. Re: How do I list all records in another table?
                    philmodjunk
                      

                    You can create any number of layouts to display the data from the same table. Thus you can choose which fields to place on your layout and which to leave off. That should enable you to avoid the "crowded" layout you describe. Thus, instead of copying information to a second table, you may simply have to switch to a different layout that displays a different set of fields (such as summary and calculation fields) to see what you want.

                     

                    You can perform finds to limit the number of records visible. Enter find mode and type 6/2009 into a date field, then click Perform Find and you should see just the records dated June 2009.

                     

                    I don't have a copy of the database you are attempting to adapt so I have no idea what structure it has nor what layouts it uses. Thus, I may not have given you any useful information as your database design and what you want to do may make a different approach the better way to go.

                    • 7. Re: How do I list all records in another table?
                      coni
                        

                      thanks for the help! I managed to do it with the portals - I realized I had been using the matching relation wrong. What I did was first I matched the month, and the portal listed all the invoices from that matching month. I kind of understand what you mean with the layouts and tables - so I just make another layout for the summary report, but with some fields omitted? But if I want a layout of some fields of the first table and some fields from another table then I can use another table to combine?

                       

                      thanks again for the help!

                      • 8. Re: How do I list all records in another table?
                        coni
                           also, now that I have all the total sums listed in a portal, is there some way of combining the portal fields with fields in the actual table - so I can use the information that came through the portal?
                        • 9. Re: How do I list all records in another table?
                          philmodjunk
                            

                          To answer both of your last two posts:

                           

                          You can place fields from two different tables on the same layout. What you get when you do that and whether that's a good idea depends on the tables and relationships that are defined in your database.

                           

                          You could, for example, create a layout based on the same table as your portal and place fields from a related table in headers and sub headers to create a grouped summary report of your data.

                           

                          It all depends on the tables and their relationships. You'll need to be able to open Manage | Database | Relationships and be able to make sense of what you see there.