CWH

thanks comment. That's what I was afraid of. A lesson in the importance of designing well before you start. Well, my amateur effort did the job for the last 7 years but now the whole thing is migrating to a giant sql system with none of the usability of FMP. I was just trying to milk some last info out of what I built, but doubt I'll have time to set this up.

Thanks, Zero. I think I didn't give enough background. I'm trying to find any set(s) of records for clinics where a value in field X (actually a percentage) has decreased over time. So my Find is something like Find any record where Report number is greater than 4 (4 quarter reports = operating over 1 year)and X in previous reports is greater. Since I'm trying to find clinics that have an overall declining trend, the script need to look at (and retrieve) a series of records.

I have a database that I set up (naively) flat. Each record represents a quarterly report from an HIV clinic in Africa. There is an id field for each record, so you can find all the reports for each clinic. Each report is numbered in another field. I need to compare the results from one quarter to another, finding any that have been operating for more than 1 year that have had a decrease in a field that tracks the percentage of women getting treated. I think I need some kind of script loop, and I started by self joining the id field so records for the same clinic are linked, but after that I'm not sure where to go. An If calc probably, but I'm ignorant about how they work. Any suggestions much appreciated.

Thanks, Ender So do I need 5 x 24 date fields in the related database? I'm assuming not, or what would be the advantage of making this relational. I think I need to read up on relational structures. Will experiment and pester smarter people again later.

Hi please excuse a beginner question, but I'm trying to devise a way to track multiple dates that relate to specific payments: when the request for payment 1 was received, when it was approved by Dept A, when it was approved by Dept B, when it was submitted to Accounting and when it was paid. I need to track these dates for up to 24 payments. Our database has separate records for each grant on which these payments are made. Is there a simple way to avoid having to create 5 date fields for each of the 24 payments? I suspect this is a job for relational databases, but I haven't ventured into that world yet. thanks for any help, Chuck

As I understand it, the accrual rate needs to be a field that can have different values for different employees, since the rate is either 3.33, 4 or 5 hours per pay period (which to make matters more complicated, is paid on the 1st and the 15th, so the number of days varies). Also, each record in the database represents individual employees. thanks for any help, Chuck

I am trying to calculate available hours of vacation after each pay period. The calculation would be dependent on a StartDate field and Today's Date and an AccrualRate field (each employee accrues X hrs per period) - DaysTaken, but I'm not sure how it should be constructed. This is probably the sort of things one should pay for but I work for a non-profit so we try to get as much as we can for free! Or if you can point me to a good template that would be appreciated. thanks, Chuck

Unfortunately, I don't think that will give me what I need, which is to tabulate each type of review that each reviewer is assigned, but only if App status=Reviewsend. So I think there has to be an If function involved, but the one I constructed has the problem I indicated above. If any of the applications assigned to a particular reviewer do not = Reviewsend, then it doesn't count any of them.

Fitch, thanks for your efforts on my behalf once again. Unfortunately, I wasn't clear -- the field is AppStaus and Review(send) is text from a value list. Even when I change the text to ReviewYes, it's still not counting the primary reviewer field for at least one record.

I'm trying to sum a field in one database (CTA) in a related db (Reviewers). I only want to total the Primary Reviewers from a particular group: those that are marked "Review (send)." I tried the following, but it only adds up the Primary Reviewers if ALL of the records are marked that way, otherwise, it's blank. Case(CTAone::AppStatus = "Review (send)", Count(CTAone::Primary Reviewer)) Maybe it should be an If function? thanks for suggestions

Fitch: Talk about above and beyond the call of duty! Thanks for the helpful example databases. I'm going to try it again with ours, although I think I'll use a calc field in the reviewers database that combines first and last name so the name is more apt to be unique. thanks again PS After giving this a go, I've run into another hitch. I need to see only the reviewers from the current cycle. I'm going to experiment with an If function to see if that can look at the Cycle field first and only calculate the totals from a specific cycle. If anyone thinks I'm headed down the wrong path, please let me know. Chuck [ February 20, 2002, 12:54 PM: Message edited by: CWH ]

