Triggers for database update

[byronic]

It is rather odd to me that you seem to think that you need event triggers based on a field (which is what they are), when everything you've said can and would be done by scripts.

At times a manual transaction may need to be entered - in which case the script which usually runs will not run. To my mind it us much cleaner to have an import script which merely does the import. When a record is added to a table, the work that is required to process the new data should be embedded in that table - not in an external function which posts data to the database. The database should define itself - not be defined by external applications.

You've sort of mixed up the difference between calculations and lookups; the latter being more appropriate to copying data (and only if necessary; most data can be viewed relationally by related tables without needed to be copied).

Consider an order details table. Products and pricing are always changing, but an order which has shipped and been paid for must retain the product description and pricing which applied at the time - and so must be copied into the orders table.

"Not knowing filemaker i am unable to look at my application from a filemaker perspective - which is why I am having problems I think..."

Yes, I think that's the crux of the problem. What you want to do is doable. Not simple, but doable. The first thing you need however, is not really the mechanism to "drive" the process, it is the proper relational structure, tables, fields and relationships, to handle the data. You cannot design actions with data without objects to put the data in.

Yes, I have the structure - it is much the same as the structure in my existing MS SQL application - the tables are largely unchanged. My problem is understanding how FM works.

The process would start with AppleScript run by a Mail Rule. But it would quickly pass the data off to FileMaker field(s), and then call a FileMaker script, via the call I mentioned earlier.

I am resisting embedding too much functionality in scripts. In the current system it is sometimes necessary to perform a manual entry - in which case the script will not be called and the required database updates will not occur. A trigger which runs when a specific field is modified will run whether the modification was made automatically or manually. It is a more robust approach, and one which is more self-documenting.

The Daily and Weekly scripts are different. They could be run/checked during a "on startup" script, if the files are not on FileMaker Server, but really, they should be, in which case they could be scheduled; FileMaker Server 9 supports scheduled scripts, as long as they require no user interaction. (Never done one myself yet though.)

I don't have Filemaker server. But I have found a plug-in which it seems will run my scheduled scripts.

I don't really see how you can say that creating a record before entering its data is "odd." Perhaps you're used to importing data, in which case the record seems to be created simultaneously; but really it's always created first.

One of the plugins provides an "on-record-create" call script function, but runs before the fields are populated, rendering it useless for my purposes. The authors have given me a pointer to a suggested way to do this using access permissions - I haven't checked it out yet.

[byronic]

This looks promising - so when the new record is added to the database, what prompts the parent record to determine the latest record and then begin the required processing? I do not know FM at all well, but I would expect that the parent record would not update just because a new child record was added.

Take a look at the above graph ... the first field in the test table calculates the ID of the latest arrived message over the cartesian relation, which then is turned into a new relational key that gets data into the 3 digest fields over the relation, which all are calcfields as well.

This prevents any kind of scripting required to get the latest arrived record and then turning to another layout where the fields data is manipulated if the structure were flat!

--sd

[byronic]

You are pushing that your way is right and not listening to those who know a heck of a lot more than you about this business. You may be right about what you THINK you need but don't confuse that with what you need to accomplish it. This has nothing to do with intelligence nor education. This has to do with FileMaker only.

I don't agree. I have a business application which is already running in an RDB environment (MS SQL) which I am attempting to move to Apple and Filemaker. There are limitations to FM functionality which I did not expect - now it is about figuring out how to work around them. There have been lots of good suggestions from people who obviously know FM very well - but I do not yet feel I have a solution for my application. It is the key application to my business - hence I want to get it right.

[byronic]

For example, let's have a table of Equipment and a table of Messages. Messages arrive periodically to update the status of some equipment attributes. All that's needed here is to store the arriving messages in the Messages table, with the correct date and EquipmentID. Once that is done, each piece of equipment already "knows" its current status by looking at its related data. Copying the same information into the parent table would serve no purpose whatsoever.

Yes, it "knows", or rather it "will know" when the parent record is next retrieved. But I want the parent record to action the new data as soon as the messages table is updated. An urgent email may be required. What will trigger the calculations in the parent record to analyse this latest data and take the appropriate action?

BTW, it's VERY easy to get data from the last RELATED record. You can either use the Last() function, or you can change the sort order of the relationship so the last record becomes first. This way, any reference to a related field automatically gets the most recent data.

[byronic]

That said, here are some items that I get hung up on reading your posts:

If the question here is a chicken-and-egg thing - "how can I update all my other bits and pieces if my new record is blank?" - then the answer is, you update all your other pieces after you're done populating the new record with data and have committed it. e.g. in pseudo-scriptmakerese

New Record/Request

Set Field[myfield1; "testdata"]

Set Field[myfield2; "moretestdata"]

Commit Record/Request

[...update various other audit tables...]

[...evaluate parent record, other records to see if email needs to be sent, and create email if needed...]

Note that since your New Record FileMaker script will be called from the AppleScript that scrapes the data from Apple Mail, you don't need an event trigger in FileMaker itself - AppleScript will handle the trigger.

The evaluations you need to perform would be handled in the same above script. In other words, anything that would be triggered as a result of a new record being created, however conditional, would be handled in that one FileMaker script called from AppleScript. You'd be using control script steps like If[] ElseIf[] Else[] EndIf[], etc to check your various calculations to see if any conditions return true, and so on.

I prefer to see the coding which gives rise to a field value in the database table itself (in a calculation field if possible). To embed all the complex logic that this application requires in a single script which is also external to the DB seems to me to be very messy.

