j.s

Dear community Can anybody provide me with some sample project (or even a manual) of how to automate Filemaker from MS Visual C++ or preferably Borland C++-Builder 6.0? I've spent quite a few hours on this now and have been partly successful, i.e. I'm able to launch a specific FM-DB or quit/set visibility of FM application. However, I still couldn't manage to run a script from a DB or do other actions on document level. Any help is very much appreciated! Thanks in advance! My data: Win XP Pro SP3 FM Pro Advanced 11 v3 Borland C++-Builder v6

That's what I would have assumed, too. But it doesn't work. I get a 0 (zero) all the time, using Get(ActiveRepetionNumber). Either way, i doubt that the repeated fields approach would solve my problem of retrieving invalid search results..

Thank you, Barbara. Thats an elegant approach that will certainly help me one day. However, i dont think it's suitable for my current work: * My layout is not a print report, but rather a planning tool. Each little square (field) must be clickable and jump to the corresponding job sheet (employee, place, date). I wouldnt know how to get this done with repeated fields. * Lots of conditional formatting is added in the final version, based on other individual joins per square (e.g. public and personal holidays, special events, collisions). I dont think i can get this done with repeated fiels, if not creating many of them ("flag arrays"). Of course, this is not evident from the sample i provided, because i slimmed it down to minimum to avoid distraction and unnecessary sources of error.

I've already replaced the global calcs with normal globals, it didnt work any better.. I also replaced the global calcs with normal unstored calcs based on the navigation global fields --> Negative, i still get records with cNumberOfJobSheets = 0, when searching for cNumberOfJobSheets > 0. Although personally, i dont think the best of global calcs either, i doubt for once, its them who cause the problem. Today, i shared the project (FM Network sharing) and immediately the error occurred. There seems to be a correlation: it works as long as the file is neither hosted nor shared.. Today, i started looking for technically similar constellations in other FM projects i made. Within short time i managed to provoke the same error in other dbs too, when performing finds on related fields based on multi-key relationships that depend on a global field. I therefore conclude that the sample file i provided here is not corrupted, but rather FM is unable to deal with such kind of query. I'd probably bite the bullet and go for alternatives, if not the resulting found sets were mostly correct. I tried to find a statement in the FM documentation or somewhere on the web, but failed. This forum is my last hope.. :-) Thank you for your hints

Thank you for your help, bcooney! Yes, they do. In fact, the critical relationship is a date range join using 2 global calculations (plus other criteria). But these fields are always well-set before entering find mode. The relationship works in browse mode. And even in find mode, i can see that those 2 global calcs contain the desired values. I also converted the global calc fields into normal global fields to see, if it makes a difference. It didn't..

Hi I eventually get a wrong found set when searching records with an unstored calc field > 0. However, the error only occurs, when the database is opened from a remote server. When it runs locally, i have never encountered the problem yet. The search criterions are a bit complex. Besides a few parameters for the base table record, there also is a criterion that claims a calc field to be > 0. This calc field counts the matching records in a related table using Count(related field). The underlying relationship is multi-value, including normal and global fields. The calculation works fine, it never yielded a wrong value in browse mode. Yet, the found set resulting from the search eventually includes results with calc field = 0. This error is not fully reproducable, but if it happens, its always the same few records that are affected. Meanwhile, I spent two days analyzing the error. I extracted the problem from the main software. I recovered the file, had indexes recovered. I redrew the relationship, simplified it, played with field types, redefined the search criteria, deleted and reimported the table data, deleted and reimported the base table, exchanged layouts -- but nothing ever got rid of the error. I slightly start believing that this is a 'FileMaker bug'. I now have tiny demo database ready that produces the error, when it's run on a remote fmserver 11v3 mac and accessed from either fm pro adv 11v3 win or fm pro 11v3 mac. If i launch the demo locally on fm pro adv 11v3, everything works fine. The fmserver machine is well-established. It hosts about a dozen of databases, including big ones, and overall performance is satisfying. I mention this, because the speed of that little demo leaves much to be desired, if hosted by the server. No surprise, there's quite a bunch of joins to be evaluated. About the demo file (attached): Its a monthly overview of work assignments. There is a set of employees (table Employees) and a set of work places (table WorkPlaces). A third table (EmployeesXplaces) defines the potential work places for each employee. A forth table (JobSheets) stores the actual work assignments. With the provided sample data, the error typically occurs, when displaying assigned employees in Jan 2012 (employee nr 7 is listed in work place nr 1, although it is never assigned there during Jan12). Im very glad about any hint. Thank you very much. Jürg from Switzerland monthlyWorkPlanA.zip

Holy cow, Comment, that's most impressive! :-) A lot of fun, indeed! I would have never guessed that the flag that I was going to implement myself in humbling manner does already exist. Moreover, it even seems to be rock-solid (at least I couldn't make your solution misbehave in no way) and requires no maintenance, should ever fields be added to the child table. All the more, I now wonder why portals shouldn't be the best choice for my problem (as you said). In terms of user-friendliness I reckon they are. Thank you, Comment! Thank you, LaRetta! It seems lik you made me save a lot of time and trouble, since this situation occurs regularly in my projects (whenever its about complex user data validation) and, feeling very thankful, I browsed through your profile looking for sth like a 'Donate' button, because the service you provide is on the far side of self-evident. How can I show my gratitude? Jürg