Fitch: Your suggestion seems cleaner so I tried it but I think I'm out of my league with relational databases. I'm not understanding what the field "reviewer" represents. I set it up this way: relatinship One is reviewer= ::Primary Reviewer Relationship Two is reviewer= ::Secondary Reviewer and so on The latter fields are in the database with the applications, but the "reviewer" field is in the separate database with all the reviewers info. Then, the calc field in that db looks like Count(One::Primary Reviewer) All fields are blank when I set up a columnar layout in the reviewers db

That looks cool, but when I try to set up the first Count field, FM says it can't find the One::reviewer field, even tho I set up that relationship. Any thoughts?

Fitch, thanks for your suggestion. It works pretty well, but I think I wasn't clear in describing our situation. The applications are the same, it's the reviewers who are assigned as either Primary, Secondary or Tertiary. In order to get a count of how many reviews of each type each reviewer has, the way I implemented your suggestion required 3 summary fields and subsummary parts for each of them. Then to see the results, it requires sorting 3 different times for each review type. Maybe the clunkiness is due to the way the database is set up or maybe I'm missing something. By the way, I don't know what the protocol is for the forums, but I would feel guilty asking for this much free advice (you already helped me earlier this week), except that I work for a non-profit that funds pediatric AIDS research. Thanks again, Chuck

I have a similar problem, but using PatternCount seems like a cumbersome solution for it. I have applications that are assigned 3 different reviewers (Primary, Secondary, Tertiary). I want to be able to tell how many applications each reviewer has of each type. Using PatternCount, it seems I would have to set up a separate calc field for each reviewer, and there can be as many as 30.

Hey, that's great! Thank you. It works like a charm. I did run in to trouble when I tried setting up a more flexible field (YearToUse). The year has to be entered on every record I want to sum, which I can do quickly using the Replace function. It should probably be a Global Field. I haven't worked with Global Fields, but I guess now's a good time to start. Thanks again, Chuck

I'm trying to devise a script/calculation that will look at 8 date fields (DO1, DO2 etc) and if the value is 1/1/02...12/31/02, a value in a corresponding number field (AO1, AO2 etc) will be added to a total figure. In other words, there are 8 quarterly payments scheduled over 24 months, but I want to get a total of just the payments that will fall in a certain date range. It seems as if it should be simple, but the only thought I have requires a script with 8 different subscripts. Suggestions appreciated. Chuck

Thanks for that advice. It helped me figure out what an IDIOT I am. I put the calc and e-mail field in a layout and it still wasn't showing up, allowing me to brilliantly deduce that there is no e-mail address in the random record I was testing the script on. Thanks again.

Can anyone tell me why the first calc below works but the 2nd fails to put the e-mail address into outlook? They're from diff dbs so field names are diff, but the scripts to call them are same (Open URL, No dialog, name of calc field)? "mailto:" & PI_Email & "?subject=" & "Cycle#" & CYCLE# & "-" &Type_of_Grant & "-" & ID# "mailto:" & E_mail & "?subject=" & "Global Strategies Invitation"& "?body=" &"Dear " &First Name & Last Name thanks for any suggestions

I'm trying to devise a simple method to import form data (prbably from multiple Word docs) into a FM database. Is there an easy way to have to have FM look at a group of fields in the import data and enter one value based on which checkbox was marked? The Word form has 4 checkboxes, which export as individual fields with either 0 or 1, while the DB has just one corresponding field with the value entered as text. Do I have to set up fields in FM that match the ones from Word, then create 4 calc fields that pass text to the one field that I want filled in? Any general pointers about importing form data appreciated.

Thank you -- Works like a charm! I'm puzzled why I couldn't find info about this in the manual, in FM Help or on their Web site. Glad you're here as a resource. Chuck H.

Is there an easy way to calculate quarterly due dates? Most of our grant payments are made quarterly, but because some months have 30 or 31 days I can't just use 90 days in a calculation. Any suggestions much appreciated. Chuck Hoblitzelle EG Pediatric AIDS Foundation