The script plugin which allows me to call a script when a record is created is perfect - except it doesnt work because, in fact, a null record is created. Thereafter we are no longer talking about a "once ever" execution on record create (which is what I want), but instead an execute on modify, perhaps controlled by flags to prevent subsequent execution. I am looking for something more elegant than this...

As LaRetta says, this is a bit backwards, though I know what you're trying to do. Here's the thing: if your data model is correct, then given a Parent record in a Parent table and a newly created Messages record with an appropriate Parent foreign key, there's nothing to "update". The relationship between the two table occurrences should allow you to grab whatever you need from either context. That's why folks are emphasizing looking at the data model, and thinking about solving your problems through data modelling and calculations.

How to grab the most recent message from the Parent context: Define the relationship between Parent and Messages as sorted, descending, based on creation timestamp. The most recent message will now be the first record retrieved in any reference to the related messages table.

Yes, this has already been suggested, but for another calculation I need the oldest record. Another way of obtaining the last record has also been proposed - however, in all of these solutions, I am still at a loss as to what will prompt the calculation fields in the parent record to spring into action and process the new messages information...

FileMaker's an unusual product in that relationships and calculations provide a lot more power than equivalent functionalities in other database products, and you'll be served well by exploring them extensively. Everything you want to do is eminently possible, and I think what you're experiencing here is some unfamiliarity with the product combined with some nomenclature issues.

I think also that FM is designed for a user sitting in front of a screen retrieving data and pushing buttons. My application has no user. It is all driven electronically - so every required action must be driven from the arrival of new data, or by the elapsing of time.

[Colin Keefe]

I prefer to see the coding which gives rise to a field value in the database table itself (in a calculation field if possible). To embed all the complex logic that this application requires in a single script which is also external to the DB seems to me to be very messy.

The script plugin which allows me to call a script when a record is created is perfect - except it doesnt work because, in fact, a null record is created. Thereafter we are no longer talking about a "once ever" execution on record create (which is what I want), but instead an execute on modify, perhaps controlled by flags to prevent subsequent execution. I am looking for something more elegant than this...

...(snip)

I think also that FM is designed for a user sitting in front of a screen retrieving data and pushing buttons. My application has no user. It is all driven electronically - so every required action must be driven from the arrival of new data, or by the elapsing of time.

I feel like we're all kind of dancing around a bit here trying to figure out why what you're trying to do sounds so complicated, when the actual processes we're talking about are so simple. Completely automated, zero user interaction database-driven tools are built in FileMaker all the time. You're not reinventing the wheel here. Autoenter calcs, lookups, calculation fields ought to handle 90% of what you want, and an event trigger plugin tied to a calc field and a script ought to be able to handle the event-driven stuff you need. That's generally speaking. The problem, again, is that I think you need to learn what these tools actually do, rather than say what FileMaker should do.

This concept of the null record being created by the script plugin, for example...honestly, FileMaker's not going to populate a record with anything you don't tell it to. What, exactly is the issue here? Records only get created once. Record creation scripts only get called when your code explicitly calls them. Script triggers only get triggered according to the logic you specify for them.

Yes, this has already been suggested, but for another calculation I need the oldest record. Another way of obtaining the last record has also been proposed - however, in all of these solutions, I am still at a loss as to what will prompt the calculation fields in the parent record to spring into action and process the new messages information...

Okay, let's start here. Can you please give a very specific real world example of what value would need to be changed in a Parent record based on a new message in the message table, so we can at least give you a specific example of how to implement it? I do feel like obvious solutions are getting lost in translation.

[byronic]

Okay, let's start here. Can you please give a very specific real world example of what value would need to be changed in a Parent record based on a new message in the message table, so we can at least give you a specific example of how to implement it? I do feel like obvious solutions are getting lost in translation.

Ok, cool. See if this helps...

An email arrives, is processed and a new message record is created.

At the equipment level, a check must be run to decide whether to react to this message, or ignore it, depending on the type of message and whether any action has been taken already on a similar message already received. In my scenario, several simple calculation fields on the equipment record set various flags, and a more elaborate calculation field or fields will then decide what to do and execute the appropriate instructions (perhaps a script to send am email to an end user; perhaps a script to log a service call; perhaps a script to order a spare part. Or all of the above.)

If an action is taken, the message record is updated to include the details of the action taken (so the message record becomes the permanent audit trail). If no action is taken then the message record is updated to include the flags or parameters which were set at the equipment level, and an error message (which might just say - duplicate message, ignored). Later, if you are trying to figure out why your system never sent a spare part for a specific piece of equipment, you can follow the decision making process more readily because of the audit trail.

At the equipment level, I would like to reset some fields with the ID of the latest alert, some data from this alert and some statistics. This is not essential, but it makes the "system" more robust and easier to debug since it is self-logging. I could write the details of what transpired to a log file, but I feel that this is more efficient. The message-handling process updates the equipment record, and the calculation fields on the equipment record then update the message record with the action taken. It is probably not necessary to include the details of the latest alert in the equipment table - though it will make exception reporting more efficient perhaps. I am not sure what performance of this application will be like at this point...

Anyway, I need something which will trigger the calculation fields at the related equipment record when a new message arrives. I expected to be able to write the ID of my message record into the related equipment record, thereby triggering the calculation fields in the equipment record.

Different types of equipment may require different decision making algorithms and result in different actions being taken.

