jrp

Dang, I wish my brain worked like that. Thank you both!

Seems like this should be simple, but I'm stumped. I have the Week of Year (44), and the Year (2003), and I would like to calculate the date of the Sunday of that week (10/26/03). Possible? Many thanks in advance.

(edited to add some more info...trying to clarify my problem) I have a report that has no body - just multiple subsummaries, in a large line item file. Every record in this file is an line item from a timesheet - related to both a staff member (who exists within a department and within a company) and a project. The Subsummaries are: Company Department Staff Member Project Name All of which have an hour total and a cost total, using summary fields. I want to also include a summary of the count of projects. So a staff person clocks 36 hours, costing $720, and worked on 7 projects total (each project also has a break-down of hours and cost). But if I create a summary field that is the count of Project Name, I get the number of records (which might be 79) - not the number of Projects. Can I get a count on a summary? Many thanks.

You both completely rock. Many thanks!

I'm setting a Letter Body field with auto-entry of a calculated value: some text, then a currency figure from a related field, then more text: ---- FOR SERVICES RENDERED IN ARRANGING " & Upper(type_of_loan) & " LOAN IN THE AMOUNT OF $" & amount_of_mortgage & " ON THE ABOVE REFERENCED PROPERTY WITH " & Upper(Company_Name) &"." ---- I would like the currency to appear formatted, with the "$" and commas in the appropriate places. (ie. $900,000 ) But in a text calculation, only the text (900000) comes out. Does anyone know a handy calculation to parse out the numbers and get the commas in the right place? Many thanks!

Yes - the many-to-many is staff to projects, and the "bit more information" is the task that each staff member is doing on the project - a field that doesn't belong in the project file, or the staff file, neh?

OK - found the info I need, and some good theoretical discussion on the values of multi-keys and join files - good stuff! I think I'm going to go ahead and create a join file for the example I listed, since I need to store a bit more information about every link. But there's another instance of this in my solution where the multi-key relationship will work perfectly. It's certainly a nice tool to have in my belt. Thanks again!

I do understand that about repeating fields - which is why I was uneasy. I'm still unclear about the other options, though. I'm about to go searching through my books and this forum for more discussion of multi-key fields, but if you have time, and could explain it a bit? I would be most appreciative. Thanks.

*Repeating fields as Keys* I

Thank you! (btw, the first problem seems to be happening only in 12 pt fonts - so I fixed that.)