Hi I'd like to have a validation script run, whenever a portal row is commited, or more precisely: whenever a portal row loses focus AND was modified before. I tried Portal::OnObjectSave, whicht unfortunately doesn't fire for known reasons. I tried Portal::OnObjectExit, which fires too often, namely, even if the portal row data was NOT modified. I tried PortalField::OnObjectSave, which fires too early, because validation can only be done on record level, but not on field level. I have in mind to use Portal::OnObjectExit and there decide, whether a record needs validation based on a flag that was either set priorly using PortalField::OnObjectSave or by evaluating the record's modification timestamp in a way that I haven't figured out yet. Either way, I'm afraid that my solution will lack of elegance and robustness. I'm aware that the same question was posted earlier this year, but there was no sufficient answer (in my eyes). Nevertheless, I reckon this problem is standard and me, being a script newbie, just blind to the appropriate solution.. :-) Thanks for your trouble! Jürg from Zurich

You mean on text level using List(), FilterValues(), ValuesCount() & co? --> Wow!! I doubt i would have ever figured that out on my own.. :-) Thank you very much for your advice, your posts read like detective stories :-) Jürg from Zürich

Thanks for your fast and solid reply. Indeed, im still struggling about which way to take: found set or self-join-relationship. I originally started with a sorted found set and two nested GetSummaries(), a somewhat doubled version of your linked post, since i need a Sunday counter for each employee concurrently. However, in order to be useful to the assignment scheduler guy, all these personal counters must be displayed compactly and embedded into the planning tool and I didnt know how to get this done with the GetSummary() version. Thats why I switched over to the self-joins that allow me to display all the personal counters in e.g. an employee list view or portal. The time period constraint is easy to maintain adding it as an additional criterium to the relation from Employees to WorkEntries. It is automatically carried on through the following self-join relationship. Yet, your worries are justifiable: Thinking of e.g. reversal, holiday and absence entries which are also stored in WorkEntries, I may easily get stuck with this approach. Either way, I'm wondering, if there will be rounding errors when summing up over many hundreds or thousands of fractions (cInverseCount)? I admit I was hoping there was a 'nobler' way.. :-) Thanks again. I'm truly amazed by your service. Jürg

Indeed, at the end, i need to know the number of Sundays for a given calendar year (e.g. stored in a global variable). However, i assumed that this constraint will not be crucial for any solution, that's why i decided to omit it here. Was i wrong? Unbelievable: There is a law in Switzerland that claims additional salary payment for Sunday work, unless there were MORE than 6 Sunday assignments per calendar year. In the latter case, the employee will only get normal pay instead. Thanks for your concern, comment.. I remember you helping me out of a former crux (and i still owe you my address, so you can send me a bill.. :-) )

Hi all Given: * Table "Employees" - Field: EmployeeID (unique) etc. * Table "WorkEntries" - Field: EmployeeID - Field: Date - Field: TimeFrom - Field: TimeTo etc Wanted: * Table "Employees" - Field: NrOfSundaysWorked (calculation) Remarks: * There can be more than 1 work entry per employee and date. I guess this makes the problem non-standard. * The wanted information must be available for all employees concurrently (e.g. in list view) and in real-time (i.e. no manual refresh triggering) Example: Employee ID 3 worked as follows: Sun, Nov 6th 2011, 10:00 - 12:00 Sun, Nov 6th 2011, 14:00 - 18:00 Tue, Nov 8th 2011, 14:00 - 18:00 Sun, Nov 13th 2011, 10:00 - 12:00 Sun, Nov 13th 2011, 14:00 - 18:00 Sun, Nov 13th 2011, 20:00 - 22:00 Then Employees::NrOfSundaysWorked for employee ID 3 is supposed to contain value 2, because the employee worked on 2 sundays. More remarks: * I know this problem could be got rid of by optimizing the data structure. However, im curious on a solution without this pain.. :-) * The wanted information must be available for all employees concurrently (e.g. in list view) and in real-time (i.e. no manual refresh triggering) * I found a solution that seems to work for small amounts of data. However, i cant manage to be proud of it and im also afraid that it wont work with growing data. Last but not least, im just very curious to know if there's a smooth way to get it done.. :-) Attachments: * sundays.fp7: provides tables, fields and sample data * mySundays.fp7: provides the solution that i found so far Thank you very much for your time and goodwill! Sincerely, Jürg from Switzerland sundays.zip

Holy cow! That's incredibly creative! Got to show this to my FM-friends! :-) Thank you so much! If you sent me an invoice, i'd pay it.. :-)

Thank you again, Comment! Does that mean that there is no way to automatically load the correct portal values - without using script triggers nor any similar plugins? Since the Articles table contains only a few hundred records and is not growing fast, i thought of a fancy hack, yet got stuck again during its implementation. My idea was the following: 1.) Add a calculation field cbCurrentRecord (boolean) to the Articles table that marks the currently selected article (in the current window). 2.) Then add a relationship from table RentalPeriods to Articles, such that all article records are selected (cartesian). 3.) Sort this relationship by Articles::cbCurrentRecord (descending order), such that it points to the currently selected article (in the current window). 4.) Through this relationship it should be possible to reach the current article's Price field that is necessary to calculate the rental price for each period. PROBLEM: I couldnt find a proper definition for cbCurrentRecord. Again, this calculation field should be '1' for the selected article in the current window, and '0' otherwise. Can this be done without plugins and in a FM<10 compatible manner? Preparations made in attachment rental2.zip. Thank you so much again for your assistance! rental2.zip

Wow, that was quick, thank you very much! Yet, can you think of a solution without script triggers, so its compatible with fm<10? Sorry, i forgot to mention that. Thank you again, i am kind of overwhelmed by your 'speed of help'! PS: One article at a time is fine.