In a typical situation, a warning error for a piece of equipment may occur on a daily or even hourly basis. It may be a warning to replace a component with a specific lifecycle, such as an air filter or bulb. The decision to ignore or action the message will depend on whether an earlier warning has resulted in a new part being despatched and whether the current message pertains to this specific incidence of the part failure (and therefore is still occurring because the part has not arrived to be installed) or is the first of a new sequence of messages which denote that a part which was replaced some time ago is now up for replacement again. Because the equipment may be turned on or off the length of time between failures will vary. Also, a warning can be reset and the messages will stop for a short time.

I feel that it is unrealistic to build the complex algorithms necessary to make decisions anywhere else but in the equipment table - here it is possible to build this in a modular fashion and avoid lengthy if-then-else code sections which are hard to follow and debug. When broken down into small steps, it is much easier to manage. If it were all built into a large script (as it is in my current MS SQL based system, it is likely to prove as unreliable and hard to manage as the current system.

[Fenton]

You say that occasionally data will need to be entered manually, so everything has to happen without a script. Well, we often use portals with manual entry, and "look up" data,* as required, though redundant, to child tables. If you're expecting child data to update data in parent tables automatically, it is not going to happen without some help.

Such help could be, if required (and it seldom is), a plug-in which implements field "event triggers" for FileMaker. There are at least a couple free ones. They would run a script upon exiting a field.

If several fields need to be edited before the script runs, then it would be better to flip the user to a layout window where they are trapped within a loop. Then you can just run the script when they click a button to submit the data, so that it only has to run once; no plug-in required, but some careful scripting. It is also vulnerable to Mac's Expose, and maybe Spaces, which allow you to override the window focus. I imagine things could be written so as to guard against that.

*I still say that in almost every case you are saying "calculation" when you mean either auto-enter by (lookup or calculation) (auto-enter by calculation is not the same as calculation; it is much closer to a lookup). This is not just semantics; you will have to throughly know the differences, and the pros and cons of each to move your data safely. The latter two are methods of copying data between tables without a script; a calculation does NOT copy data, it simply references it.

There is another method of copying data, which will push the keys of a relationship into the child table if you [x] Allow creation of related records, effectively linking the new child record.

Your statement that putting the logic in a script which will be external to the database is kind of odd, in reference to FileMaker. Scripts are a part of the structure of the database. They are not "external" to it.

[Fenton]

elaborate calculation field or fields will then decide what to do and execute the appropriate instructions

Calculations do NOT "execute instructions," scripts do. Calculations just calculate, as soon as any fields referenced within them are changed. They do not, cannot take an action. But this is not a problem. This whole process is entirely scripted. There is no reason for it to stop.*

The results of the calculations, in whatever table are available to the script. FileMaker handles all timing issues flawlessly. Tests within the script will decide what to do, where to go next. Whether it's just one script or a master script calling smaller scripts is up to you. One often breaks parts of a very long script into smaller modular subscripts, for readability more than anything else; it still works the same.

As far as logic goes, yeah, it sounds pretty complex. You can put as much of it as you can into calculations. FileMaker has fairly robust calculation operators, such as the Case() function. You could even break the really long calculations into a few smaller ones; though some small amount of speed is lost; but so what, better than going nuts :-]

*From what you're saying, this is too complex to handle manual entry without a script. You're just going to have to accept that and build an interface to ensure the script runs. It is not that hard to do, comparatively.

Edited February 25, 2008 by Guest

[comment]

I would advise you to get a lot more specific than "several simple calculation fields on the equipment record set various flags, and a more elaborate calculation field or fields will then decide what to do" and such. Otherwise this will continue to be a purely theoretical discussion that leads largely nowhere.

You have some very firm ideas about how this should work - well, as you have been already told, it either won't work that way, or it won't work optimally.

There are limitations to FM functionality which I did not expect - now it is about figuring out how to work around them.

Filemaker is not without limitations - but the things discussed here are not among them. What you really need to figure out is how to work with the best Filemaker has to offer - not around it. This takes some time, especially when it requires a change in perspective, as LaRetta so aptly put it.

For example:

I want the parent record to action the new data as soon as the messages table is updated. An urgent email may be required. What will trigger the calculations in the parent record to analyse this latest data and take the appropriate action?

Nothing, if you ask me. Because the action is already taking place in the messages tables. And the newly created message is certainly capable of handling all the action needed - including getting data from the parent record (or from a previous message record, if so required). And since the script that created this message is still running, it can continue to evaluate the situation and call any further action as required. Because, as YOU so aptly put it, "the work that is required to process the new data should be embedded in that table".

[byronic]

You say that occasionally data will need to be entered manually, so everything has to happen without a script. Well, we often use portals with manual entry, and "look up" data,* as required, though redundant, to child tables. If you're expecting child data to update data in parent tables automatically, it is not going to happen without some help.

Such help could be, if required (and it seldom is), a plug-in which implements field "event triggers" for FileMaker. There are at least a couple free ones. They would run a script upon exiting a field.

If several fields need to be edited before the script runs, then it would be better to flip the user to a layout window where they are trapped within a loop. Then you can just run the script when they click a button to submit the data, so that it only has to run once; no plug-in required, but some careful scripting. It is also vulnerable to Mac's Expose, and maybe Spaces, which allow you to override the window focus. I imagine things could be written so as to guard against that.

There is no user. Everything happens automatically. There is no user, no display, no buttons to push, no fields to exit...

*I still say that in almost every case you are saying "calculation" when you mean either auto-enter by (lookup or calculation) (auto-enter by calculation is not the same as calculation; it is much closer to a lookup). This is not just semantics; you will have to throughly know the differences, and the pros and cons of each to move your data safely. The latter two are methods of copying data between tables without a script; a calculation does NOT copy data, it simply references it.

