12 Replies Latest reply on Dec 8, 2009 2:47 PM by philmodjunk

    External Data sources

    mattb

      Title

      External Data sources

      Post

      The commercial app that I'm rebuilding in FMP (OSX) is an ideal candidate for using external data sources.  I have a set of layouts in the main DB, some of which are designed to be used with tables from external FMP databases.  These external databases are basically raw data in tables, no layouts.  I successfully set up an external data source and was able to successfully display data from this external source in a layout contained in the main DB, so the functionality works, BUT...

       

      Is it absolutely necessary that each external data source table be referenced in the Relationships window of the main DB?  I ask this because my app potentially has access to 91 external FMP DB's, each with at least 3 tables, sometimes more, so we're talking about adding in excess of 300 table references to one screen!  If I had to add each and every external table to the main Relationships window in order for them to function in this fashion, that would be an awfully dense screen even if each referenced table is minimized.  These tables just need to lie loose anyway, there are no relationships going on.  They are just being accessed by layouts in the main DB.

       

      I was hoping on the layout itself I could specify the external source, but it seems to want that external source reference in the Relationships window to be present before it will give me access to that data.  Is this the only way to accomplish external data access???

       

      I'm trying to avoid adding duplicate layouts to the external DBs and running them as separate apps.  

       

       

       

      Matt Bloomfield

        • 1. Re: External Data sources
          mrvodka
             Do you really need 91 other FileMaker files?
          • 2. Re: External Data sources
            philmodjunk
              

            An option to consider:

             

            Put as many tables as you can in one file, no scripts or layouts needed.

             

            Create different "interface only" files as needed to link to these tables and define only those relationships needed for a given "interface" file. This may allow you to group your relationships in different interface files (which can be seamlessly linked to appear like one file to the user) into less complicated sub-groups. Note that you are trading off one form of complexity (the Relationship Graph) for another (Multiple Interface files) so this may not work for you.

            • 3. Re: External Data sources
              mattb
                

              Phil:

               

              Put as many tables as you can in one file, no scripts or layouts needed.

               

              Create different "interface only" files as needed to link to these tables and define only those relationships needed for a given "interface" file. This may allow you to group your relationships in different interface files (which can be seamlessly linked to appear like one file to the user) into less complicated sub-groups. Note that you are trading off one form of complexity (the Relationship Graph) for another (Multiple Interface files) so this may not work for you.

               


              Hmm...I'm trying to wrap my mind around this concept and don't quite get it yet.  Let me boil this down to the essence of what I'm trying to do and then maybe you could clarify your suggestion to me in more practical terms.

               

              For the sake of example, let's say I have 3 FMP databases, 1 main database with layouts and scripts, and 2 external ones with only tables.  The table names and schemas are identical in all 3 DBs.  I would like to use some of the layouts in the main DB to display data from the external DBs, by user choice.  So when the user makes his choice, the layout would display data either from external DB #1 or external DB #2.  The problem, as you know, is that FMP requires one to associate a layout in advance with one particular table, be it internal or external.  I need (or would like) the ability to associate a defined layout in the main DB with data in a table in external DB #1 or external DB #2, depending on the user's choice at runtime.  That's the crux of the matter.

              Would your "interface only" idea help me in this regard?

               

              It's looking as if I may have to combine tables from all these separate DBs into a smaller number of DBs.  Even further, some of the single-record tables could be combined into a single multi-record table.  Some of the tables are multi-record and could not be combined in this fashion, so they would need to stay separate and have unique names (right now they don't).  

               

              Kind of a clusterf*ck for me, because I'm trying to duplicate existing functionality of an existing Windows program, and it's going to have to be accomplished somehow, unless my client wants to dump the project after already having spent some money on me!  Anyway, I hope this gives you a little more clarification on what I'm trying to do, and if your "multiple interface" idea can be applicable, I'd like to hear more about it.  Thanks.

               

               

               

              Matt Bloomfield

               


               


              • 4. Re: External Data sources
                philmodjunk
                  

                "Would your "interface only" idea help me in this regard?"

                No that's not what I had in mind when I suggested this.

                 

                I think Mr. Vodka and I are still waiting for the answer to: Why 91 separate files? You can merge these files into a single file of many tables and this can simplify certain aspects of your database design.

                 

                I'd also like to see your reasons why you think this: "Some of the tables are multi-record and could not be combined"  is a true statement. If you have multiple tables that can all be displayed from the same layout, that suggests similar field definitions and that in turn suggests that you can easily merge the records into a single table assigned to that layout. Instead of selecting a table for use with a given layout, you would use a find or Go To Related Records to select the group of records (Called a found set) that you would view with that layout.

                 

                An example or two might clarify the issue.

                • 5. Re: External Data sources
                  mattb
                    

                  Mr. Vodka:

                   

                  Do you really need 91 other FileMaker files? 

                   

                   



                  I'm rebuilding an existing commercial Windows app that was written in Visual Foxpro.  In VFP, layouts ("forms" in VFP-speak) are not associated with any particular table when they are created.  So what I'm doing there is using a lookup table.  When the user makes a choice, the lookup table tells the form

                   

                  which folder to go to and which set of tables to use.  In FMP, the necessity of locking a layout to a table in advance, i.e. during the design phase instead of at runtime, is problematic to accomplishing what I need to do.  

                   

                  Also, the way this program is distributed on the Windows end,  only the equivalent of 35 "external DBs" are used.  The other 56 are optional add-ons purchased (or not) by the user, divided into 14 groups of 4, each of which may be purchased singly and then integrated into the app by addng entries to the lookup table during their install process.   

                   

                  See my note to Phil also for further details.  Any useful suggestions are appreciated!

                   

                   

                   

                  Matt Bloomfield

                   


                  • 6. Re: External Data sources
                    philmodjunk
                      

                    Our posts were simultaneous. :smileywink:

                     

                    There are other ways to do what you describe that do not require so many separate files.

                     

                    Certainly, the basic set of 35 files can be merged into one file and you may find that you can make similar files that group several files into a single combined file that represents one "upgrade" module. You could even merge all your tables into a single file and simply use a "configuration" table to enable/disable specific modules for a given customer though this may complicate the distribution of upgrades to those same modules.

                     

                    See my earlier post about merging all tables that are to be displayed from the same layout (assuming they're not related records to be displayed in a portal of course).

                    • 7. Re: External Data sources
                      mattb
                        

                      Phil:

                       

                      I'd also like to see your reasons why you think this: "Some of the tables are multi-record and could not be combined"  is a true statement. If you have multiple tables that can all be displayed from the same layout, that suggests similar field definitions and that in turn suggests that you can easily merge the records into a single table assigned to that layout. Instead of selecting a table for use with a given layout, you would use a find or Go To Related Records to select the group of records (Called a found set) that you would view with that layout.

                       


                      Thanks for your reply.  See my response to Mr. Vodka as to the 91 potential DBs, 35 of which are distributed with the main program.  The others are purchased as optional add-ons.  Most certainly all of the separate single-record tables can be combined into a single table, which will simplify matters greatly.  As to the multi-record tables, all of which have identical schemas and indeed can be displayed by the same layout (one at a time of course), I'm less sure.   

                      This is an app for screenwriters and the main DB contains tables for the user's story.  Where the external DBs come into play is that each screen in the program has a companion "example" screen with filled-in data that we supply.  The user chooses 1 out 35 (or more if optional add-ons are purchased) movie examples to display this example data.  

                       

                       

                      The multi-record tables are for up to 99 cast characters in one table and up to 200 scenes in another table.  So these tables have 99 and 200 records, respectively.  In the case of our initial distribution, I just don't see how 35 of these multi-record tables can be effectively combined into a single table.

                      In the case of the 99-record "cast" table, there are 54 fields.  54 * 35 = 1890.  Are you suggesting I create a single table with 1890 fields?  In the

                      case of 200-record "scene" table, there are  112 fields.  112 * 35 = 3920, so that's even worse and only taking into account the initial distribution, not any of the add-ons. 

                       

                      Or are you saying just append all 35 tables into a single table leaving the field structure alone?  For example, in the case of the cast table, the first 99

                      records belong to example #1, the next 99 records to example #2, etc.?  In this scenario, the cast table would have 35 * 99 or 3465 records and the

                      scene table would have 35 * 200 or 7000 records.  Either way the numbers are a bit staggering, but I do have to find the most practical way to make this work.

                       

                       

                       

                      Matt Bloomfield

                       

                       


                      • 8. Re: External Data sources
                        philmodjunk
                          

                        Matt,

                         

                        Some of my tables store over a million records so storing less than 10,000 records doesn't seem so unmanageable. I am not suggesting that you merge individual records into one massive record with 100's or 1,000's of fields. That makes no sense whatsoever.

                         

                        "...first 99 records belong to example #1, the next 99 records to example #2, etc.?..."

                        That's sounds exactly like what I mean though you wouldn't need to have precisely the same number of records for each example.

                         

                        Here's an example from one of my databases that currently stores over 180,000 records:

                         

                        Each record represents one invoice. The invoices may be "printed", "Ready for Cashier", or "Pending".  A status field records this "status" text and another field stores the date a given invoice was printed. Users may pull up a list of all records printed on a given date by clicking scripted buttons in the header. + and - buttons move you forward and back one business day at a time and a "specify" button pops up a floating window where the user can select a date from a popup calendar, specify  a range of days, a month, a quarter or even a year. Each of these buttons pull up different groups of records to be displayed with the same layout with little or no delay. The group of records found could be less than a hundred or several thousand. All the scripts find the specified records by date--excluding all that don't have "Printed" in their status fields. A different layout with a different set of fields from the same table is used to list all "Ready for Cashier" or "Pending" records.

                         

                        Make sense?

                        • 9. Re: External Data sources
                          mattb
                            

                          Phil:

                           

                          That's sounds exactly like what I mean though you wouldn't need to have precisely the same number of records for each example.

                           

                          Here's an example from one of my databases that currently stores over 180,000 records:

                           

                          Each record represents one invoice. The invoices may be "printed", "Ready for Cashier", or "Pending".  A status field records this "status" text and another field stores the date a given invoice was printed. Users may pull up a list of all records printed on a given date by clicking scripted buttons in the header. + and - buttons move you forward and back one business day at a time and a "specify" button pops up a floating window where the user can select a date from a popup calendar, specify  a range of days, a month, a quarter or even a year. Each of these buttons pull up different groups of records to be displayed with the same layout with little or no delay. The group of records found could be less than a hundred or several thousand. All the scripts find the specified records by date--excluding all that don't have "Printed" in their status fields. A different layout with a different set of fields from the same table is used to list all "Ready for Cashier" or "Pending" records.

                           

                          Make sense?

                           



                          Yes that makes sense.  When I concatenate the example files, none of them will have 99 cast members and 200 scenes - they all have varying amounts less than the maximum.  So one example might have 20 cast members and 110 scenes etc. etc., so it would be incumbent on me during the importing process to select the correct number of each example so I don't import useless blank records.  Also, I would probably need to add an identifying field on each record, so I can easily know where one example stops and another begins.

                           

                           

                          My only concern is that in the main DB for the user's story I am using portals to display all of the cast characters and all of the scenes on a single screen (including blanks because I can't know in advance how many cast characters and scenes the user will input).  But I'm not far enough along in the process to know what questions to ask just yet about implementing this on example screens.  I'm thinking the portals on these example screens should contain just the right amount of rows because the data is pre-defined, but that brings up the issue of how to re-define portal rows depending on the # of records involved.

                          Anyway, I'll be necessarily thinking about big-picture issues in the immediate present, but if I take the single-table approach here with all 35 example DBs, do you foresee any problems with using portals on them?  

                           

                           

                           

                          Matt Bloomfield

                           

                           

                           


                          • 10. Re: External Data sources
                            philmodjunk
                              

                            It sounds like you are getting the idea and Portals sound made to order for what you describe. A couple of hints to point you in the right direction once you start setting up the portals. 

                            1. You can use an extra pair of match fields to "filter" the records that appear in a given portal. Search this forum using the key words "filtered portal" you'll find a number of examples.
                            2. If you enable "allow creation of records via this relationship" you can display the existing records in the portal and a single blank row will appear at the bottom. Enter some data into the fields in this row and a new related record is automatically created to record this data. Thus, adding a new cast member is is simple as typing new data into the bottom blank row.
                            3. You have a number of ways to display your portal records, you can include a scroll bar so portals can display more records than will "fit" in the portal.

                            There's also a flawed but powerful script step called Go To Related Record... Go To Related Records is a very useful tool, but which is very poorly documented. To learn more about GTRR, click the following link:

                            The Complete Go To Related Record

                            • 11. Re: External Data sources
                              mattb
                                

                              Phil:

                               

                              Yes, I'm already using portals with scrollbars for the main "user" story, so I'm less concerned about the user adding new info via the

                              portal as I am in displaying differing amounts of "example" info in the example screens with portals.  Let's say example #1 has

                              20 cast members showing up in the portal and example #2 has 10.  Is there a way that one layout can show different numbers of

                              portal rows?  Anyway, this is an issue for a little bit later - I need to consult with my client about building these large combined DBs

                              from the separate smaller ones.  Of course the option also exists that I can leave them alone and just import cloned layout sets into each

                              of them, but that sounds like the greater of two evils to me!

                              • 12. Re: External Data sources
                                philmodjunk
                                   Using your example, just set the portal to display 20 rows. The space for the additional 10 records will remain blank unless the user adds more records.