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.
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