I use a calculation field to define the value in a child field via the lookup function. What do you call this - a calculation or a lookup?

There is another method of copying data, which will push the keys of a relationship into the child table if you [x] Allow creation of related records, effectively linking the new child record.

It is not the keys that I want but the data which may change in the future, such as the price of a product. It is essential that the values which existed "on the day" are preserved in the child record, which acts as an audit trail.

Your statement that putting the logic in a script which will be external to the database is kind of odd, in reference to FileMaker. Scripts are a part of the structure of the database. They are not "external" to it.

By that I mean that anyone looking at the database definition should see everything that governs the function of the database. If you separate the logic into external scripts then you make the database more obtuse. A calculation field, defined in the database, makes it clear to anyone who looks at the database what it does. If it is in an external script you have to know the script exists, and when it runs, and if you have the latest version before you can know the same thing.

[byronic]

Calculations do NOT "execute instructions," scripts do. Calculations just calculate, as soon as any fields referenced within them are changed. They do not, cannot take an action. But this is not a problem. This whole process is entirely scripted. There is no reason for it to stop.

There are 16 logical functions in the calculation fields, including IF tests. These can be used to execute instructions to change the value of other fields (set flags, calculate results) and, with the appropriate plugin, execute a script.

The results of the calculations, in whatever table are available to the script. FileMaker handles all timing issues flawlessly. Tests within the script will decide what to do, where to go next. Whether it's just one script or a master script calling smaller scripts is up to you. One often breaks parts of a very long script into smaller modular subscripts, for readability more than anything else; it still works the same.

As far as logic goes, yeah, it sounds pretty complex. You can put as much of it as you can into calculations. FileMaker has fairly robust calculation operators, such as the Case() function. You could even break the really long calculations into a few smaller ones; though some small amount of speed is lost; but so what, better than going nuts :-)

*From what you're saying, this is too complex to handle manual entry without a script. You're just going to have to accept that and build an interface to ensure the script runs. It is not that hard to do, comparatively.

Well, i don't think it ought to be too complex to do this in a modern database product - even one which controls access to the database through a user interface which seems to be optimised for the presentation layer. But I do not want a spaghetti of code in a script - I could have done this in MySQL...

I think I am close to a solution - I can set a field in a parent table via a script, and this will, hopefully, allow my calculation fields in the parent table to do their stuff every time a new child record is added. It looks like it will work - but it sounds like I am bending filemaker to achieve it. So either I bought the wrong product, or else i still don't understand how to use FM to implement an elegant solution to my problem.

And PLEASE PLEASE PLEASE dont talk to me about users and data entry. I am getting the message that this is what FM is all about - but my application does not have a user, except for minor admin functions including the occasional need to manually add an error message to provoke the system to respond.

[byronic]

I would advise you to get a lot more specific than "several simple calculation fields on the equipment record set various flags, and a more elaborate calculation field or fields will then decide what to do" and such. Otherwise this will continue to be a purely theoretical discussion that leads largely nowhere.

Say there are 27 different types of equipment being monitored, as of now, each of which has different things being monitored and which require different actions in response to a message being received. So, a router which issues an alert on something like a buffer overflow might require a review, or may not if the problem is understood and it is known that it requires no action. A printer which sends an alert because the paper is out in tray 1 may require no response, or it may require an email be sent to an operator who will refill the tray (it happens - esp in large legal firms). An alert from a security alarm may require some other kind of response.

In each of these cases I want to define the response in the equipment table, for each piece of equipment. As I add a new type of equipment I can then define the new response within the equipment record (or even by using a lookup to a device type table if I can re-use some existing code) without disturbing any other aspect of the system.

To try and drive this from the message table, by embedding all the logic in a script, makes this system hard to "read" and difficulty to maintain. It also sounds very clumsy.

You have some very firm ideas about how this should work - well, as you have been already told, it either won't work that way, or it won't work optimally.

I think it is becoming apparent that the real issue here is that FM has no triggers. There are those in the FM community who think this is no big deal, and others who think it is. I like triggers - and I was expecting them to be in the product. A trigger is a very neat way to build something which is event-driven. And that is what this application is - and event-driven application. Scripts can do the same thing but not so elegantly.

What you really need to figure out is how to work with the best Filemaker has to offer - not around it. This takes some time, especially when it requires a change in perspective, as LaRetta so aptly put it.

I am resisting being pushed down a path of a mega script which controls everything - that sounds like the application I already have.

Nothing, if you ask me. Because the action is already taking place in the messages tables. And the newly created message is certainly capable of handling all the action needed - including getting data from the parent record (or from a previous message record, if so required). And since the script that created this message is still running, it can continue to evaluate the situation and call any further action as required. Because, as YOU so aptly put it, "the work that is required to process the new data should be embedded in that table".

It will be a very large script, hard to maintain and very inelegant. It takes all the work out of the database and pushes it back into code. I want to go in the opposite direction - which is what I thought modern RDB's were supposed to be all about.

[Søren Dyhr]

[color:red]MAC ONLY - MAC ONLY - MAC ONLY - MAC ONLY - MAC ONLY

I do not know FM at all well, but I would expect that the parent record would not update just because a new child record was added.

Good catch! Everything is pulled by the layout event there are two solutions to the problem either show the calc'field in the child-tables layout stashed away in a corner where text-color matches the environment, or even better do it via a self-join ... provided we just have the parent record for processing purposes only, are the cartesian relation actually made redudant...

