jrb12

cb .... Unfortunately, I hope you are either still experiencing the problem (misery, company) or have a known resolution to the slowdown. I have the same symptoms with FMP7v3 on a G4 with the latest 10.4 updates. FM is hosting databases for network and iwp access. But with no one else online and little activity on the G4, FM occassionally slows to a crawl doing the simplest task. There is plenty of CPU and disk available and the task is one that screams by at other times. When a request is made to FM for a next record on a simple layout, for instance, the cpu and disk activity bumps briefly then settles back near zero. Meanwhile the wheel keeps going round and round for another 20 seconds before the result is displayed. Interestingly, when a PC user connects remotely and does the same request, it is returned immediately and the effect on cpu and disk on the G4 is the same. So something seems to be occurring at the presentation end on Mac. Other Mac apps run as quick as ever at this time. Very puzzling. Also, when this situation does occur, the only resolution is to restart FMP7. Else it eventually just stops responding altogether. Any assistance or commisseration would be greatly appreciated.

Comment, Your point is well taken. It is important to give the user a method of updating the constants in the calculation. My intent, I suppose, was to give Steve an example of the calculation he requested. With his advanced skill level I assumed he would take it at face value and implement it where he saw fit. Having said that, the calculation does rely on embedded constants. Something that, as you point out, is not accessible to the end user. For me, the number of times the user needs to access and change these constants does not warrant the overhead of the incessant table accesses to retrieve them. This has been a major area of inefficiency in systems I've worked on over the years. There could be a method of satisfying both objectives in Filemaker and that's the interesting problem that Steve presents. The reason why I responded on this thread. This is the Brain Food forum after all, not Define Fields. Its all academic for Steve's app as he only needs the result of the calculation at the time the record is stored. If this calc were to run several million times per day, what's the best way to satisfy both objectives in Filemaker? Cheers, John.

