AnsweredAssumed Answered

One database, two Tables.

Question asked by GaryVogt on Jan 25, 2014
Latest reply on Jan 31, 2014 by 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.

Outcomes