"an amount in one of five numeric records"
Did you mean to say 'an amount in one of five numeric fields'?
My opinion: get Fusion, install whatever version of windows you use. Use Access while you work out you FileMaker solution. Don't use Bootcamp. If you're using XP, stay with it until you move to Windows 7. If you have Vista, oh well...but that's what I use. You can Command+Tab between Access and OSX-FileMaker all day long. Just keep in mind that a VM to the Mac (or the host) is just one file (though when inside your VM you'll see lots of files, that's the VM's perspective).
So for individual file back ups, of say an Access file, drag your copy from the VM to the Mac and put them in a folder on the Mac. When the folder has ten copies, Zip the folder and burn to a disc, et, et. After you install your VM, copy the VM to an external drive, and don't boot it up. Just keep this copy as a backup of a fresh install. Then install Access, et. The less the better, to keep your VM light. Once a month, defrag inside the VM (using Windows based tools), then shrink the VM using VMTools (otherwise you're VM will get too large over time). You can (perhaps more optionally) also follow this with a virtual defrag, and then an OSX based defrag using, say iDefrag (note OSX handles defragging by itself, but large files less well, and VMs are large files).
Whenever you move a VM to another location, because you're changing drives et, if it asks you was that move or a copy. Selecting 'copy' is more likely to trigger a need to reactivate. 'Copy' implies that you're, keeping the source of the copy as a VM you'll still be using. In other words, if you have ten machines, you could just copy the VM to each machine, but then you'll have to reactivate each one and give each their own product key or id.
It's a bit tricky, but you could do the YTD from the context of Clients (assuming you do have a Clients table), by defining a second relationship to the Transactions(?) table as:
Clients:: ClientID = Transactions 2:: ClientID
Clients:: cYear ≤ Transactions 2:: Date
where cYear is an unstored calculation (result is Date) = Date ( 1 ; 1 ; Year ( Get (CurrentDate) ) )
Then define the necessary fields as calculations using Sum ( Transactions 2:: Income ) etc. and place them on the report layout, in the sub-summary by client part.
Sounds like it would work, but I'm so new at this I don't understand some of the shorthand. I do have two tables. One is financial records and the other is client info. So what you're telling me is that in Client Info I create a field that calculates ytd totals by using that formula, so let's say in my client Info file my records would now be:
Is that what you're telling me? And I would get those summaries from the financial records table showing all records. then I would do my find for just this month's records and put my YTD amounts on the layout? That makes sense to me. I'll start trying, but please feel free to disabuse me of my euphoria if I didn't get it right.
You're saying Access is better than filepro? That I can't do it in file pro?
i think all you need to do is create a layout that has a single line listing your transactions (assuming that is one of your tables) in the body part of the layout (more or less as you showed it in your posting). then depending upon how you want to organize the results, you will need some sub-summary parts (first would be for year, then probably by month, and finally by client). when you sort your records according to these fields, you will get an organized summary. to get the appropriate totals, you will also need a few summary fields (e.g. monthly_total_income, ytd_total_income, monthly_total_expenses, ytd_total_expenses, etc). for the monthly total summary fields you will want to select the option to "restart summary for each sorted group" and then selet the apropriate group (most likely client in your situation). i use this basic method for a payroll database so that i can see a payees totals for both a short-term pay period, as well as their ytd and even lifetime totals. if i search for all records, it will break it down yearly, then by payee, then by pay period and i get subtotals for each payee and period as well as a grand summary of all payments in the found set. hope that is helpful.
Almost, but not quite.
Let me digress for a moment: you COULD do it the way you describe, i.e. find the financial records from the current year (not ALL records - as that would be just as incorrect as finding for the current month only), get the 3 summary values for each client, and put them in the client's record. However, that would take some advanced scripting, which I don't feel would be right for you at this stage.
So instead, we make the client's records from the current year related to the client - IOW, we create a filtered relationship, so that only current year's record are related. Then we sum the values of the related records only.
Note that the filtered relationship is IN ADDITION to the existing relationship, and that you must use another occurrence of the financial records table in order to create the new relationship.
Thanks for the response. You're saying Access is better than filepro? That I can't do it in file pro?
first, terry, i would like to reassure you that not only can it be done in filemaker, but that it can be done in several different ways (comment and i each have a method that will work for you). as for which is better, filemaker or access, i am not familiar with access but i can tell you that filemaker is awesome! it is a very powerful and infinitely customizable database application.
second, you can use the method i outlined without using any scripts or formulas. i do have a script that will sort the records for me, but that can be done manually, the same way you are maunually finding the records you want even though this could be scripted too. i have included screenshots below showing my "summary" layout both in layout mode and in browse mode.
finally, comment, i am curious about your method. i understand that your relationship will filter only the current year records for a given client and thereby automates the otherwise additional step of performing a find for the current year's records. but i have 2 questions. why do you use the sum function for calculating the totals instead of a summary field? also, how are you calculating the monthly totals? are you using a sub-summary part when sorted by month? please elaborate. i am always interested in learning new or alternative methods.
here are the screen shots from my solution:
The answer is simple: we are not answering the same question. I suggest you review the very first post in this thread to see why your suggestion won't work here.
Thanks so much for your comment and you too, JohnHorner. You guys are saving my life. I sure do like everything else I've seen of FileMaker, so I really want to make it work. Okay. Comment: I think that idea will work. I had the thought of using another occurence of the table, but I don't know how to do it. What I did was copy the table and then relate all the records so that if I added or changed a record to the main table, my other table would be updated as well. The first time I did it I tried to copy the sum fields as well and they did not copy. So the next time I did, I deleted all the sum fields and re-defined them. So I guess the question is how do I get a second occurence of the table? I'm sorry, but when it comes to this I have a bad case of the dumbs.
Thanks so much again.
Thanks so much for all your samples. You're amazing. In looking at this it seems like you enter gross earnings per pay period and then do the running total with restart for gross earnings YTD for each employee. Each of my clients normally have beetween two and six records which have to be summarized for the pay period and then year to date. Right now I'm using the sum with the running total with restart to summarize the records for the pay period. But then what I'm having trouble with is summing the year to date records. So I need to use the same records twice. Once to sum and print only the current month's data and the second time to sum the whole year's data. The bigger problem is that I don't yet understand all this stuff yet. Thanks for all your help.
Go to the relationships graph, click the 'Add table' button, and specify the 'financial records' table. Or select the existing occurrence of the table and click the 'Duplicate' button.
The new occurrence will be initially named "financial records 2" (you can rename it to whatever you like). Use this occurrence as indicated above.
Okay. It worked up to a point. I got the total of one of the five fields I was hoping to add up. It's a field that typically doesn't change throughout the year. On the other hand, the other field like that didn't work. These two fields are escrow fields. The income and expense fields didn't work. I did the same thing for both. I wanted to look at the second occurrence of the data base, but I can't seem to find it in the little drop down menu in the top left. I'm thinking the fix worked, but I still have some work to do. Thanks so much, Comment. You're great.
I got it! It works like a charm! Thanks so much.