There are still issues, but as such will the calc react on a new record is created via applescript, but it hangs if a record is omitted from the found set. Take a look at the template it consists of two layouts belonging to each it's own table, but the viewer layout is related to the other table via a cartesian looking only at the established values of the GetNthRecord( without the relational discourse .... the refreshing issues still prevail!

But as such could the applescriped creation based on the mail-rule actually handle it reliable, as long as we don't strain the found set in any way!

I apologize the messy template!

--sd

Untitled.zip

[Søren Dyhr]

It have suddenly occured to me why you seems to make french fries with a tennis racket metaphoricly speaking, you wish to implement the transaction model, while we when it comes to stock levels use recursive structures:

http://jonathanstark.com/recursive_data_structures.php

But then use a event trigger plugin for your task, zippscript comes to mind. But I would think you probably were better off with use of the MBS plugin's ability to direct SQL commands to filemaker it self simply by stuffing the calls in either the field validation or some adjacent autoenter fields - checkout:

http://mbs.geistinteractive.com/node/7

--sd

[byronic]

[color:red]MAC ONLY - MAC ONLY - MAC ONLY - MAC ONLY - MAC ONLY

Good catch! Everything is pulled by the layout event there are two solutions to the problem either show the calc'field in the child-tables layout stashed away in a corner where text-color matches the environment

There is no display. No user. No interactive processing. Everything is driven by the arrival of the email message, and everything after that has to happen without user interaction.

...or even better do it via a self-join ... provided we just have the parent record for processing purposes only, are the cartesian relation actually made redudant...

I cannot see how a self-join is useful here.

Take a look at the template it consists of two layouts belonging to each it's own table, but the viewer layout is related to the other table via a cartesian looking only at the established values of the GetNthRecord( without the relational discourse .... the refreshing issues still prevail!

I had a look at your example but I am afraid that I cannot see how it applies to my application - and I am not sure what it was supposed to do. At any rate it did not do what I expected...

But as such could the applescriped creation based on the mail-rule actually handle it reliable, as long as we don't strain the found set in any way!

I very much appreciate the effort - but I am not sure what you are getting at here. If I have received 26 messages in the last 3 hours from a router with a problem, I need to decide what to do about all 26 messages based on the information which is stored in the equipment table for the specific router, or even in a parent record for routers in general. In my database the router "knows" what it is supposed to do when a message arrives. It "knows" how to respond - this "knowledge" is embedded in the calculation fields in the router equipment record.

I just need to tell the equipment record that a new message for it has arrived and it should therefore do something...

The same is true for every piece of equipment - all of which will "know" what to do when a message arrives, because the equipment record contains that intelligence.

[byronic]

It have suddenly occured to me why you seems to make french fries with a tennis racket metaphoricly speaking, you wish to implement the transaction model, while we when it comes to stock levels use recursive structures:

http://jonathanstark.com/recursive_data_structures.php

I am familiar with this technique - I started life as an analyst for order entry/inventory systems and my first system design was for IBM S/38. This is commonly used for products which are related - such as an iPod, and its case, and its strap, and its warranty, etc. But I have no such application here that I can see...

But then use a event trigger plugin for your task, zippscript comes to mind. But I would think you probably were better off with use of the MBS plugin's ability to direct SQL commands to filemaker it self simply by stuffing the calls in either the field validation or some adjacent autoenter fields - checkout:

http://mbs.geistinteractive.com/node/7

Thanks - this looks very powerful. There are several scripting tools - I will play with some and see what I can achieve...

[Søren Dyhr]

There is no display. No user. No interactive processing. Everything is driven by the arrival of the email message, and everything after that has to happen without user interaction.

No of couse not, then use command 1 .... the applescript is only there to emulate what a rule in mail does. What I here tries to prove is the update occures based on the input made by applescript ...which BTW uses an interface strictly speaking.

How would you make the setup for the test then, you wish to digest the latest arrived mail ... so it can be source for the assembly of a new mail to transmit, this is where I count a and b's in the latest .... should this in any way be compared to previous arrivals??

--sd

[Colin Keefe]

An email arrives, is processed and a new message record is created.

That part you've obviously got down pat.

At the equipment level, a check must be run to decide whether to react to this message, or ignore it, depending on the type of message and whether any action has been taken already on a similar message already received. In my scenario, several simple calculation fields on the equipment record set various flags, and a more elaborate calculation field or fields will then decide what to do and execute the appropriate instructions (perhaps a script to send am email to an end user; perhaps a script to log a service call; perhaps a script to order a spare part. Or all of the above.)

Briefly, I think I understand this, though I will say I think some of the action logic should be pushed to a data structure rather than explicitly embedded in a calculation. For one thing, a generic calc called EquipmentAction in a table will need to contain explicit logic for each equipment record andevery possible action, and that to me seems as cumbersome as a script.

I would see this as a calc in Equipment that checks the most recent message in the Messages table for any data that matches items in a related ExpectedActions table. It keeps the code a little more abstracted, and when you need to add actions to the expected behavior, you just add an Action record to the ExpectedActions table.

When you actually perform the Actions, you would write the Action to a PerformedActions table with the EquipmentID and the MessageID the Action was a response to.

See attached Relationships Graph for a simplified example.

I guess that's quibbling and not the main issue, which is event triggers. But it does simplify some of the other things you're trying to do, below.

