Can you describe the relationships involved here? It sounds like you may have 11 tables, the 10 project tables and one "report" table on which you've based your report layout, but I could be wrong here.
Using 10 separate tables, one for each project, seems a very cumbersome design. If you had a unified Projects table and a unified transactions table for tracking income and expenses, this report would be much easier to set up.
Projects::ProjectID = ProjectTransactions::ProjectID
I understand your point. Here is how it's set up. I manage commercial real estate on 10 different streets. Each street has X number of tenants. I created a table/layout for each street so i could simply track income/expenses by month. Each new record is a new month. So, while it was for sure a bit cumbersome to set up, i have a page with 10 tabs on top (for each street's layout), from which i can go to any street's income/expenses for the current month with one click. The 10 tables are linked by Month & Year.
The report layout (which does not have it's own table) is based off of 'Street A'. 'Street A' is linked by Month and Year to Street's B through J. In the report, i currently have Project A's Month and Year at the top of the body, followed by the fields for Project Name and Project Income for each street.
This is basically what it looks like, assuming all fields are being entered into the 'body' of the report layout
Project A Month Project A Year
Project A Name Project A Income
Project B Name Project B Income
Project C Name Project C Income
While in 'List View' i can scroll throgh all the months. While in 'Form View', each new record is a new month/year with the corresponding income figures for all of the streets.
It was working perfectly until the current month of march. It was seamlessly showing me income for all properties by month. However, it's not showing income for March. I tried troubleshooting the problem on a backup file and actually got it to work, but when i went back to the original file it didn't work. I tried entering a subsummary section and i put the Month and Year in there and all of the sudden all of the income figures for March popped up. Not sure what i did, but now i can't get it to work again on the master file.
Thanks as always for your help.
Main idea that comes to mind is not to set up your tables this way. You can have your "layouts for each street" without having to use separate tables. You might even be able to have just one layout and use controls, scripts, fields to make that one layout work for all your "streets", but only one "street" at a time. This eliminates the need for adding layouts, tables and relationships any time you need to add another project to your system just to point out one other major advantage.
I'd use two tables Projects and Project Income. One record in Projects for each housing Project and one record in Project Income for each Project's month. A find on ProjectIncome can then pull up the data you need for your report just by specifying the month with summary fields an sub summary parts used to compute monthly sub totals.
Leaving that comment to one side, there are several possible issues. The index of one of your fields could be corrupted. Odd results when you perform finds for dates are a common symptom of that or your relationships and calculation fields have some flaw that kicks in for the month of March and not other months. Describe the fields and/or calculations you used to link the records by month and year.
To reindex a field, you can turn indexing off and then back on via the storage options for that field (turn it off, exit manage | database, then return to Manage | database and turn it back on.)
You can also rebuild all your file's indexes by importing all the data into an empty copy (clone) of your file.
If you have FileMaker 11, you can also use Advanced Recovery options to rebuild your file's indexes:
- With the file closed, select Recover from the File Menu.
- Select "Use advanced Options"
- Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
Ok. That's great, i didn't realize that i could put all of the fields for each layout in one table, maybe i'll give that a try. Will i lose all of my current information for each record by doing this? I don't know if i have the ability to set it up with just one layout, although that certainly sounds easier than dealing with 10 layouts.
I tried re-indexing the month and year fields but it unfortunately didn't work.
For both the month and year fields: they are both text fields set to value lists where i list the all 12 months and roughly the next 5 years respectively. I use a drop down list (with arrow) to select the desired month/year. Should those two fields be set up any differently?
What kind of fields are used in the relationships and drop downs? text?
Do you have data like January 2011 for the values in this list or something else?
I'm looking for some very small difference between the values entered in "street A" and the values that are entered for this month year combo in the related tables. It might be invisible to the eye as it could be a character such as an extra space character.
When it comes to replacing you 10 tables with one, You can use Import Records to pull all your existing data into a common table one table at a time. Immediately after each import, you can use Replace Field Contents to update a field in the table that identifies the Project to which they belong so that they can link to a the correct record in the projects table.
Hint for getting a single layout to use with all projects one project at a time: if you base your data entry layout on the Projects Table, a portal to Project Income will only list records for that project and can be used to log new records for each month's income/expenses etc.
Yes, text fields. But the text fields are programmed to show a value list. In other words we are dealing with two fields here. The Month field and the year field, both set to value lislts, both text fields. So technically there is no room for error in terms of adding a space or anything like that. The user is simply clicking on the desired month and year.
Just because you have value lists doesn't mean you don't have an extra character in one of these two fields somewheres.
I'd put a copy of these fields, without value list formatting on a layout, find the problem records and click into the fields and examine them very carefully.
When you tried reindexing, did you try reindexing ALL fields in the file?
This statement you made: "I tried entering a subsummary section and i put the Month and Year in there and all of the sudden all of the income figures for March popped up." Strongly suggests an issue with the field indexes.
Given your structure, I'm not sure why you would need a sub summary part but are any of these fields in a sub summary part in your current file? I understood that all these fields were in the body.
Ok, i'll go back and look at the value lists as well as examine the fields more carefully. When i re-indexed, i only turned the index off and then on again for the month and year fields. I'll try re-indexing the entire file.
Yes, all fields were in the body, but when i was troubleshooting and trying different things, it was when i moved the 'month' and 'year' fields in and then out of a subsummary part, that all of the sudden the march figures appeared.
I went through every project's march page and all of the month and year fields are identical. Also, when i re-indexed the entire file, the March figures were still not showing up on the report. It's so bizarre how i was somehow able to get these figures to show up on a backup file and then now i can't do that on the master file. I'm pretty stumped on this one.
With regards to the 'Month' field and the 'Year' field...i'm going through each table and noticing that some of these fields are set to 'minimal' indexing while others are set to 'all'. Not sure how that happened, it certainly wasn't on purpose. What should they all be set to?
I was speculating at the time you got the March data to appear, you also sorted the data--a common next step when you have a sub summary part. I've had data in a field fail to allow me to find records of a specific date, but when I sorted by that date field, I could find the missing records by scanning down the list of records to find the section in which they should be sorted into. Repairing the indexes corrected this issue. (I'm remembering an issue of several years ago so I may not have those details fully correct, maybe I sorted on a differnt field than the date.)
I'd certainly try changing them all to "all". I don't see why this would make a difference just for the month of March, but it's worth a shot.
Agreed, that seems to be how i got it to work on my backup file. I tried that on the current file and it still hasn't worked. I also refreshed the indexes of the field showing the project name for each project. Still doesn't work. Looks like i'm just going to have to find a new way to create this report.
I should also note, that when i got it working in the backup file. I created new records in each layout for april and entered data in the income fields. When i went back to the report, the march data was still there, but then the april data was missing, except for the data from the table of which the report layout is showing records from (Project A). That is also whats going on with the current file and 'March'. It only shows data from 'Project A', not projects B through J.
Does a record for March actually exist in the first table or might it have been accidentally deleted?
Yes, there is a record for March in each of the 10 layouts. I actually just got it working again in a test file by duplicating a different report (that is also based off of month and year fields. It too at first didn't show anything for march and then all of the sudden (w/ no sorting , no sub-summary) it worked. then i went back to the actual report i was working on (in the same test file) and sure enough, there were the march figures. Trying now to make that happen in the master file. Its so glitchy. I wish i just set it up in a more simple organized way in the beginning.