I have two value list questions: First, a formatting problem that I'm beginning to suspect can't be solved: My value lists, in pop-up lists, are displaying without enough space between each line, and the top bit of the text is getting cut off in each line, making it difficult to read. I've tried to play with the paragraph formating of the field, but nothing has any effect on the display of the list. Is this just a bug? (I'm using FM 6.0v3, Mac OS X 10.2.1) Second, I would love to copy the entire index of a field, so that I can use that to create a value list in another file (one that won't, eventually, be related in any way). How can I access that index? Many thanks!

I'm not at all sure where this question belongs, so I'm going to try relationships. I have 2 files: Reps Districts I need to do a monthly import of revenue data from an Excel file containing three fields: RepID, DistrictID, and Revenue. Most line items on this file will contain both a District ID and a Rep ID, but a few line items will only contain one or the other. I need that data to be available to both the Reps file and the Districts file, and I need to be able to summarize revenue for both Reps and Districts, based on Last Year's Total, and Current Year to Date. I created a third file, called Rev_LI (revenue line items) into which I import these files. I created a script to assign each line item, on import, a month and year. I created relationships from the Reps file and the Districts file to the Rev_LI file. But now I'm stuck. I can't figure out how to summarize the data into "Last Year's Total" and "Current Year to Date" on either the Reps file or the Districts file. Can anyone suggest a method? Would be so grateful.... jrp

Just a note on this: I encountered this same issue on a client install today. Filemaker Tech Support recommended a re-install of OS 8.6. This resolved the MPLIBRARY issue.

You can never index a calculated field. I believe that's rule you can't break. (I could be wrong, and would LOVE to know how). But - you can make your field a normal, indexable field, then put your calculation into a script or auto-enter value. This way, the calculation is done *to* the field, which should still give you the data you need, while allowing the field to be indexed.

I want to perform a lookup in a field, then restrict the editability of a field *only if* another field is not empty. If the user selects an ItemCode from a drop-down, the Term field should lookup that term from the related file

Thank you both so much! *hard at work*

I am updating a solution created by someone else. Here's my problem: I have three related files: Departments Staff Projects Departments and Staff are related such that for each Department there are multiple staff members, and each staff member is associated with more than one department. For each Project, A staff member and a Department need to be selected from a pop-up value list. I would like to use a conditional value list, so that once a Staff member is selected, only that person's associated Departments would appear in the Departments pop-up list. This seems elementary - but since there are three files involved, I haven't managed to make it work. Can someone help me? Is there a trick I'm missing? Many many thanks!

Is it possible to create a "SuperDeveloper" password that is not accessible by other users, even if they have all access to the file? I want to give a user access to edit a file, including define fields and scripting, without them being allowed to change my passwords or access.

Thanks Kurt - I really do need to go both ways, so to speak. Once I had it confirmed that it wasn't possible, I quit my whining and setup all 11 layouts, and with the scipt they print just fine...and in the end this is only a hand-holding measure for a nervous executive. Once he's comfortable with the new setup, I think he'll be happy with the other reports I've created, but for now I want him to feel like he still has access to the same data in the same format. Thanks again for your help!

Well, the report needs to be wide because my client says it needs to be wide. I'm trying to replicate an Excel spreadsheet that my database is in fact replacing - they want to be able to print out a report that "looks & feels just like what they used to have." So, I'm creating 11 separate layouts that look like each of the 11 pages of their Excel file. And a script that will print them all out. Seems like a silly amount of work, for a reasonable request. Ah well, now I know.

OK, but how? I create a new list layout, select all the fields I need, and then in preview mode it only shows the first page. Where do I put the fields to tell FM I want them to go to a new page?

Ack! I'm killin myself here. Isn't there anyone who can help? Is there no way to create a report in FM that is wider than one page? Please.

Using FM Pro 5.0v3, on a Mac. Trying to build a report that should be printing about 11 pages wide - about 10 fields per page, with a set of repeating titles on each page. I can't seem to create a report wider that one page. Is this impossible in FM? What am I doing wrong? Many thanks.

My favorite solution is to use FM's indexing feature and set the field as a pop-up list, using the value list of that field's index. So, every time you type an entry into the field, that entry is available in a list for the next time you create a record. If you begin to type in the field, it will automagically drop down to an already - entered item. Here's how: In layout mode, select the field, select Field Format from the Format menu, set it to Pop-up list, using value list - define value list - create a new value list, then select use values from field "your field name" and OK, Done, OK.

I don't know if it's too late for this to be helpful - but I've been working out a similar issue. I have an OK solution, lacking holiday resolution - but I've got an idea for that, and am hoping someone here can help us both figure it out. I have a sequential list of tasks, each with a given duration (of business days), each with a start date equal to the former end date (I calculate an estimated End Date, then the user has a field they can enter the Actual End Date, and the next process takes this into account). Using the same basic calculation droid gives - with my own additions - I created the list in this way: Step 1 Start Date Step 1 Duration Step 1 Start Date Work (a calculation to be sure we start on a business day - only happens once) If(IsEmpty(Step 1 Start Date), TextToDate(""), Step 1 Start Date + Case(DayofWeek(Step 1 Start Date) = 1, 1, DayofWeek(Step 1 Start Date) = 7, 2)) Step 1 Estimated Complete Date If(IsEmpty(Step 1 Start Date Work), TextToDate(""), Step 1 Start Date Work + Int(Step 1 Duration/5) * 7 + Middle("12345012340123601256014560345623456", (DayofWeek(Step 1 Start Date Work) - 1) * 5 + Mod(Step 1 Duration,5) + 1, 1)) Step 1 Actual Date Step 2 Duration Step 2 Start Date If(IsEmpty(Step 2 Duration), TextToDate(""), If(IsEmpty(Step 1 Actual Complete Date), Step 1 Estimated Complete Date, Step 1 Actual Complete Date)) Step 2 Estimated Complete Date If(IsEmpty(Step 2 Start Date), TextToDate(""), Step 2 Start Date + Int(Step 2 Duration/5) * 7 + Middle("12345012340123601256014560345623456", (DayofWeek(Step 2 Start Date) - 1) * 5 + Mod(Step 2 Duration,5) + 1, 1)) Step 2 Actual Complete Date ...and so on. Now then - on to the holiday issue. I have a global field that contains all the year's holidays ("gHolidays") - one that can be edited by the user year to year. I want to write a script that looks at the range between "Step 1 Start Date" and "Step 1 Estimated Complete Date" and compares it with the dates in "gHolidays" - if it contains one of those dates, it should add one day to "Step 1 Duration." This feels conceptually possible - I just can't figure out the calculation. Can anyone help? Or maybe just tell me it's impossible and I should stop trying... Many Thanks - and I hope this is helpful to leenu_n. -jrp [ December 31, 2001: Message edited by: jrp ] [ December 31, 2001: Message edited by: jrp ]