If an action is taken, the message record is updated to include the details of the action taken (so the message record becomes the permanent audit trail). If no action is taken then the message record is updated to include the flags or parameters which were set at the equipment level, and an error message (which might just say - duplicate message, ignored). Later, if you are trying to figure out why your system never sent a spare part for a specific piece of equipment, you can follow the decision making process more readily because of the audit trail.

Logging Actions in PeformedActions table with appropriate keys would provide this audit trail without having to actually do anything to the original Message record. The Message record and the Equipment record would both have related PerformedActions, timestamped, with details.

It also gets around the whole issue of updating Equipment records and Message records, since it's not actually the Equipment or Message that's changing - it's the Action you're logging.

At the equipment level, I would like to reset some fields with the ID of the latest alert, some data from this alert and some statistics. This is not essential, but it makes the "system" more robust and easier to debug since it is self-logging. I could write the details of what transpired to a log file, but I feel that this is more efficient. The message-handling process updates the equipment record, and the calculation fields on the equipment record then update the message record with the action taken. It is probably not necessary to include the details of the latest alert in the equipment table - though it will make exception reporting more efficient perhaps. I am not sure what performance of this application will be like at this point...

I think the first part of this paragraph - setting fields in Equipment with the latest message data - isn't necessary, if you model the data so that the most recent (or earliest, or whatever filter you wish) message data is accessible via relationships. Ditto with Actions.

Anyway, I need something which will trigger the calculation fields at the related equipment record when a new message arrives. I expected to be able to write the ID of my message record into the related equipment record, thereby triggering the calculation fields in the equipment record.

To the first part:

I know you hate the idea of embedding this in a script, because of concerns that all the logic will be socked away in there, and make the data structure opaque.

But when a new message arrives, it's arriving via a scripted process, and I sincerely don't believe that having that same script execute the logic embedded in a set of Equipment, Message, ExpectedAction and PerformedAction tables will make that logic as opaque as you think. Remember, the script can be dumb. It doesn't need to know which actions need to be performed - the Equipment record will inform the script based on what related ExpectedActions match the Message - it just needs to know what to do with the actions that match the current context of Equipment/ExpectedActions/CurrentMessage. If your data is modeled correctly, that can be as simple as:

from Equipment context:

Go to Related Record (Required Actions) // - assume a relationship structure here that filters only the required actions, based on expected/current message

Go to Record/Request (first)

Loop

If (ActionType="Email")

// - perform email action here

Else If (Action Type="Log Warning")

// - perform log writing code here

etc.

Go to Record/Request (Next, exit on last)

The above script doesn't know or care what the particulars of the Equipment record are, or know in advance what actions are expected to perform, or what the particulars of the message are. All of that is informed by the data model. And all of it can be driven by the script that inserts the incoming message into the message table.

I understand that records will occasionally be created manually by an admin. Remember that for that there will be an interface, and the user experience can be controlled. Replacing FileMaker default menu choices (command/control-N for New Record) with Custom Menus tied to an abstracted script process can handle that exception.

Anyway...take all of this with a grain of salt.

Best,

Colin

[comment]

I want to define the response in the equipment table

IMHO, that is a conceptual mistake. Because the response is not to equipment. The response is to some attribute of equipment, which arrived as a message.

Let's simplify this example to the bare minimum. Let's have a table of Equipment with fields:

• EquipmentID

• MaxTemperature

and a table of Messages with fields:

• MessageID

• EquipmentID

• Timestamp

• Temperature

We want an e-mail to be sent when a message arrives with temperature higher than the maximum set in the parent equipment record. Since e-mails cannot be produced by a calculation, we will need to trigger a script.

I would have the script that created the new message go on and check if the temperature exceeds the parent's maximum, and if so, send an e-mail. No event-trigger is required.

If you don't want "a mega script which controls everything", this can be (and should be) broken into individual sub-scripts: one to create the record, one to check the condition, one to send the notification. The amount of required work needed is the same in any case (except the redundant duplication of data into the parent, which my method avoids) - so the only question is who/what calls each sub-script.

If I understand you correctly, your method would run a script to create a value, whose creation is then supposed to trigger another script to modify another value, whose modification is then supposed to trigger yet another script to finally get to the point of sending the e-mail. All you have achieved is data redundancy and more potential points of failure. The conceptual mistake has spawn mistakes on the strategic and tactical levels.

I think it is becoming apparent that the real issue here is that FM has no triggers. There are those in the FM community who think this is no big deal, and others who think it is.

I used to think it was an issue, too. When I understood better what a relational database is about, I realized it's not. You may find the folowing note interesting in this context:

http://fmforums.com/forum/showpost.php?post/187127/

[byronic]

Good catch! Everything is pulled by the layout event there are two solutions to the problem either show the calc'field in the child-tables layout stashed away in a corner where text-color matches the environment, or even better do it via a self-join ... provided we just have the parent record for processing purposes only, are the cartesian relation actually made redudant...

This is very obtuse for me I have to say. I have had a good look at the code but I don't understand what it is doing.

However, as is often the case, you have helped me find an easy solution to another problem we discussed earlier - which is to obtain the first and last alerts. The first is just straightforward, leaving the sort order as creation order. To find the last alert I create a calculation field which counts the related record set and then I use your GetNthRecord (field, record number) to pull in the ID of the most recent message for a piece of equipment.

Very neat. :-)

Now I have to see if this is enough to trigger my calculation fields on the equipment table. :-)

[byronic]

IMHO, that is a conceptual mistake. Because the response is not to equipment. The response is to some attribute of equipment, which arrived as a message.

