8 Replies Latest reply on Sep 18, 2013 10:46 AM by FentonJones

    Can I pull data from various tables into new table?

    TimFeist

      Title

      Can I pull data from various tables into new table?

      Post

           I am trying to set up the following:

           - Bank transactions (separate table for each account). Although all these tables contain the same fields (e.g. transaction date, amount, item), I don't want to combine them into a single table, since then it would be difficult to have a column showing the balance after each transaction.

           - I also want a separate table showing expenditure per month (one record per month). For each record, I want to pull all the relevant transactions from the corresponding month. So, for example, for the record for Mar 2013, I want to pull any transactions that took place in Mar from each and every bank account transactions table. 

           So far I have managed to do this by using a separate portal for each bank account, but this isn't really what I want, as I want all the March transactions to be combined (and sortable), regardless of which table they were pulled from. Is there any way to do this? I've seen reference to the "David Graham" method of adding a mid-way table, but I can't find any details of how to do this or if there are any better ways to achieve this.

        • 1. Re: Can I pull data from various tables into new table?
          FentonJones

                

               I'm not really big on the "bank accounts" part of this. But I have do know something about needed to use data in separate tables, in order to do something different in another; though I avoid this whenever necessary. The one thing that is OK is when you're dealing with "history," hence don't have to worry about someone changing it.

                

               I used to do a little with "David Graham," down in San Diego, years ago. He's a very smart guy (more than me then, and even more so know). I was very interested with his file (which was "Contact Type" I think). It is basically about taking 2 (or more) tables, which have a lot in common, and creating a 3rd table, which becomes what it mostly used for most things. His example file's 3 tables are: Company, Person, and Contact (middle). It solves this problem: "Sometimes there is only a 'person,' other times it's a 'company' (which may have multiple people, but has a 'main person')." That is hard to handle with only two tables, if you want to show and use them at the same time, same place. But if you use the 3nd "main" table, you can create a calculation field which has either.

                

               Each of those 3 tables would have a auto-enter serial ID. The one from the "Contact" table would go to the other two tables. Each would also get a "flag" or 0 or 1 (to make it easy to tell them apart). The two fields would therefore have their own ID (to use for their own 'other' relationships). But they would be seen thru Contact via the Contact ID, when wanting to see them as similar.

                

               The trick to all this is something "new" (to me) which David Graham added. Which is that FileMaker can create BOTH IDs at once, via the "Allow creation of related record", to Contact. Example:

                

               1. Create a new "Person"

               2. Enter something into the "First Name" fields (which is a Contact table's field)

               3. Contact will create a new record, with its new ID

               4. It will also put that same new ID into the "Contact ID" field in the "Person" table's file!

                

               I say "!", because it is hard to see how it knows to do that, but it does. [Hopefully I've explained this.]

                

               So, if you can but a lot of the "same" data into such a "middle" table, then it might be useful method. 

                

               P.S. I have some problems with words, etc., these days. This was hard to understand and explain even when I was smarter :-|

          • 2. Re: Can I pull data from various tables into new table?
            philmodjunk

                 I would put all your account data into a single table to begin with. You can still get account balances and even running balances for each account. Sorts and finds can be used to look at data for a single account at a time or to look at multiple accounts with sorting used to group the records by account and your running total summary field set to restart the total with each sorted group.

                 And Sub Summary layout parts can be used to create sub headers and sub footers for each account with an account total balance visible in one of the sub summary layout parts.

            • 3. Re: Can I pull data from various tables into new table?
              TimFeist

                   Fenton Jones - thanks for the reply. I wasn't sure if I fully understood your example, but either I understood more than I realised or else I managed to glean enough ideas from it to solve my problem (at least it appears to work). In case anyone else is struggling with this, I did the following:

                   1) I set up three tables, two for two individual accounts and another "master table" in which I wanted the data from both tables to appears.

                   2) I only set up fields for "Date", "Amount", "Description" etc. on the master table.

                   2) I selected the "Allow creation of records in this table via this relationship" for the master table.

                   3) On the individual account tables I added related fields from the master table.

                   4) This now allows me to enter data on the table for an individual account, but in the background it's actually adding a record in the master table where most of the data is actually being stored.

                   5) In effect, then, all my transaction data is in a master table, as PhilModJunk suggested, but from a user point of view it feels like I have a table for each individual account.

              • 4. Re: Can I pull data from various tables into new table?
                TimFeist

                     PhilModJunk - thanks for the reply. The way I've gone about it is essentially to have a single master table as you suggested, but then have individual tables that pull the relevant data from the master table each time a record is added. I'm hoping to set this up so I can enter data into either table and the linked table will automatically get the record added.

                     However, from what you said, it sounds like that might be more tables than I need and I should just have a single table with all transactions from all accounts mixed together. I know I can sort and filter and so on and quickly switch to see the data from a single account, but there was one thing you said which I wasn't sure how to do. You said I would still be able to get a running balance for each account, but I don't know how I would go about doing that.

                • 5. Re: Can I pull data from various tables into new table?
                  RickWhitelaw

                       Having a separate table for each account can lead to a potentially infinite number of tables, causing you to go "under the hood" each time a new account is needed. I discourage this approach.

                  • 6. Re: Can I pull data from various tables into new table?
                    FentonJones

                          

                         I was telling Tim what I knew about David Graham's method. But I didn't really understand his original post as well as I should have. I agree with Rick that you should NOT create separate tables just because it is not the same "bank accounts"; in fact even if there's some differences. 

                         Tim, if what you want is just to sometimes see entries by month, etc., this can be done. There are various ways to do that, with Summary field(s). You can make it look like "one per month" when it is really the entire months worth of records.

                         Somehow I got it into my head that you had quite different things also, not just the basics. In any case you should never create a new table for data just because it has a few minor differences. People here can help you with this. Just tell us more about it.

                         I am sorry if I sent you on a false direction (though David Graham's method will help you someday, for something).

                         P.S. About me. I was a pretty good developer, 3 years ago. Then I had a stroke, which made it difficult for me to even read, nor able to know what most words are. I've recovered well, as far as that goes, and I seemed to remember FileMaker better than many things. But it is not easy, and I will sometimes not be clear, about what I read, or about what I write. I wouldn't even trust myself for the final conclusion…

                    • 7. Re: Can I pull data from various tables into new table?
                      FentonJones

                      OK, another idea.

                      - Bank transactions (separate table for each account).

                      Separate table for "Accounts"

                       

                      - separate table showing expenditure per month (one record per month).

                      Yes.

                       

                      Add separate table for "Transactions." It would have much of the data, such as date (and time?).

                      Transactions would have the ID from the relevant Account.

                       

                      When you want to see the transactions for an Account, you could see it from a portal in the Accounts table, record form view layout.

                       

                      You could see the "month" view in the Transactions table, via a list view. It would have only the "Sub-summary" part. You'd sort by month. It would show only the month, with another "Sub-summary" part for each "account" (if desired). Both would have Summary fields, to add up the numbers.

                       

                      You could also create a new table for "monthly totals." You'd wait until the next month started, to put the fixed data, for each "account" into the field, the calculated total of numbers into a plain number field. The idea is that this data does not change later.

                       

                      • 8. Re: Can I pull data from various tables into new table?
                        philmodjunk

                             I would use this structure:

                             Accounts-----<Transactions

                             Accounts::__pkAccountID = Transactions::_fkAccountID

                             Data for a given account consists of a single record in account linked to multiple records in Transactions used to record deposits, withdrawals, interest, fees, transfers....

                             A portal to Transactions can be placed on an Accounts layout to view all transactions for that account. A list view layout on Transactions can be used to view the same data for just one or multiple accounts in the same report.

                        For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained