ctz243

Mike, I could do all this in FM6 and I know exactly how to provide users with messages whenever I want. You don't seem to appreciate that if you do sequential finds manually, or make one 'combined' find, or do a find followed by one or more constrain found set finds, that the FileMaker manuals say that ALL of them do EXACTLY the same thing, ie that they perform AND searches. This is specifically explained for constrains in the help menu. This means that progressively tighter restrictions must result in fewer records, and that if none match the correct output should be zero. In all of these ways of doing things, a 401 error is the outcome of a null found set - this behaviour is exactly the same in FileMaker 7 as it was in FileMaker 6. All three techniques also returned a zero found count - correctly - when a search that should result in a zero found count was done. This is consistent with their descriptions in the manuals. Now, in FileMaker 7, there are no changes to the description of how these steps work in the manual or the help file. All three approaches still return 401 errors the same way. But now, and only in FileMaker 7, the constrain technique - unlike all the others - returns a incorrect non-zero counts when the correct answer is zero. This is *not* likely to be deliberate. If FileMaker had deliberately intended to change the way a Find:Constrain Records script step was working, when moving from FM6 to FM7, they would have explained the difference somewhere. I fail to see how the same feature, that has been around since at least FM5 and worked the same way then, but now is broken, can be anything other than a bug. We'll find out, however, as the bugs are slowly ironed out. There are bugs in sliding and in dragging parts to new layouts. In time they will be found and fixed. Then we know who was right in this discussion....

Hi Shadow.... I figured a way of counting the related 'owner' (parent) fields from the found set ... not elegant though! What I did was add an extra 'counter' field in the 'cars' (child) table that can be set to either 0 or 1 by a script. When invoked, the script does a search, sorts all found records by owner, then loops through all found records. The first time a particular ownerID is encountered, a global stores this, and each time in the loop it is compared to the ID of the next record. Whenever they don't match, the counter is set to 1, for all subsequent records for the same owner, they do match, and it is set to 0. The sum of this counter in the 'child' table in any found set equals the number of 'parent' records with the same ID. Since in my 'real' database a typical search is likely to return only a relatively small number of records from a much larger set, this works out fine, and gives the desired result. I was going to have to use a script anyway, so the brief loop though the found set was not a problem. To be honest, I was really surprised that it wasn't something kind of built in to FileMaker. It seems strange that there is no easy / built-in ability to count the 'parent' records related to a found set of 'child' records. No-one else in this forum seems to know a simple solution either... if you do come up with something easy and neat, I'd be fascinated to hear how this is 'supposed' to be done in FileMaker. There must be an easier way than my solution! thanks for your interest Chris.

look at the sample I sent in a recent post I made about subsummaries. The file is called 111591-SubSummary.zip. It illustrates a problem with counting the classes if they are in a different relationship, but does provide totals for categories and shows how it can be done with related records too. At least you could play around with it. Basically the subsummary function is what you need. If it's in a flat file, make subsummary categories for each break, ie one for financial class, and another by age, either above or below the body part containg put the individual records. Sort the file by category first then by age, and view in preview. Use status fields to count fields, put them anywhere and the totals will be right. Subsummary parts can go above or below the body (leading or trailing subsummaries). There are bugs in FM7 in terms of putting parts on reports, sometimes it's easier to add them from the menu rather than dragging them from the status bar. The body part is (I think) optional, leaving only the counts / totals. Not totally intuitive but can be got right without any scripting Don't forget: sorts must match subsummaries, and be in the same order, and you must look in preview mode. Note also that summary counts/totals only work for the 'base' (child) table, not 'parent' type related tables. Hence my other post. I now think that doing counts for fields in 'parent' tables require a script loop - it basically won't work without some fiddling. Hope this helps - chris.

Bad News! Shadow, your solution always shows the TOTAL count of owners for each insurer, not the count of the found set of owners. If, in the report, I find by name >K, go back to Preview and re-sort, the number of owners still shows all three of them - even though only two are in the list! Is it possible to refine your concept so that it will yield the correct answer after a find? Thanks for further advice / ideas on this. Chris.

Bad News! Shadow, your solution fails if I do a find; the count of Owners always is the TOTAL count of owners, not the count of the found set of owners. Try doing a Find in the report, set the name to >K, then go back to Preview and then re-sort. Notice that the total number of owners shows all of them (3 for that insurer) but there are only actually 2. Is it possible to refine your concept so that it will yield the correct answer after a find? Thanks for further advice / ideas on this. Chris.

Whoa! That was quick! What a great solution! But - and I feel so dumb asking - htf does it work??? Shadow, if it's possible... how does adding that relation do it's magic? I've wasted hours and hours trying to figure out how to do this... Also will it work, in principle, further up a relationship tree? Kudos, kudos! :-)