Is it? I think it is a response to a piece of equipment which finds itself in a particular situation, when governed by a range of parameters set for itself specifically, the class of device itself, the location at which it is running, the organisation within which it is deployed.

Let's simplify this example to the bare minimum. Let's have a table of Equipment with fields:

• EquipmentID

• MaxTemperature

and a table of Messages with fields:

• MessageID

• EquipmentID

• Timestamp

• Temperature

We want an e-mail to be sent when a message arrives with temperature higher than the maximum set in the parent equipment record. Since e-mails cannot be produced by a calculation, we will need to trigger a script.

If it was simple, this would be ok. But it is not so simple. The equipment class may be being retired, or it may be out of warranty, or it may be in a remote location, or it may be a known problem which can be ignored, or it may be close to the end of the contract, or a rollout of replacements may be in progress...

I would have the script that created the new message go on and check if the temperature exceeds the parent's maximum, and if so, send an e-mail. No event-trigger is required.

If you don't want "a mega script which controls everything", this can be (and should be) broken into individual sub-scripts: one to create the record, one to check the condition, one to send the notification. The amount of required work needed is the same in any case (except the redundant duplication of data into the parent, which my method avoids) - so the only question is who/what calls each sub-script.

The only data i think i really need in the parent is the last alert ID. The rest can be pulled from the child record when required.

If I understand you correctly, your method would run a script to create a value, whose creation is then supposed to trigger another script to modify another value, whose modification is then supposed to trigger yet another script to finally get to the point of sending the e-mail. All you have achieved is data redundancy and more potential points of failure. The conceptual mistake has spawn mistakes on the strategic and tactical levels.

see below...

I think it is becoming apparent that the real issue here is that FM has no triggers. There are those in the FM community who think this is no big deal, and others who think it is.

I used to think it was an issue, too. When I understood better what a relational database is about, I realized it's not. You may find the folowing note interesting in this context:

http://fmforums.com/forum/showpost.php?post/187127/

[comment]

Is it? I think it is a response to a piece of equipment which finds itself in a particular situation

Well, you keep saying so, but that's where I think is your major obstacle. You think you need to respond to a SITUATION. I say the response should be the result of an ACTION. The action, in this case, being the arrival of a new message.

I believe I have explained why such approach is preferable. If you have more questions on this, I will try to answer them - but I will not try to convince you.

If it was simple, this would be ok. But it is not so simple. The equipment class may be being retired, or it may be out of warranty, ...

So? I picked a simple example to demonstrate a principle. The real situation may be more complex, but the principle stays the same. All you are showing me is more conditions that need to be checked.

As for the rest, I am puzzled why you think you need to explain these things to me.

[comment]

To find the last alert I create a calculation field which counts the related record set and then I use your GetNthRecord (field, record number) to pull in the ID of the most recent message

The Last() function does all of that at once.

[Colin Keefe]

Also, you should consider archival. In this system, the message record contains everything you would need to know about the system's response to an alert, including whether it was acted upon, and if so why, or if not, why not. It will show you what part was ordered, when, on what purchase order and what technician was despatched on what date to effect the repair - and on what invoice the repair was charged, and what date the invoice was paid. You can safely delete all the associated records in every other table once this incident has been close, because the message record is an audit trail that contains everything you need to know to reconstruct the event. Compare this to my financial system (Great Plains Dynamics running on MS SQL) which will not allow me to delete employees who have not worked for the company for years; customers who have not bought in years and any product I have ever sold. This is an elegant RDBMS design, but it is a nightmare to manage historically.

Conversely, if you store all the logic for your event triggers in granular calc fields, when the business logic later changes, and you need to change those calcs, another programmer has no way of looking at those old messages and understanding WHY they were acted upon that way. The business logic is lost, unless you agressively archive/comment your old logic calcs.

Granularizing business rules into easy-to-understand snippets is an attractive idea in theory. But if I were to inherit such code from someone else, I would have a hard time pulling all the pieces together and understanding the dependencies.

Storing business rules as records and abstracting the logic to perform those rules provides a method for archiving/updating business logic without the need to update calcs or scripts.

Two sides to every coin here, I suppose.

[byronic]

Conversely, if you store all the logic for your event triggers in granular calc fields, when the business logic later changes, and you need to change those calcs, another programmer has no way of looking at those old messages and understanding WHY they were acted upon that way. The business logic is lost, unless you agressively archive/comment your old logic calcs.

Granularizing business rules into easy-to-understand snippets is an attractive idea in theory. But if I were to inherit such code from someone else, I would have a hard time pulling all the pieces together and understanding the dependencies.

Storing business rules as records and abstracting the logic to perform those rules provides a method for archiving/updating business logic without the need to update calcs or scripts.

Two sides to every coin here, I suppose.

Well, not really. The "granular" calculations are just things that are self-evident. They just pull together all the "facts" on which the decision will be made. For instance, one calc field may simply establish whether the item is under warranty and set a field value to "Y", while another may establish the threshold for action for this device. Each of these parameters is written to the messages record, so the archived record shows you the state of the device, the nature of the message and the resulting action or inaction along with an english-language message to explain the decision.

The granular calculations are not part of the decision making logic in any real sense - you will always need to establish the facts before you decide how to deal with them.

[byronic]

You are so right. Thanks. :-)

[byronic]

So? I picked a simple example to demonstrate a principle. The real situation may be more complex, but the principle stays the same. All you are showing me is more conditions that need to be checked.

