1 2 Previous Next 18 Replies Latest reply on Apr 12, 2015 1:06 AM by erolst

    Pulling data from several sources into one dataset

    sphilipr

      I have three separate lists of data for example; one bank account and two credit cards, that I want to combine into one list without merging the three original sources which must stay separate and not be modified. I want the final data to be available in a list of the same set of 4 fields (Date, description, amount and type).


      It seems as though it must be elementary but a lookup would only pull form any one of the three and a calculation seems the obvious choice but I have now idea which function would perform the merge. Any assistance with this basic problem would be very much appreciated.

       

      Thanks

        • 1. Re: Pulling data from several sources into one dataset
          planteg

          Hi sphilipr,

           

          two ways come to my mind:

           

          1. The first method is using ExecuteSQL() to create virtual lists: see http://www.seedcode.com/virtual-list/ and POExLondon2011 - The Virtual List The Advanced Training material also talks about them. The way you would use it would be to create a SELECT QUERY with UNION
          2. Having a script that reads all of the three tables into a new one that will be used for your layout.

           

          Are you coming from the world of Access - you talk about dataset ?

           

          Gilles Plante

          • 2. Re: Pulling data from several sources into one dataset
            erolst

            What is this list supposed to show – transactions grouped by payment method/source? Then I think a proper relational model defined as

             

            PaymentSource --< Transactions

             

            and a report based on a transactions found set would be the optimal way to go.

             

            If you have one transaction table for each payment method, think about consolidating them.

            • 3. Re: Pulling data from several sources into one dataset
              wimdecorte

              It sounds like you need a transactions table where the account is just an attribute, related to an Accounts table.

               

              We need a bit more information about your tables and architecture to answer this one.

              • 4. Re: Pulling data from several sources into one dataset
                sphilipr

                Each separate table is a list of transactions from a separate bank account. The point is that I do not want to consolidate my 3 separate sources  I want to pull them into a new table and consolidate them there to make a single list of all transactions where I can then add further information. I can do this literally by exporting the data as a .csv from each source table and reimporting it into the merged table but that seems crude and is labrorious when I'm working with a relational database.

                • 5. Re: Pulling data from several sources into one dataset
                  wimdecorte

                  While it may seem laborious, it IS the fastest way to do it.

                   

                  You can also loop through each of the 3 tables and collect data and turn around and create new records in the consolidated table.

                   

                  If you are more proficient with SQL you can do the same with the SQL plugin.  The native ExecuteSQL() function does not allow INSERT.

                  • 6. Re: Pulling data from several sources into one dataset
                    sphilipr

                    Ok well I thought I was being dumb but it might be that this is not elementary and is just beyond my skill set.

                     

                    Thank you for the reply

                    • 7. Re: Pulling data from several sources into one dataset
                      wimdecorte

                      Well, you can choose not to maintain 3 different transactions tables in the first place which will save you from trying to consolidate.  You ruled it out but maybe you should not and revisit your initial architecture.

                       

                      (it will also make it a lot easier if you add an account)

                      • 8. Re: Pulling data from several sources into one dataset
                        sphilipr

                        Lets try a different example and see if that get my idea across because it seems odd to me that Filemaker can't do this.

                         

                        Lets say I have 3 RSS feeds that I want to pull into one new page as a simple list of headlines? Showing all the headlines from say: Reuters, AP Newswire and the BBC all in one list.

                         

                        It's an ongoing 3 into one merge from different sources. It doesn't really matter if these are held in the same database or come from external sources such as XML, .csv or another table or another database. It seems so simple that there must be a very straightforward way to do it.

                         

                        Surely?

                        • 9. Re: Pulling data from several sources into one dataset
                          wimdecorte

                          Sure, the principle is simple: you collect each one and then create records in one central table.  In this example you would collect the XML from stream 1, parse it and create records in the central table, then turnaround and collect the XML from stream 2, and so on.

                           

                          In the case of your bank transactions the decision point is when you go and collect or import the transactions from bank A:

                          1- you can dump those records in their own "Bank A" table, or

                          2- create records in the central table and not have a "Bank A" table

                           

                          If you choose option 2 then you have a "dataset with records from different source".  If you choose option 1 and you then want to see those records together with records from other tables then you have to do extra work by merging data from the different tables:

                          - export from the "Bank A" table and import into the central table, or

                          - loop through records in the "Bank A" table and creating matching records in the central table, or

                          - if the data in the central table is only temporary (for viewing) and should not persist you can go with a virtual list approach.

                          • 10. Re: Pulling data from several sources into one dataset
                            sphilipr

                            Thank you for the very clear explanation. I think I will go the script route because a) I do want to keep my original data separate in other databases and b) automatic import lessens the possibility of import errors especially in data that needs to remain as accurate as banking information.

                             

                            Again it does seem that this must be such a common need that I'm surprised it's not basic functionality in Filemaker.

                             

                            Thanks again.

                            • 11. Re: Pulling data from several sources into one dataset
                              planteg

                              Again it does seem that this must be such a common need that I'm surprised it's not basic functionality in Filemaker.

                              I am not sure it's a common need, in the sens that FileMaker should provide a way to do that. I am not sure other database solutions provide this, unless I am wrong. Have you got that in another product ?

                               

                              Thanks

                               

                              Gilles Plante

                              • 12. Re: Pulling data from several sources into one dataset
                                sphilipr

                                Giles,

                                 

                                No I haven't seen it in another product I know no other standalone database products. I come from a front end web back ground where things aren't so clearly defined in terms of functionality.


                                Philip

                                • 13. Re: Pulling data from several sources into one dataset
                                  erolst

                                  sphilipr wrote:

                                  it does seem that this must be such a common need that I'm surprised it's not basic functionality in Filemaker.

                                   

                                  Why should it be? Usually, a database is set up to record entities of the same type in a single table, from which you could generate subsets – while what you insist on doing is the reverse. (I'm not saying that there aren't valid reasons for you to do so, but you should be aware that your approach creates redundancies and requires more maintenance.)

                                   

                                  So while this functionality/requirement is far from unheard of – and as you have seen, there are tried and working methods using native tools – it's not really common, let alone basic.

                                  • 14. Re: Pulling data from several sources into one dataset
                                    sphilipr

                                    My intention is not to cause offense here, I was just musing. It says newbie on my status. I don't pretend to be an expert.

                                     

                                    My need for redundancy is the desire to keep a pristine version of the data as this is financial information and errors need to be kept to a minimum and easily caught/corrected. My reason for a combination of sources is mundane in that not all data comes from the same place, we don't many of us have just the one credit card. That's all.

                                    1 2 Previous Next