This content has been marked as final. Show 16 replies
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!
P.s. Have a look on my youtube channel for some Filemaker Tutorials:
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.
How do I fix missing index?
Relationship, i.e., structure, . . . so far
ACA (Table) Accounting (Table)
Expense: 01 General = Expenses: 01 General
Format for: Expense: 01 General
• 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.
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
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.
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?
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.
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:
And a file:
I hope this shines at least a little bit of a light on your problem.
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.
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.
This screen shot shows the 'code.' Between the two pics, I think you should be able to see it's not complicated code.
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.
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.