1 2 Previous Next 16 Replies Latest reply on Jan 31, 2014 11:02 AM by GaryVogt

    One database, two Tables.

    GaryVogt

      Title

      One database, two Tables.

      Post

           Let's try this again.  PhilModJunk doesn't want to understand the problem and apparently we have a problem explaining things to each other.  He says my database is too complicated, too big, and I have difficulty understanding exactly what he means by 'structure.'  Is 'structure' the way I use the fields?  If so, I've recreated the 'structure' here is a simplified form.I could copy and paste all of the fields but this post is too long already.  ------- so, here goes.  

           I asked this once before and thought I had it figured out.  But, alas, it still won't work.  

           Once upon a time I had two separate databases that were used, almost all of the time, together.  Both have income/expense information in them.  I used both of these for a long time and I'm happy with the way they worked separately.  Then, I thought, "Wouldn't it be nice if both of these databases were combined?"  The thought of rewriting all of the fields of the second database, over 500 fields, into the first database was overwhelming.  Then, I discovered the button that lets me import one database into another.  So, I imported the second one into the first.  The result, I just added some buttons to jump back and forth and now it's all in one database. 

           Here's the thing though.  Each database had its own Table for fields.  So, now I have two databases in one, i.e., one database, with two separate tables.  One database.  Two different data field tables.  I merged two commonly used files into one so I could share info more easily.  Note:  I have removed all relationships between the two tables.  WHY?  I have no idea what to make a relationship.

           >Problem: Database 2 has Summary and calculation results (and/or calculations that are summarized) that I want to use in Database 1.  The simplest step would be to just copy and paste the summary fields I needed from Database 2 into Database one.  However, it doesn't work.  all I get is a blank result.  During one attempt to put summary data fields from 2 into 1, I think through a calculation field, FM Pro said I could only do that through a Global field.  So, I messed with those for a while.  

           I Created a Global field that was a calculation of the Summary/Calculation field that looks something like ...

                 Expense General (calculation) = x+y+z

                 Expense General_G (calculation, global) = Expense General

      It worked at first.  I was happy to see the fields with the correct values.  Then, adding more data and now the global fields are empty.  So, if I have a calculation field that has a result, why does the result for the global calculation go empty?  Especially if the Global field only has the same calculation?

      Here is a sample of the 'structure' of the fields in database 2 that I want to see in database 1.

      ========Manage Database for Company=======

           Table: Accounting 2013

            

           Expense: Type        Text          Indexed, Required value, Allow Override

           Entertainment_Calc:                Calculation[12] =If(Extend(Expense: Type)="Entertainment";Amount: Tax Back Calculated;0)

           Food_Calc:                             Calculation[12] =If(Extend(Expense: Type)="Food";Amount: Tax Back Calculated;0)

           . . . . . . . . . . .   etc.  All of the fields being calculated and summarized are of the same form.

           -------------- note:     "Entertainment," Food," "Gifts," "Clothing," "Miscellaneous," "Travel" are in a dropdown menu.

            

           Entertainment_summary:      Summary         = Total Entertainment_Calc

           Food_Summary:                   Summary         = Total Food_Calc

           . . . . . . . . . . .   etc. All of the fields being summarized are of the same form.

            

           Expense: Total_Summary      Calculation       Unstored, =Entertainment: Expense_Summary + Food: Expense_ Summary + Gifts: Expense_ Summary + Clothing: Expenses_ Summary + Miscellaneous: Expenses_ Summary + Travel: Expenses_ Summary

           -----------------Note: This works just fine.  I've been using this same format for over 10 years. 

           -----------------Note: I have more sets of expenses like these in the same Table  

           =============================================================================

           -----------------Note: I created these because it said I couldn't put Summary fields into the part of the database created originally, i.e., Income_Expenses with a table name of Income and Expenses

            

           Entertainment_G:        Calculation         = Global, Entertainment_Calc

           Food_G:                      Calculation         = Global, Food_Calc

           . . . . . . etc.

            

           Expense: Total_G        Calculation       Gobal, =Entertainment_G + Food_G + Gifts_G + Clothing_G + Miscellaneous_G + Travel_G

           -----------------Note: When I created these, they worked just fine.  But, as time goes on, the Global result goes to 0

            

           Now, if I could get the Global fields to show something other than 0 in my primary database, we'd be stylin.'  Or, if there is a magical way I can view the summary fields from database 2 in 1, that would be great too.

        • 1. Re: One database, two Tables.
          GuyStevens

               If you couldn't get along with PhilModJunk then I think the problem might lie with you.

               PhilModJunk is a legendary Filemaker guru and knows how to see through peoples problems and structure like no one else.

               You managed to type a pretty lengthy post and I read trough the whole thing and still have not a single clue on what it is you are actually trying to do.

               I saw some words in there like "accounting" and "expense"  so that gives me a little bit of a clue. 

               But if you don't tell us what it is you are trying to achieve, how do you expect anyone to help you?

               Also you are creating all kinds of calc fields to separate all your categories.

               Have you ever used a list view with a subsummary part?

               I don't know anything about your situation and I don't know if this would be something you could use, but I think that's definitely something worth looking into.

               When we talk about the structure of a database we are talking about how different tables are related to each other.

               Remember what most people do in Filemaker is "Relational Database Design". This means that the most essential thing is relating different tables in a proper and logical way.

               I can't imagine how you manage your data without loads of repetitions if you only have two unrelated tables.

               Also a table with 500 fields doesn't look like a very good idea to me.

               Anyway, if I were you I would re-read your discussion with PhilModJunk. I'm sure it's loaded with tons of gold!

               Greetings

               Guy Stevens

               P.s. Have a look on my youtube channel for some Filemaker Tutorials:

          http://www.youtube.com/watch?v=5v2EzvaWyBk 

          • 2. Re: One database, two Tables.
            GaryVogt

                 I'm sure Phil is a guru and I'm sure he knows his stuff.  I apologize if I can't make my question any more clear.  I can only state what I experience and what I'm doing.  If I don't know the correct question to ask, maybe you can help me find out what I need to ask.

                 I wish I could make my case more easy to understand, but I can't.  

            "But if you don't tell us what it is you are trying to achieve, how do you expect anyone to help you?"

            I thought the bottom line was pretty clear, but, I guess it isn't.  Here it is again.

            • I have calculations in one part of my database.  I would like to show them in another.  That's it.

            I have tried connecting fields with the same name that exist in both Tables.  I drag the relationship line across and they connect. If the field in the second half (the database I imported into the first database) is made a Global field, then it works at first but not after I do anything in the database.  If I take off the Global and make it 'unstored' then I get "Index missing."  

            How to I make the tables 'related' so I can use the data?

            "Have you ever used a list view with a subsummary part?"

            - yes.  And I do use SubSummary parts when I need them.  But, that isn't what I want to look at.  I want to display the results on a page (layout) that I use for summary.  I want to see all of the results on the same page.  Image a Schedule C and all of the 'fields' that need data.  That all comes from somewhere.  A SubSummary Part is not useful here.  

            "When we talk about the structure of a database we are talking about how different tables are related to each other."

            Structure:  Your description of structure is simple and clear.  It would have been nice if Phil had provided that information.  But he didn't.  For all I know, his 'structure' meant the way I used input and calculation fields.  I have never taken a class on databases so that is why I ask questions here.  If I don't know the vocabulary with which you are familiar, then teach me.

            Prior to me connecting the same name fields in both tables (by dragging lines between them), there was nothing connecting them.  I.E., no structure if structure only refers to the way the tables are related.  Making related Tables is still a mystery to me.  The only relationship I use is a lookup if a customer number is inputed.  Then a lookup occurs.  HOWEVER, only one of these two tables (the Table from the first database) relies on customer numbers.  And only for input from a separate customer database.  The two tables in the database I'm talking about never had any relationship until I connected the same name fields in an attempt to see if that would work.  It doesn't.

            "I can't imagine how you manage your data without loads of repetitions if you only have two unrelated tables."

                 Well, good point.  If I could make a Table with just Expenses for Clothing, for example, that would be nice.  And then a Table for Medical.  That would be awesome.  Problem is, I don't know how to connect them to share the information I need.  With it all in one Table, no problem.

                 Have you ever heard of FORTRAN?  In the 70s I learned how to program FORTRAN using goto statements.  Lots of goto statements.  Then, along came FORTRAN 77 and an easy way to make subroutines and move data from one part to the other part with just a parentheses.  And IF-THEN-ELSE statements were more than just evaluating for a -1,0,+1.  It was a Godsend.  My Primary programs, which had no subroutines, were as much as 30,000+ lines.  Talk about a nightmare.  With subroutines, the Primary part could be made a few as 20 lines.  Each subroutine had its own task.  It all made perfect sense to me.  Pascal was a little more challenging, and C++ was fun for a while.  

                 So, do you want to help me with this?  I would appreciate it.

            • 3. Re: One database, two Tables.
              GaryVogt

                   How do I fix missing index?

              • 4. Re: One database, two Tables.
                GaryVogt

                     Relationship, i.e., structure, . . . so far

                     ACA (Table)                    Accounting (Table)

                     Expense: 01 General  = Expenses: 01 General

                     =======

                     Format for:       Expense: 01 General

                     I've tried:

                     • Number, Number auto enter Calculation (Accounting ::Expenses: 01 General), Number lookup (Accounting ::Expenses: 01 General)

                     • Calculation  unstored = (Accounting ::Expenses: 01 General), Global =  (Accounting ::Expenses: 01 General)

                     • Calculation  and every form of GetFunctions I can find.

                     Nothing works.

                     Note: Expenses: 01 General is a calculation field = Entertainment: Expense_Summary +Food: Expense_ Summary +Gifts: Expense_ Summary +Clothing: Expenses_ Summary +Miscellaneous: Expenses_ Summary +Travel: Expenses_ Summary

                • 5. Re: One database, two Tables.
                  GaryVogt

                       Here is something I just discovered.  I thought all of the records for each half of my database was available in each half.  Apparently when I imported one database into the other, it keeps both databases separate but not existing under the same database.  Hmm.

                       I'm just trying to display the summary results from one set of records that exist in my database onto a page with summary results that has another set of records.  I should be able to do that.  

                  • 6. Re: One database, two Tables.
                    GaryVogt

                         Quote: When you join two tables using a relationship, you establish criteria (?) that FileMaker Pro uses to display access to [related records].  Your criteria can be simple, such as matching a field in TableA with a field in TableB.

                         OK, that is what I did.  The information from the field I select in TableB is not displayed in the field I select in tableA no matter what I define TableA to be.  

                         I see something that says "Matching Records."  What if I want all of the records summarized.  What is an easy way to make that relationship?

                    • 7. Re: One database, two Tables.
                      GaryVogt

                           OK, here is what is making it work so far.  

                           TableA I added a Global number field called iRelate_A.  TableB I added a Global umber field called iRelate_B.  I put '999' in each.  Then I used the Relationship page to connect these two together.  Then, I deleted all of the additional Global fields I'd made in order to try and figure out a way to make this thing work. Then, In Database_A (the front half of the database), I just inserted the field from Database_B.  

                           So far, so good.  

                           Seems kind of silly though.  I mean, I should be able to insert a field from anywhere and it should work.  Oh well.

                      • 8. Re: One database, two Tables.
                        GuyStevens

                             I think you are not really making any meaningful relationships. I don't even know if you can make relationships between global fields.

                             And I wouldn't know why you would want to.

                             What you really need to start thinking about is meaningful ways to relate your data.

                             Look at whether if you have a lot of repetition going on in your file or not. If you do, then that means you might be able to improve by creating related tables. 

                             I think you might be having an issue that I think I can point out in the following video:

                        https://dl.dropboxusercontent.com/u/18099008/Screencasts/Structure.mkv 

                             And a file:

                        https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Database_Structure.fmp12

                             I hope this shines at least a little bit of a light on your problem.

                             Greetings

                             Guy

                        • 9. Re: One database, two Tables.
                          Vicky

                               Are you trying to change from a flat database into a relational database? I made a simple of example of a flat versus a relational database and took a screenshot of what it looks like.  I hope it helps.

                                

                               Here are some links on relational database design.

                               http://www.techrepublic.com/article/relational-databases-defining-relationships-between-database-tables/

                               http://help.filemaker.com/app/answers/detail/a_id/3247

                               http://help.filemaker.com/app/answers/detail/a_id/3246/related/1

                          • 10. Re: One database, two Tables.
                            GaryVogt

                                 Guy, I have no software to open a .mkv file.  When I click on it it goes to my download folder and nothing else happens.  Still not quite sure what you mean by 'repetitions.'  I do not have duplicate inputs for data if that is what you mean.  

                                 Vicky, When both of these databases were created, they were created in whatever Filemaker Pro was available in 1995.  That is when I started them.  They operated by themselves.  Using the description, 'Flat database,' makes sense.  

                                 The primary database (DbA) is, for all practical purposes, the Schedule C you would fill out  for self employment income.  This database is used to input income and expenses.  There are a lot of records in this because there are a lot of income and expense records.  I kept income and expense together, initially, because it was easy to find a net income.  The concept of making DbA a Schedule C came around 1998.  DbA has gone through a lot of iterations to make it do what I wanted.  It was always a flat database.  

                                 The secondary database (DbB) is a record of personal expenses.  I included a screen shot of a summary page showing the results of all of the records.  EACH line item has its own record.  This entire database uses only 50 (or so) records.  Under Sorter: I have the type of expense.  When I click on the pink box, it finds all of the records named "General."  Under 'General' I have separate records for Entertainment, Food, etc.  The example I attached does not show taxes.  If I choose to input the total invoice, I can click on 'back calculate' and it will go in and back out the subtotal and tax.  The second screen shot shows just the records found when clicking the pink 'General' box.  Notice that the rest of the summary fields are empty.  ----- apparently I can only attach one image.  

                            • 11. Re: One database, two Tables.
                              GaryVogt

                                   This screen shot shows the 'code.'  Between the two pics, I think you should be able to see it's not complicated code.

                              • 12. Re: One database, two Tables.
                                GuyStevens

                                     I would suggest the VLC player. It should be able to play this file.

                                http://www.videolan.org/vlc/ 

                                     Greetings

                                     Guy

                                • 13. Re: One database, two Tables.
                                  GaryVogt

                                       A few years ago, after using both of these separately for many years, I thought about putting them together and just jumping back and forth in one database.  I saw a button that said, Import, so I made copies of both and then imported DbB into DbA.  It worked.  It would have been nice to show personal expenses on the main summary page in DbA.  Nothing I tried worked.  I asked on this Forum a couple of times and was never able to come up with a solution.  

                                       Here is a screenshot of the Schedule C.  The data in the lower right comers from DbB.  That is the information I wanted to display on this summary page.  Seemed simple enough to me.  

                                       It never occurred to me to connect, i.e., relate, the two Tables with a dummy field.  I looked at another one of my databases and how I used customer number to import data across databases, I thought maybe it will work within the same database.  As mentioned above, a Global field in each linking the two tables together seems to work.

                                        

                                  • 14. Re: One database, two Tables.
                                    GaryVogt

                                         One last question.  I moved one of the 'pink' guides to the edge of the window when zoomed into 400% thinking it would scroll the screen.  Turns out, all it does is make the guide disappear.  How do I get it back?

                                         In earlier versions of FMPro, I could turn off the guides and when I turned them back on, both guides would be in the middle.  Can't do that now.

                                    1 2 Previous Next