Owen Mathews

Thank you both! Color me impressed, relieved and somewhat confused. That is a bit non-intuitive, the way it works... Why does a pop-up menu seem to correctly display the name, but a drop-down list display the (hidden) ID until it actually drops down? Bug or feature? I'm glad to find such an easy solution.

I hope I'm overlooking the easy solution... If I have a many-to-many relationship (my sample has articles and contributors), how do I set up a layout that allows for easy linking of the two? That is, I have a layout based on Articles that has a portal from Contributors, and I want to be able to create the relationships by clicking in the portal and choosing the contributors to the article. This doesn't seem to work across two hops in the relationship diagram. I click on the portal row and because the field is from a table not directly related to Articles, no record is created, and I can't display a drop-down list. I think I understand why, but surely there's some way to do this simply? I want a nice drop-down list when I click into the next empty portal row so I can choose a contributor and associate it with the article and have the join row created automatically for me. Ideally this would involve little to no scripting or buttons, but I'm flexible. Any ideas? Articles.zip

Thanks for the advice, Genx. Do you think I'd get more responses if I re-posted in Right Brain? I'm going to try the script opening a new window and see if the performance is acceptable.

First off, I wish there was a better place to post this. I ended up deciding on ScriptMaker, but I could've posted in Layout also, I suppose. Why not have a UI-related forum dedicated to issues related to creating advanced interfaces? I've attached a file that contains my implementation of row highlighting--not for portal rows, but for the records in the layout itself, when viewed as a list. To see it in action, click on Build an Image or the Record a Purchase. You'll probably see the behavior I'm annoyed about pretty quickly if you navigate around in my modal window selecting the software titles. Basically I'm using a global field to store a user's session key, which is based on a timestamp and MAC addresses to ensure uniqueness, which is then used to keep records of selected items in a table related to the TO that the layout is based on. An invisible button is laid out on the record that, when clicked, manages the selection highlighting and unhighlighting depending on whether the dialog requires only one item to be selected, or allows multiple selections. The problem is that the scrolling jumps as you select items. I'm assuming this is a consequence of my relatively complicated script, which requires me to switch to several different layouts to manage records relating to selections, before swapping back to the layout for the modal window. I'm assuming that when you swap layouts, there's some default behavior in FMP8 that just automatically scrolls the window so that the current row is at the bottom. I've tried freezing the window at the beginning of the script, but it didn't seem to have any effect. Possible solutions? What if I opened a new window, froze or hid it, and did my work in there and then closed it? Would the original modal window be unchanged? How slow would it be to have the overhead of a new window with every click? How about some extra scripting that figures out which record would need to be active for the scrolling to be positioned properly? Is that possible? The whole thing would be a non-issue (I think) if I weren't required to switch layouts in the script, but I don't really think there's any other way of doing what I'm doing. So... Advice, anyone? Oops! Forgot to set up a guest user. Just a sec. Software.fp7.zip

For the sake of completeness, I'm posting my solution after talking to FMP tech support. We discovered that it had to do with files being accessed via the initial layout. All files that had TOs that were "touched" in the relationship graph by the original layout were opened when the DB was opened on the server, and therefore finds of those data worked. Finds in other files did not. The solution was to add a small login script that opened("hidden") and then closed any files that were not already accessed implicitly via the relationship graph from the initial layout.