Each piece of equipment has its own complex algorithm to determine what alerts to respond to, and how to respond to them. It is not just a question of checking more parameters, but of using quite different logic for different equipment types. The processing logic (and parameters) involved in managing the supply and installation of air filters, say, for an air conditioning system (where new filters are required every nn hours of operation, and where the alerts continue until the filter is replaced and a switch reset, but the unit is still functioning) is very different from the processing involved in a memory module failure on a PC. The former involves complex analyis of the incoming alerts to determine whether an alert is a continuation of an event you have already actioned, or a new event which requires a response. In this case, where such alerts are expected, the logic also involves predictive processing to ascertain when the next filter will be due (using calculations based on reported usage over time).

I wouldn't want to include all this complex logic in my processing of the simpler DIMM failure alert. The KISS principle is at work here, even if it doesn't appear that way at first glance...)

If all of this processing were to be built in to the initial script it would be a very large script and one which would be, I believe, difficult to read and maintain: ensuring that large chunks of the logic were NOT applied to a specific situation would take as much careful design as the creation of the logic in the first place. In the Equipment Type table (parent of the individual devices) I can build simple rules which will apply to all devices of that type, and I can add exceptions to specific pieces of equipment which might, for some reason, require an individual approach (the CEO's notebook for instance).

As for the rest, I am puzzled why you think you need to explain these things to me.

Since you have very generously offered your assistance I have erred on the side of verbosity. And, particuarly, where I am suggesting I will not implement your advice, I feel obliged to offer some reasoning.

[byronic]

I used to think it was an issue, too. When I understood better what a relational database is about, I realized it's not. You may find the folowing note interesting in this context:

http://fmforums.com/forum/showpost.php?post/187127/

This example is about the pitfalls of duplication fields: RDBMS 101 I guess...

However, when I "duplicate" information in my messages table, I am storing the actual value of a field at a specific point in time. The values of these field will change over time in the parent record, and it is essential that these changes are not rolled down to the messages table.

A good example of where you would do this is in an order lines table where you "duplicate" the product description, price, weight etc. In the IT industry where products have a life of months rather than years, most of the products in your order lines table will have been discontinued within 12 months. Indeed, within 3 months, a large number of the products will have different prices.

So although the field is duplicated at the time of creation, it is really a new field - one with a value which is unchanging from this point onwards irrespective of any change to the original field.

[byronic]

[You may find the folowing note interesting in this context:

http://fmforums.com/forum/showpost.php?post/187127/

I think we are talking at cross purposes here now anyway. When I first looked at this problem I had not explored very far into FM, and (as you can see from the length of this post) a great deal of valuable assistance has been provided by those who have generously given their time.

In my equipment table, I have now defined several fields which duplicate information in the child records. however these are unstored calculation fields.

I am still working through the issue of writing data to the child record from a calculation defined in the parent, but I no longer need to write to the parent from the child - given that I will be running a script from a calculation field in the child (using a plugin) which will update the parent and, hopefully, provoke the execution of the relevant calculation fields at the parent record.

[David Jondreau]

If all of this processing were to be built in to the initial script it would be a very large script and one which would be, I believe, difficult to read and maintain: ensuring that large chunks of the logic were NOT applied to a specific situation would take as much careful design as the creation of the logic in the first place.

You've repeated this theme several times in this thread and it's caught my eye. You do know you can create multiple scripts that can be called from within on main script?

For example whatever processing is generic to all types can be performed then:

If[Equipment::Type = "SIMM"]

Perform Script[sIMM Alert]

Else If[Equipment::Type = "Air Filter"]

Perform Script[Air Filter Alert]

End If

[byronic]

You've repeated this theme several times in this thread and it's caught my eye. You do know you can create multiple scripts that can be called from within on main script?

For example whatever processing is generic to all types can be performed then:

If[Equipment::Type = "SIMM"]

Perform Script[sIMM Alert]

Else If[Equipment::Type = "Air Filter"]

Perform Script[Air Filter Alert]

End If

LOLOL... I think we have done this to death now... If I place the logic outside the equipment table, or preferably equipment type table, I have to build a script which allows for each equipment type and then calls the appropriate sub-script. If I add a new equipment type i have to remember to change the script and if I delete one, I have to do the same. By leaving the decision process in the equipment table it will always happen when necessary - automatically...

It's really about using the power of the database to manage itself - right now i don't have any scripts (except the external script which dumps the message data into the database). I just have calculation fields, and most of these just produce data. I guess I am going to need a couple of scripts to update data in the message table from a calculation field in the equipment table, but that should be a simple script which just updates a single field: thereafter calculation fields in the message table can take over.

So far, so good. Only I can't for the life of me work out how to get today's date in a calculation field: I need to subtract the date of the latest message from today's date and get an answer in days (actually an answer in week days would be better, but I can live with days). The books say that "//" should produce today's date in a calculation, but it doesn't do it for me... Any clues?

[Fenton]

Get (CurrentDate)

Must be unstored or it will not update in a calculation; it is always current when in a script.

Edited February 28, 2008 by Guest

[byronic]

Only I can't for the life of me work out how to get today's date in a calculation field: I need to subtract the date of the latest message from today's date and get an answer in days (actually an answer in week days would be better, but I can live with days). The books say that "//" should produce today's date in a calculation, but it doesn't do it for me... Any clues?

Solved my problem. Get( CurrentDate ) - LatestAlertDate gives me the answer in days... :-)

[byronic]

Get (CurrentDate)

Must be unstored or it will not update in a calculation; it is always current when in a script.

Thanks matey - just figured it out. Didn't know about the unstored part, but its used in an unstored calc field so that's cool...