Steve, Because the calculations in this problem will be performed over and over again during the life of the app, its worthwhile, I think, to add extra effort in the setup of the solution in order to save in processing during the execution of it. Not that Comment's PriceList table would necessarily make a noticable difference in response given the load you may have. Still performance may be an issue and this is just an alternative way of doing this. One that does not require a dedicated table or any relationships. My first thought in reading your post was to use arrays to map the input (pieces) to the calculation parameters (BasePrice and CostPer1kStitches). What to use for the mapping function really determines how efficient the lookup is going to be. I thought that since your cutoff points for the Pieces are divisible by 12, the BasePrice and CostPer1kStitches fields could be defined as repeating fields with the number of repetitions set at 48 (12*48=576). Preload these fields with the corresponding value for the given number of Pieces. For instance Base Price would hold [5.50,3.45,2.90,2.90,2.10,2.10,1.90,...]. Most of the fields would hold 1.50, in fact the last 36 repetitions would. Then use the integer value of Pieces divided by 12 as the index. Then it seemed a waste to use fields at all. You don't really need the repeating fields, Something like this in a calculating field would work: Let( BasePrice = Choose( Int(Pieces/12); 5.50; 3.45; 2.90; 2.90; 2.10; 2.10; 1.90; ... ); ... Ok, forget all that, use a Case function with a series of progressive tests like so: Let([ BasePrice = Case( Pieces<12; 5.50; Pieces<24; 3.45; Pieces<48; 2.90; ...; 1.50 ); CostPer1kStitches = Case( Pieces<12; .55; Pieces<24; .35; Pieces<48; .30; .27 ); kStitchesCount = If(NoOfStitches>3000; Int((NoOfStitches-2001)/1000); 0)]; BasePrice + kStitchesCount * CostPer1kStitches) All this considering fields called Pieces and NoOfStitches as input to the calculation. It may seem bizarre to setup something that so many redundant memory cells. And probably not appropriate here but its likely pretty quick. Cheers, John ... whose remember those long nights tuning a Fortran nuclear reactor simulator.

The Get(RecordNumber) function returns the current record number in the current found set. The operative word here is current. There is only ever one of those, regardless of how many times you call the function. Likely all four of your calc fields will change to the same value as you scroll through the found set. Are you trying to add a sequence number to records in the found set? Cheers, John.

Fenton, Filemaker files need to be protected from being opened by two copies of FM at a time. That's the impetus for this rule of thumb. In the black and white world of OSX file sharing, it is likely that the safe bet is to not file share period. But Unix is capable of presenting a richer sharing environment than is made available with desktop OSX. The HornWare SharePoints preference panel opens this up for the user. Take a look at it, it could save you the cost of another machine or a server license. As I said, this is not new to Windows users. I think Filemaker discourages file sharing on the hosting machine simply because vanilla OSX does not have in place controls to limit access to the Mac by users who have accounts on the machine. If the files are resident in one of those user's file areas (desktop for example) then they are ripe for opening through the network. Then Filemaker has to explain to the poor sot why his files are all higgeldy-piggeldy. BTW, I see Zax aleady has server. Cheers, John.

You could put the wires back in to see if that is the case but I suspect it came about due to a change to the hosting PC. (I am assuming it is also wireless BTW, if not just ignore this. If it is wireless, Why?) Either a hardware failure caused by the upgrade or settings that were changed in the CMOS or a conflict induced by the wireless card. Do you experience the same issue when FM is hosted on a different PC? If it is wireless network related, it maybe due to dropped packets and retries though how that translates into a what seems like timimg or caching error on the drive, escapes me. Turn the wireless down to plain vanilla 10Mbps and try it. Maybe 54Mbps is pushing it for your environment. The fact you receive this error on the clients seems to indicate the client wireless card could be initiating a conflict with something that then causes this error. Is there anything in the event log? If nothing else, all I can advise from this distance is gradually eliminate the changes you've made starting at the hosting PC until things start to work. Cheers, John. PS. Maybe you're right and FM4 doesn't work with wireless networks. A case of planned obsolesence? But how they knew that at the time is beyond me.

There is a post at Technet on this here: http://support.microsoft.com/?kbid=330174 Service pack 2 has a fix for something causing this. The CMOS settings on your UDMA controller also could be an issue it seems, if you've changed them to speed up disk access or enabled write caching. Other comments seem to blamed it on a faulty connection to the physical drive, maybe caused when the wireless card was installed. Or the RAID setup was an issue in another topic. Do a Google on "WINDOWS- delayed write failed" to come up with a number of sites discussing the issue. Seems like a hardware or firmware setup anyway. Cheers, John.

Zax, There are many posts on these forums covering this issue. Search around for further explanations of what to do in this case. I have gone through this for a mixed site with Macs and PCs with the host being on a Mac. The basic point is that a file reference is stored in the container field in Filemaker. This reference defines the location of the file relative to the user account and computer that was used to upload the file. In your case the file reference on the hosting Mac points somewhere different (if it exists) when used on the client Mac. You can view the file reference path for a container field by using the GetAsText(<container field>) function. It will return a string looking like this when a Mac is used for the upload: file:NameOfYourFile filemac:/drivename/foldername/.../NameOfYourFile The filemac:/ line is the full relative path to your file. The key to making this relative path work on a network is to make it absolute over your network. To do this without scripts or plugins means always uploading files that already reside on a server. And that have the same server share name on all computers. That way the filemac:/drivename portion of the path will actually be the share name on the server. There are several issues involved in this and bits of software about to assist in implementing it. One issue is, it may preclude using the filemaker host Mac as a client as I believe Macs don't like you accessing the local drive through the network share (not entirely sure about this). But you could make the sharename the same as the host computers drivename to get around it, maybe. Bit of a fudge. To assist in setting up shares on a regular Mac you can use HornWare's SharePoints, http://www.hornware.com/sharepoints/. An invaluable piece of software if you are from the Windows world and used to being able to share anything at anytime. If you do use scripts to upload and open the files, an applescript can make sure a particular server share is loaded first. An alternate method is to use a separate path text field or fields to hold the location of the file. This can be created from the uploaded path given what you know of the source of the upload request. Then when the file is opened, build an appropriate path for that user and computer and open it with an applescript. The key is to make sure the path you store is usable by all Macs on the network. This was the method I used for the mixed network as there were a number of pre-existing network connections that need to be accomodated. Now its a matter of adding the IWP clients to the soup. Cheers, John.

Jeff, Here's a few more ideas to speed up the collecting of keys. 1. If every invoice needs a customer, why not sort the invoice table accordingly and use it directly for your report? Or use the Customer table directly if your report needs to begin there. 2. Use the 1 on 1 relation from FM6 to match every record in the Customer table with each one in the table you are using to hold the global. 3. Go to a Customer layout with just the Customer key field on it and do a Copy All Records with no style. Then paste that into your global field. Filemaker can surely do this quicker than your script. Cheers, John.

Philip, I believe you are in need of a subsummary report with a reorder on the summarized field. In this case the average of the Price. I've attached your file with: - a new summary field in the Product Catalog called PriceSummary that averages the Price field - a new layout called Keyword Summary that contains a subsummary part on the Keywords field and displays the new PriceSummary field - when the Product Catalog table is sorted by Keywords, the subsummary part will show the summarized data in Preview mode (look at the part definition for the subsummary part on this layout to see how it works) - a new script called Keyword Summary that takes you to the new layout, sorts the records by the Keywords field, then reorders the result set by PriceSummary, and finally displays the new layout in preview mode. - the reorder is specified in the Sort Records dialog (I think I set it as descending on PriceSummary, I see now you mentioned ascending) There is a section in the help on this, look for: 'Sorting records by subsummary values'. I hope this gets you a little further along. John. WineryAttemp2.zip

Stanley ... thanks for attending to this ... I missed a couple of posts with my last reply. This was working in FM6 fine as there is an option there for including summary fields in the sort. You would add the subsummary sort field then next add the summary field you wanted to sort the report on and it all worked fine. But after going to 7 I can't seem to make it work. John.

Aha ... now we're getting somewhere. In the Sort Records dialog box there is a section at the bottom where you specify a reorder of a sort by one of the summary fields in the table. It is used for summary reports when you want the report grouped by the sort field but sorted by one of the summary fields. In our case we want the report sorted in decreasing order by a summary field that holds a total dollar value. Lookup "Sorting records by subsummary values" in the help files. John .... whose shredded the box thinking about this problem

Stanley, maybe I'm still missing it. The subsummary part just requires a sort field. It makes no mention of the direction or of whether there is a reorder to the sort. I've stripped a layout down to the sort field and one summary field. The summary displays fine when there is no reorder on the sort field but goes blank when there is a reorder on that summary field. If its necessary to specify the reorder in the subsummary part, how is that done? I think that I've followed the help directions correctly for this sort of report. But am always willing to be in error. I generally am not good with the bleeding obvious. John.

Thanks Stanley. We have many fields to sort by and that is why we have so many layouts. Each of these layouts displays the data sorted by a different field. That field is specified in the subsummary part for that layout. The layouts display all summary fields correctly when there is no reorder to the sort. Its only when the reorder is added to the sort that the reordered field goes blank. I hope that is clearer than mud. John.

I have a number of layouts with subsummary and trailing grand summary parts. Each layout has the sort field and a summary field in the subsummary part, then the summary field again in the trailing grand summary part. When the records are sorted by the sort field for the subsummary part, everything displays fine and correct. However when a reorder is added to the sort so that the records are reordered to be decreasing by the summary field, the summary field content goes blank in the subsummary part. It still displays correctly in the trailing grand summary part. It doesn't matter which summary field I put on the layout, the one that is included in the reorder goes blank. I believe it displays as blank because it is not computed. A percent summary field uses one of the summary fields and that goes to zero when the records are reordered by that summary field. Any assistance would be most appreciated. Thanks, John.

I'm about to use the separation method in migrating an FMP6 project to FMP7. Is keeping the _DATA and _BR tables in sync going to be a problem? In the many-to-many example, the _DATA and _BR database table records are added/deleted explicitly in the scripts in the interface database. In production implementations of this model, is this the method used? That the records are added/deleted separately and the operation limited to being performed in one place. Would using the relationship, "allow creation..."/"Delete related..." settings be more effective? Set on the _BR table relationship for the primary link to the _DATA table. Again, though, this requires that all other methods of creating just a _DATA record be prohibited. Not always an easy task, I suspect. Again, I'm not sure this will be a much of problem but ask the question to see what consideration has been given to it. Cheers, John.

Rivet, The reverse of your formula takes a little longer to work out ... eh eh ... no, I just dropped out of the forums for a while, as well. To start y at zero, remove the displacement term to put the min value of the parabola back on the origin: y = ( x ^ 2 ) * a where a is the same as in the previous formula. Cheers, John.

Zahary, I began a reply to this thread a few days ago with a possible solution to your global fields problem. Then got called away and chucked the reply. Since then the thread has meandered a bit. But then your initial message contains a lot of food for thought. I agree with you to a certain extent regarding FileMaker, the fact that a layout is part of a table is a serious limitation. One that I hoped would disappear in 7 when it became known that multi-table databases were possible. Unfortunately, its still the case that layout fields are table fields. That's really the problem, I think. That's why a layout needs to have a table to start from, picking up fields from other tables via relationships off the enclosing table. But conceptually this is likely easier for a novice user to understand. My experience with non-IT users who develop with point and shoot applications is that they perceive their data as being stored in the manner in which they view it. The idea that there is a layer of intelligence between the screen and the data on the disk usually requires some eureka moment that takes awhile to occur. Granted FileMaker has calculation fields that may be considered as an intelligent link between layout and table. But they are embedded in the table, akin to a user defined field type in an SQL based system or a simple stored procedure. In most every other DBMS, the user interface is separate from the data. Usually its SQL that provides the link between the two. And that's where the power that you require really resides. Building your address table is likely a single SQL insert statement with an embedded, perhaps complex, select statement to gather up the data from however many other tables are in play. No need even for a cursor. But, this lack of separation is also what makes FileMaker so interesting, in my opinion. The imaginative workarounds that are needed to do something relatively simple in SQL, make for entertaining coding. Much more fun than COBOL over CODASYL ever was. Getting back to your problem with globals. Will something like this work? Define a relationship between the address table and your input table, that is based on a global in the address table and a unique key in the input table. Define your address table fields with auto-enter lookups into the input table using this relationship. Before storing a new record in the address table, set the global field to the record ID of the corresponding record in the input table. This should copy the field contents across when you add the new address record. I realize you have data coming from several records in the input table into a single record in the address table. Can these be considered different record types? If so, can each record type be imported into a different input table? Then define the lookups from the address table fields to use whichever input table relationship is appropriate and fill the global key fields accordingly. Looking forward to your comments. Cheers, John.

Surfer, To get the number of days dependent on PAID: If(PAID = "No", Get( CurrentDate ) - DATE SUBMITTED, "") For the number of months, subtract the month fields and add 12 times the difference in the years: Month (Get( CurrentDate )) - Month(DATE SUBMITTED) + 12 * (Year( Get( CurrentDate)) - Year(DATE SUBMITTED)) and the number of days left over is: Day(Get( CurrentDate)) - Day(DATE SUBMITTED) Cheers, John.

Anand, When retrieving data from a repeating field, you need to specify the repetition number otherwise you get the data from the first repetition. You may be using a construct that doesn't allow for a repetition number to be specified. Its speculation of course unless you post the field definitions, calculations, scripts and/or relationships. However, you are already familiar with relationships, why the insistence on maintaining the repeating fields? In my experience the effort put into making a quirky repeating field solution work, and stay working, is about the same as reworking it to use another table. I've been through both scenarios this year and wish the former had not happened. The client, though he had insisted on keeping them, does too, now. Both because he can expand the database into new business functions much more easily and because his quarterly reports actually produce correct results. Create an invoice items database. There is a good document on the FileMaker site to help you migrate repeating fields into a separate database. Cheers, John.

Ok, WSH doesn't have access to the FMP type library and in general does not need to have its objects defined like in VBA. Try this trimmed down version of the script: Dim FMApp, FMDocs, FMActiveDoc 'Launch FileMaker Set FMApp = WScript.CreateObject("FMPRO.Application") 'Set the Documents Object Set FMDocs = FMApp.Documents 'Make FileMaker visible FMApp.Visible = True 'Open a file Set myOpenFile = FMDocs.Open("c:FileMakerPart.fp5", "") 'Run a script myOpenFile.DoFMScript("Import_All_Parts") 'Cleanup - close the documents first so the app will close FMDocs.Close Set FMDocs = Nothing Set FMActiveDoc = Nothing Set myOpenFile = Nothing FMApp.Quit Set FMApp = Nothing Cheers, John.

Ted, Hope this doesn't turn out to be good intentioned road kill. I get the feeling your FileMaker app, Part.fp5, is not running on a regular basis. That you are opening it up just to run the script then closing it again. If this is correct, why not set your task in XP to run Part.fp5 itself on a regular basis. Put your script in as the startup script. The script can finish by quitting the application. I haven't actually tried this mind you, but it does seem reasonable. Cheers, John.

Rivet, Change the parabola to: y = ( ( x - n ) ^ 2 ) * a where a is the factor of n^2 that gives the sarting value of y. So for n=10 (10 steps), at x=0 (the starting value of x), set a = 2 to make y = 200. I had assumed, god knows why, that you wanted the y (for x=0) to be the same as x (for y=0). Anyway, adjust the value of a accordingly to give whatever starting value for y you like. Cheers, John.

Rivet, Unfortunately I don't have version 7 and the trial has expired so wasn't able to look at your attachment. But your curve could be generated using a displaced parabola. Consider the equation: y = ( ( x - n ) ^ 2 ) / n Where the ^ symbol represents 'to the power of' and n is the number of steps. So for n=10 the (x,y) pairs are: (0 , 10) , (1 , 8.1) , (2 , 6.4) , ... , (8 , 0.4) , (9 , 0.1) , (10 , 0). Cheers, John.

Fenton, Its a good suggestion using a script to build intermediate data for the report. Build the totals into a separate database, produce the report then throw the data away. By rebuilding the report data each time you give the user more control over the parameters for the report. Also, you don't dump responsibility for producing the data on some other process or worse, a user. Following this idea, I've attached a zip which includes an aggregate report which shows attendance for a class over a given period of time. Ender, this may provide the structure for your %present classroom report. The script for this report first clears accumulator fields in the student record, then transfers to the attendance table to loop through the relevant records accumulating into the student table fields. The report itself is just a list of student records with summary fields added in the footer (though it could be done as a class/student portal). This implementation obviously only tolerates one user at a time. Your second report, Ender, listing a student's attendance history, could be accomplished with the calendar table I mentioned previously. The report needs a record for every date in the requested range. Currently this requirement is satisfied by storing these records for each student or worse, each student/class. I content its only necessary to store these once. The report can be produced in the calendar table with the 'AttendanceDaily' field and relationship copied over from the student table. Though using a global for the StudentID and the date from the calendar record for the relationship to the attendance table. Do you think this would satisfy your attendance history report? Fenton, I'm not too clear on your second paragraph. Consider that the teacher is looking at the layout you present to them. They are not looking at your database field structure. This idea of separating the user interface from the data structure has been around a long time but not, it seems, in the FileMaker world. Likely because a layout field, with the exception of a merge field, is a database field. I think a separate thread discussing ways of using multi-tier methods in FileMaker would be useful. And why they are a benefit, of course. The case of when a student leaves a class in mid term as far as attendance is concerned can be handled by first storing a record somewhere to indicate the event has occurred. Then at data entry time, by checking for a record of withdrawal before deciding whether to display the student in the portal. At report time there is no change to the logic as there would be no further attendance records added after the withdrawal date. Any added prior would be included in reports if they fit into the report's date range. If the student withdraws before the cutoff date for the course, you would delete the entry and all relevant attendance records at withdrawal time and continue on as if he never existed ... and so on ... its a matter of following through all the event scenarios and applying the target site's rules to each. BTW. I like your FM101. Great idea. Cheers, John. StudentAttendance.zip