I've been stymied on this one for a while, and it all seems to have to do with limitations in FMP 8. Though perhaps if I got Advanced, there might be some functionality that I don't know about... If so, please let me know. Anyway, I want to fully automate my backup and restore. Let me explain... I am developing a complex database consisting of multiple files, each containing tens of tables and many layouts. It is hosted on a server and used daily. I want to be able to add features and make bug fixes without disrupting the use of the database, so I make a copy of the files on my own machine and make changes as needed. When it comes time to sync the new database with the current data on the server, I need a way to do this efficiently and quickly with as little error proneness as possible. What I *really* want (and hope it's possible to do) is have a single script that is generic enough to import into all my files, which will import all data from the current files on the server (after I've taken them offline and downloaded them to my machine, of course) into the updated & fixed files on my machine. I've run into two problems when trying to do this. 1: The Import script step isn't fully automatic; i.e. AFAIK, you can't write a simple loop to go through all tables and import the data, because you have no way of programmatically specifying which layout to import them *to*. As soon as you specify that in the script step via point-and-click, you're tying yourself to importing a particular TO. Hence, no generic code. I hate the idea of having to maintain a script per file that manually does the import (i.e. no loop, just a series of Import steps, one for each table) because it's one more thing that I'm liable to forget when I add a new table or a new field. 2: There seems to be no way to transfer auto-enter serial numbers from the server file, whose serials have most likely been incremented past the point of the ones in the file that's on my machine. Once again, I can implement a specific script to do so, but then I have yet another thing that I have to keep track of when I add new auto-enter serial fields. I want a *generic* script, and I came really close to getting what I wanted before I realized that the Set Next Serial Value script step is tied to a specific layout. Why, oh why can't it accept an expression as an argument, so I could programmatically specify which field I wanted? -( Regarding #2, I've played around (mentally) with the idea of having a separate table just to store all auto-enter serials, because then that table could just stay on the server. But (and correct me if I'm wrong) I believe that could lead to all sorts of atomicity problems when it comes to many users creating new records at the same time, since I would be relying on scripting to grab serial numbers for my new records. Am I SOL? Why does FMP make it so hard to import and export? I'm certain I'm not the first person that's had to deal with this problem, so... What solutions have people come across? I'm open to plug ins and anything else that will make my life easier.

When you create the PDF you are specifying it to print to a file somewhere. That location, such as relative to the database file, will only be available on the host machine (there is no database file on the client machine). Printing happens from/on the client machine. It's actually a bit more than that. When I tried to do the same thing using relative paths while accessing the file from a server, the PDF actually was created, on my local machine, in the same directory as the application itself (in my case, /Applications/Filemaker Pro 8). However, when the email script step used the exact same relative path, it couldn't find the file... I have no idea where it would have been looking. This is frustrating, because the absolute path solution *sucks* on the Mac! Whoever thought that including the volume name was a good idea?!! That means that any "smart alecks" that have the temerity to customize their hard drive names (if you know Mac users, you'll realize that it's more common than you might think) are unable to use the script. I simply can't believe that they didn't include the capability to provide unix-style paths. If they had actually implemented the file: URL properly instead of creating a look-alike, this wouldn't be an issue: just specify file:///Users/Shared/ and call it a day. So, basically, I'm not guaranteed that any path exists on all my machines because I know that some of my users have renamed their hard drives. Am I relegated to an AppleScript solution? Anybody know why the "save to PDF" script step and the "send email" script step seem to have two different definitions of the current directory?

Excellent idea, queue. That worked like a charm for almost everything. I'm still having trouble, though, when I try to search for certain values. I'm trying to figure out why. Searching for people works fine, but when I enter a location (e.g. Lab C), it works on my local machine but not on the server. I'm verifying with our server admin that it's not using a separate machine to store the files.

Thanks for the advice, but I already went down that path during my development. That's the situation I described in my "toughest part" paragraph. I need the extra TO of ComputerCurrentAssignment so that I can use the global to do the find from assigned name back to the computer table and get the correct information. The self-join lets me eliminate spurious records that are found when searching for a particular name. To see what I'm talking about, switch the "currently assigned to:" field in Computer Detail so that it draws from ComputerCurrentAssignment 2 as you suggested. Now go into find mode and search for "oubs". Why did it pull up Mary Buczek? Go to the All Assignments layout and you'll find the answer. Now do a find for "oubs" in the AssignedToName field. You'll see three results. Lance has had three computers. Your find pulled the first available record into the results. But it's obvious from the table that Lance no longer has that computer (computer1341)--he gave it up on 8/5/05. The current owner, if you now do a find on that computer ID, is Mary Buczek. That's how she ended up in your results. If you switch back to my system (using ComputerCurrentAssignment::AssignedToName), you can do a search for "oubs" and it will pull up the correct record. By adding the second TO, I can require that only records where the end date is 12/31/4000 will match a find from the "currently assigned to:" field. No second TO, no capability to find current owners by name. (Took me a long time, as I mentioned, to figure this one out. FM can be an odd beast sometimes.)

The problem in a nutshell: I can do all the finds that I want in my database when I open it locally in my FM7 Client (updated to v3, on OS X Tiger), but when I open it via FMNet on a Windows-based FM7 Server, some of the finds that worked on the client give me "no records match that set of requests." Two paragraphs of background before I describe the problem in detail. I've been developing a moderately ambitious database project to keep track of computers, who they're assigned to, the issues with them, etc. I wanted a way to keep track of the history of assignments of a computer (e.g. computer X was assigned to person A from 1/1/2000 to 8/1/2000, then then to person B from 8/1/2000 to 12/10/2002, and then to person C from 12/10/2002 until the present) and worked that out fine. Then I wanted to be able to browse through the computers and see the most current owner, which I also was able to do using a global field and joins that used it (it's named gSentinelDate, if you're digging into the structure). The toughest part was getting Find mode to work properly. If you open the Computer file I attached, you can search in the Assignment box for a particular person, location, or group that the computer is assigned to, and it will automatically pull up the computer based on the most current assignment data. (I did this by adding an extra TO of the assignment table -- you'll see ComputerCurrentAssignment and ComputerCurrentAssignment 2 in the relationships graph -- and putting in the same global field to "point backwards" towards the computer table.) Now the problem. When these files are shared on the FMP7S, I can still do finds on computer data such as the barcode, serial number, and other items in the blue box on the left (in the first layout, Computer Detail) and get the appropriate records. But when I try to put criteria in the field labeled "currently assigned to:", it returns no records. Again, works fine on the local machine, doesn't work on the server. What's your opinion? Can you reproduce the behavior? Can you get it to work on your server? Is this a bug? For now I'm implementing a scripted workaround, but it's not nearly as flexible as being able to use actual find requests. I've attached the files in question (you can get full access as Guest), though I have not taken the time to prune the database down to a minimal reproduction of the behavior. test_case.zip

Maybe I haven't made myself clear. I agree that you can use GTRR and a proper relationship between TOs to do a static find. I also agree that a solution involving scripted finds works, and that it's more flexible. Here's the scenario I'm envisioning: 1. User is viewing the "all employees" layout. 2. User enters Find mode (from the menu) and types "Smith" in the Last Name field, performs find and sees all current and former employees named Smith. 3. User switches to the "current employees" layout. (If we used a scripted button with GTRR here we'd get the proper set of people. If we just used the layout menu we'd get whatever found set was last in that layout.) 4. User enters Find mode and types "Smith" in the Last Name field. (Again, not using a scripted find here, just straight from the menu.) What happens when the user performs this find? Is there any way to prevent non-current employees from showing up in the results besides disallowing menu access and using scripted finds? In essence, I want to filter *by default* all non-current employees, because rarely is a user of the database going to want to look for them. He should not have to remember to add anything to his find requests to make sure that they are eliminated. As far as I can tell, this is only possible by using scripted finds. Basically I'm asking whether I'll have to restrict access to the layout menu and other menus and go to a button-driven interface rather than leveraging the built-in functions of the FM application.

Thanks, Ender, for your additional comments. Scripted finds make good sense, and I and can definitely do it without too much trouble, but I just want to make sure... Is there no way to do it completely automatically, solely through the proper relationship graph, without having to rely on scripts and other tweaks?

This sounds like a good start for a solution... Thanks! I still have some questions though. What if you want to do a find from the current employees view? Will it not be the case that a find will pull in employees regardless of their status? (For example, if I search for LastName = Smith, wouldn't it find all Smiths, including those who no longer were employed?)

OK... I have an employee table that stores first and last names, hire date, and termination date. It's good for keeping a historical record of all employees, past and present. What I want to be able to do is filter by whether the employee is current. More often than not, a user of this database will want to only view the current employees. I thought I could do this using a self-join, so I set up two additional fields: CurrentlyEmployed(Calculation) = if(isempty(TerminationDate); "Yes"; "No") CurrentlyEmployedMatch(Text; Global) = "Yes" Now I created a second TO of Employee and joined using the criterion CurrentlyEmployedMatch = CurrentlyEmployed. (I may also be missing an ID field and an ID=ID match here, but that's beside the point for this discussion.) This didn't work as I expected. How can I go about filtering out employees that are no longer current? To me it seems wrong to put them in a separate table, for many reasons (the main one being that I lose the ability to easily see information from related tables). My attached file contains this table, plus a few other experiments in the same vein. testing.fp7.zip

S