Hi again! I'm trying hard to get a report to work - but I can't figure out a simple (?) counting problem using subsummary fields. It's hard to explain here but the example file illustrates my problem and I hope someone could look at it and if possible tell me how to make it work! I'll explain the attached file. It opens previewing a report that has an incorrect count for the number of people insured by a particular insurance company. All other counts are correct. It's that one incorrect count I'm trying to fix. Here are the details There are two tables related by OwnerID; one owner can own multiple cars: Owners: OwnerName, Insurer and OwnerID Cars: CarName, CarValue, CarID and OwnerID What I want to know in the report how many Owners are covered by a particualr Insurer, while at the same time providing detail lines of each car they own. I am trying to avoid using a portal so I can better implement page breaking. So I've built a report based on the Cars, sorted first by Owners::Insurer and then by OwnerID, with leading subsummaries (in the same order) above the Body part. It all looks exactly correct; the calculations for the total value and number of cars in each section are correct. But I can't figure how to count the number of people insured by each insurer. The report breaks properly by insurer, but a summary count on OwnerID always returns '1' no matter where I put it or what I do. This is such a basic thing - yet I cannot for the life of me make it work. Is there a trick to doing this? Any takers? My hat off to anyone who can fix it. Thanks immensely! chris. SubSummary.zip

Hi Bruce R You solved this for my little test file, but not for different file of mine which has a more complex layout but the same blank pages problem. No matter what I do, it keeps on spitting out blank pages. So I am for now cramming everything into one page (used to possibly need two). A VERY kludgy workaround. If a proper fix to this comes out from FileMaker inc I'd be relieved to say the least! thanks anyway chris

I did another test - see the attached SMALL file! In this example, with Error Capture on, a scripted find followed by a constrain results in an incorrect (non-zero) found count, where the correct response should be a zero count. Using the 'Perform Find' script step with identical find criteria a correct (zero) found count results. The bug is in the Constrain Find command. It does not work! ct ConstrainProblem2.zip

I'm grateful for your response, but perhaps I didn't make the problem clear enough. First, 'constrain' is supposed to perform a logical 'AND'. What good is a constraint that *should* return a found count of zero if instead it returns hundreds of records! In the old way, it was still possible to trap the error because 401's were reported whenever the found count was zero. So you could do whatever you liked. But now, one 401 error is reported, but the found count immediately becomes positive, so checking for a found count of zero will fail. So instead of simple code like this: find constrain constrain if (found count is zero) ---tell the user no records were found else ---do something else, ie print the outstanding accounts for this client end the new way has to be find if error is not 401 constrain if error is not 401 constrain end if end if if (error is 401) tell user none were found (found count will NOT be correct) else some were found (foundcount will be correct) end if As you can see, the new code requirements are much more tedious! As regards your question, what good is a found count of zero... imagine you want a button to print all unpaid accounts for a particular date range, and you want a simple means to constrain the output to either all clients or just some particular clients, either to use different stationery or for some other reason. It would be a simple matter to constrain the original find by client. If the found count was zero, no accounts need printing. It doesn't matter to the user if there are none because there are none outstanding, or what the reason is. They are happy to know that the end result of the search was a count of zero. There is nothing wrong with that. But in Filemaker 7, the logical flaw is real. The manual says 'constrain' does a logical 'AND'. Zero records is a perfectly valid outcome for an AND search! In fact, FileMaker 7 only does an AND search provided that the result is non-zero; if the result is zero, it does an OR search and returns a positive number. Anyone who understands set theory knows this is an error. Imagine I have 1000 people, and 100 have blue eyes, and 50 are old. I want to find how many old ones have blue eyes. If I went, find blue eyes, constrain to old, I'd expect the found count to be 50. Let's say only one of the 100 with blue eyes were old. Then I'd expect a found count of 1. This is true both in FileMaker 6 and FM7. But, let's say NONE of the 100 with blue eyes were old. Then I'd expect, and set theory as well as logical AND searches require, that the output would be ZERO records found. This is what happens in Filemaker 6. In FileMaker 7, the output is, wait for it, 100 records! Go figure. Sure, if you trap every find with a 401 then you'll be OK; you could do this before also if you wanted. But the underlying logic is faulty, the found count is simply incorrect, and code that used to work properly and logically now fails. This is a really fundamental error and I am sure it is a bug rather than a deliberate action. chris.

Sorry, sent the wrong file. This is the right one! my apologies. BlankPages.zip

Aha! Found how to fix it! In my case, the portal was displaying invoice lines. The relationship between the invoice and the invoice lines permitted entry of invoice lines (obviously). This relationship was also used to populate the portals for the printing layout. If I made another relationship, based on exactly the same linking strategy, but not permitting entry of invoice lines, and used this exclusively on the layouts to print from - voila! The extra (empty) portal row disappears. I assume that this is a bug because FM6 didn't do it. It was smart enough to know that the 'entry' row at the bottom of a portal should not be printed. Unfortunately this is not the case in FM7 - although the workaround above solves the problem nicely! thanks for those who've thought about this...

Sorry the previous file may have been locked. This one opens with guest having full access. It is an 8k zip archive. WIth a few button clicks you can see the (incorrect?) effect of serial constrains after a find. I'd be grateful for feedback on this. Chris. ConstrainProblem.zip

Here is a small 60k file showing the blank pages problem. It opens in preview mode. There is a sliding portal that spans the page border. The sliding works perfectly. But after every two (?) normal pages, a totally blank page appears. If anyone can fix it I'd be grateful. Seems like a bug to me. ConstrainProblem.zip

Two more things: First, the portal is set to sliding so as to not show empty boxes if not all portal rows are occupied. Second, I made a test file with a portal - and it does not show the empty boxes. So I'm doing something to my portal that causes this. The exact same code in FM6 did not do this, and I can't figure why. I definitely don't have a spare blank record to explain it! Very odd. If anyone has the same thing happen to them please let me know what they did to fix it. thanks, chris.