I am new to filemaker and applescript tho i have some years experience in other databases.

I am looking for some way to trigger the execution of a script upon the addition of a record to a table - this record is added by a script which processes incoming emails and formats a new database record.

When each new record is added I need to use information in that record to update other database tables.

Where can I attach a script so that it will execute when a new record is added to the table? Where can I find a good scripting guide to help me build the script?

I need to use information in that record to update other database tables

I need to make sure you're aware that filemaker actually is an implementation of relational approaches, and it's "sacrilegiously" to think of synchronization?

--sd

yes, i am aware. i started work on relational databases in 1978.

when a new event record is added to the database I want to use that event as a trigger to calculate and write information to another table - to update information about the rate of events for a specific item, the current state of that item and so forth.

You havn't yet found the calc'fields then??

It however raises the issue if some the data actually is written in the correct table to start with?

--sd

Edited February 20, 200817 yr by Guest

yes, and they are very useful. but i can't see a way to embed a calc field in one record which updates fields in another record...

Why should it be updated, unstored fields only exhibit values when shown.

It would be beneficial to know what the fields/tables are in the two instances, an outline of what was supposed to be scripted ...too!

embed a calc field in one record which updates fields in another record...

No it's the other way round, you toss some data in one table and it gets morphed when presented in the other ...over the relation! Most commonly via an aggregate function, or a custom function involving:

http://www.filemaker.com/help/FunctionsRef-39.html

But there is more to it, the entire entity vs. attribute distinction might mislead you into thinking a script might be what's needed:

http://www.fmforums.com/forum/showpost.php?post/149069/

Which clearly shows you that the presentation layer not necessarily correspond to where data and field labels actually are stored.

--sd

Edited February 20, 200817 yr by Guest

Ok, sure. I have a table of records which are created by a script which intercepts incoming mail messages which contain a formatted network status message.

As each mail message arrives, a new record is added to this table. Calculation fields look up client, equipment and location tables to then select various styles of response and log these in the record.

I then want to perform further calculations and update the equipment record before i finish processing this new status message.

At the equipment record, I want to take the updated information and refresh my statistical information and send some emails.

There is, at no stage, anyone entering data. It is all entirely invisible.

I then want to perform further calculations and update the equipment record before i finish processing

This is sometimes called tagging, but shouldn't it in reality be a newly created record in a join table instead? Despair not here the booking doesn't necessarily need this extra table in between, which would demand a script to get created. In filemaker can you link on a pilcrow delimited field, and make it behave many2many.

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

--sd

Its not really that kind of relationship. The alert messages for an item of equipment are out of date as soon as the next one comes in. But, statistically, the frequency of alerts is important.

The detailed information from the LATEST alert is particularly important to someone viewing the equipment record but I cannot see, using a calculation, how to refer to the last record in a one to many relationship so as to extract the detail from the LATEST of the status messages when the equipment record is viewed.

And then I want to know how many errors in the last hour, day, week, month, year. I feel that this information is best stored in the equipment record, and updated incrementally as each error arrives.

I also want to send an email, sometimes, when these statistics are updated. The decision to send, and what to include, is held at the corporate, site and equipment levels.

ps: I notice you are a sound engineer. I start at SAE next month here in byron bay in bachelor of digital media (film). -)

using a calculation, how to refer to the last record in a one to many relationship so as to extract the detail from the LATEST of the status messages when the equipment record is viewed.

In danger of simplifying the matter out of proportion, would I say the sort order of the relation does it ... completely script free!

This could even be sans calc'fields, just place the field from the related table in the equipment layout, as long as the sort order is opposite the creation order.

--sd

I start at SAE next month here in byron bay in bachelor of digital media (film). -)

Havn't really ever had the money for it, I learned the ropes as apprentice in a touring company in live sound! Whether such institutionalized tuition makes sense at all is good question, but I've noticed that Full Sail pupils get the jobs after completed tuition, because of the internship system they provide.

http://www.fullsail.com/

--sd

Haha. That would be nice -) The entire file is retained so that the error details records can be analysed - for instance, to see when errors started and stopped, and for statistical information. In my equipment record i intend to show the viewer the details from the last record, but also from the first one (exactly when the issue began). I worked through this sequentially - so I have the detail from the first message, and now cannot get the detail from the last message. If I turn around the sort order, I have the reverse problem. :-)

You can have both or all:

http://www.briandunning.com/cf/308

But we have to distinguish between need to know and nice to know, all statistics should IMHO be a genuine reporting matter but you could cheat a little by using this while entering new records to your solution provided the initial entry is under script control.

http://www.kevinfrank.com/download/kf-fast-summary.zip

This will land the entire summary in a field you can make a part of the reply mail.

Way down in this article is an example on how to tie an applescript to a mail rule:

http://www.mactech.com/articles/mactech/Vol.21/21.09/ScriptingMail/index.html

--sd

Since you're a near total newbie, I think you should attempt this in the easiest way, by scripting. Don't worry about join tables, custom functions, summary fields etc etc.

The three basics of filemaker are tables & fields, layouts, and scripting. You already have a script creating a new record. You can add to that script or write a new one and call the new script from within the old.

Probably the simplest way to write a script is to simply do what you want manually, using the drop down menus. Most script steps simply replicate what you can do manually (those some important ones are only available in scripts).

Write down what you do, then assemble a script, using the Script Step Reference in the Help file as your guide.

Thanks for the input - though I am not sure I understand how to apply this to my problem. The script needs to update the database by itself, in background. The record is created by a script and thereafter everything is automated. There is no user input at any time.

The scripts need to perform a series of complex calculations on the information in the newly added record and then update other tables and spawn other processes.

I had in mind that my scripts would be small, single-function, and linked to the tables and fields themselves, just like a calculation function.

But I keep seeing suggestions about user input, or display windows, and this worries me - perhaps I misunderstood what Filemaker is about. All the transactions in this system happen behind the scenes - without any user involvement at all. It is designed to run unattended.

Did I choose the wrong product? Do I need MySQL or something similar instead?

"I am looking for some way to trigger the execution of a script upon the addition of a record to a table - this record is added by a script which processes incoming emails and formats a new database record."

"I had in mind that my scripts would be small, single-function, and linked to the tables and fields themselves, just like a calculation function."

These statements a little contradictory. If you're creating the record via script, parsing an email, populating fields with the data, and no requiring any user input, then there's no reason the script cannot continue and do other things.

There are (free) plug-ins which can provide "event triggers" upon field-level modifications. But I don't think you either need or want that for what you're proposing. If the 1st step of this process is actually scripted, you would just continue. If it's an AppleScript that started it, then it could call a FileMaker script, via:

do script FileMaker script "Script Name"

("FileMaker script" may be optional, but I always add it; have had problems without it, though can't remember.)

This is rather confusing. Since we are in the AppleScript section, you need to clarify when "script" means an AppleScript and when a Filemaker script. In any case, you can initiate a Filemaker script from within AppleScript just as you can from another Filemaker script (and vice-versa). So once you got the ball rolling, you can get it to roll as far as you want.

I had in mind that my scripts would be small, single-function, and linked to the tables and fields themselves, just like a calculation function.

That part is not going to work - at least not the way you think. Scripts cannot be "linked to the tables and fields" (unless you use a plugin). However, that shouldn't be obstacle, once you clarify what the actual problem is.

"I am looking for some way to trigger the execution of a script upon the addition of a record to a table - this record is added by a script which processes incoming emails and formats a new database record."

"I had in mind that my scripts would be small, single-function, and linked to the tables and fields themselves, just like a calculation function."

These statements a little contradictory. If you're creating the record via script, parsing an email, populating fields with the data, and no requiring any user input, then there's no reason the script cannot continue and do other things.

Without explaining the entire application in detail, the various tasks and updates are not synchronous. For instance, an initial equipment table record is required to be updated when a record is added to the messages table. A weekly update of the equipment record is required to obtain performance statistics which may then spawn emails or other table updates. A daily script will run and generate work orders for any issues arising from the messages received.

"There are (free) plug-ins which can provide "event triggers" upon field-level modifications. But I don't think you either need or want that for what you're proposing. If the 1st step of this process is actually scripted, you would just continue. If it's an AppleScript that started it, then it could call a FileMaker script, via:

do script FileMaker script "Script Name"

("FileMaker script" may be optional, but I always add it; have had problems without it, though can't remember.)

This is rather confusing. Since we are in the AppleScript section, you need to clarify when "script" means an AppleScript and when a Filemaker script. In any case, you can initiate a Filemaker script from within AppleScript just as you can from another Filemaker script (and vice-versa). So once you got the ball rolling, you can get it to roll as far as you want.

That part is not going to work - at least not the way you think. Scripts cannot be "linked to the tables and fields" (unless you use a plugin). However, that shouldn't be obstacle, once you clarify what the actual problem is.

I have found a plugin that looks like it will work. As for the section, this is the only forum which I could find which looked suitable - is there a separate one for filemaker scripting?

this is the only forum which I could find which looked suitable - is there a separate one for filemaker scripting?

Yes.

http://www.fmforums.com/forum/showforum.php?fid/36/

I have found a plugin that looks like it will work.

I have no idea what you intend to do, but let me warn you about this: it's a common beginner's mistake to think they need scripts and event-triggered scripting for just about everything. The truth is you should have as few scripts as possible, and limit their use to things like user interface, automated data creation, data import/export, and other auxiliary tasks like that. Using scripts for actual data manipulation is not good practice. That's what calculations and relationships are for. Event-triggered scripts can be nice here and there, but they are almost never REQUIRED, and I would avoid using them for anything critical.

Yes, there's a ScriptMaker section but don't worry about sections, per se, Stephen, we can always move them for you if you guess wrong. But I too was getting confused on which scripting you meant. No prob though ...

Then why did I post? I wanted to welcome you to FM Forums since nobody else did yet! You are being answered by some of the brighest (and nicest people on Forums) and you won't find a better group to help you through your FileMakering issues. And I agree with Comment 100% (if not more). Even advanced Developers can get messed up by using event triggers - they can be great but they can also bite you bigtime. And, I have only needed them twice; usually, good design will solve your problems.

Again, welcome!

LaRetta

Yes, there's a ScriptMaker section but don't worry about sections, per se, Stephen, we can always move them for you if you guess wrong. But I too was getting confused on which scripting you meant. No prob though ...

Then why did I post? I wanted to welcome you to FM Forums since nobody else did yet! You are being answered by some of the brighest (and nicest people on Forums) and you won't find a better group to help you through your FileMakering issues. And I agree with Comment 100% (if not more). Even advanced Developers can get messed up by using event triggers - they can be great but they can also bite you bigtime. And, I have only needed them twice; usually, good design will solve your problems.

Again, welcome!

LaRetta

Haha. Well I am glad you are also confused - i just learned that there are two types of script - I thought it was all applescript.

This application does the following:

1. A new status message arrives by email to Apple Mail

2. A script processes this mail and creates a new message record

3. Upon creation of this record some stats need to be updated in the equipment master record, using information stored in the master; in previous message records and in other database tables.

4. An email may be sent depending on the outcome of the calculations

5. Daily tasks need to run to evaluate the situation

6. Weekly tasks need to run to evaluate the situation

The message table becomes an audit trail. This means information from other tables must be pulled down to the message table so that they are not affected by changes to the other tables at a later date. I use calculation fields for this, and it was very easy to make this work.

The equipment master table needs to be updated with the current status and the date and time of the last message. There are some specific numerical calculations for some devices.

In my mind, I saw this process as follows:

a) The new messages record is created, and as a result...

... calculation fields automatically populate the audit fields with data pulled down by Lookups from parent records...

c) ... and some mechanism updates the parent Equipment record with some detail from the new messages record, or perhaps only the ID of the new messages record. (I cannot see how to do this without a script.)...

d) ... which fires an automatic update of the Equipment record, using calculation fields or scripts, to update statistics, send emails, create records in support database, etc.

The daily and weekly tasks run automatically based on a schedule.

I have been pointed to some plugins which will allow me to create the event-driven scripts I think, tho filemaker seems to have an odd create-record philosophy of create empty record first and then update the record following user input. So a record-creation event occurs before there is any data... I am not sure how this will work when the record is created by a script. Perhaps it will be created with all the data and my event-driven script will have something to work with :-)

I suppose I am just thinking logically from a real-world process point of view. Not knowing filemaker i am unable to look at my application from a filemaker perspective - which is why I am having problems I think...

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.

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).

"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.

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.

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 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.

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.

I need event triggers on

(a) new record being created

( field being updated

© time schedule

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).

I use calculation fields to copy data from related tables - the record is an audit trail and the data copied down from the parent tables must be static once it is copied and not change when the parent tables change.

"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.

The database design is fairly simple, though the logic is complex. It would be very easy if calculations allowed data in the parent table to be updated. Maybe there is a way to do this and I just don't know what it is...

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.

Yes, this is exactly what happens. An Applescript processes the incoming mail message, extracts the data and creates a new record in the messages table. Calculation fields in that table pull down the relevant data which must be preserved for audit purposes.

Here there is a bit missing... I need a method to update the parent record with the ID of the new messages record so that the calculation fields in the parent record are activated, and it can retrieve the data from the new messages record easily.

Then, when the parent table is updated to indicate that a new messages record has been created, various calculation fields are triggered in the parent record. Some of these will call scripts to generate emails, create purchase orders etc.

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.)

That would be fine - i don't yet have filemaker server, but i have found a plugin which will do scripts on a schedule.

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.

I think this depends on the mechanics of the underlying database. But the important thing is that I want to run a script when the record is first created (ie: I don't ever want to rerun the script, no matter what data changes in the record) but i need the fields to be populated. I was using another plugin which worked on RecordCreate, but i will have to use a field-level plugin and figure out how to ensure it only runs once.

You can have both or all:

http://www.briandunning.com/cf/308

This looks very complicated - a lot of file handling to read forward to the end of related records in a flat file. When it gets this difficult it seems to me there is a lack of function in the product. I guess i was expecting it to be easier to find the last record in a selection of records.

But we have to distinguish between need to know and nice to know...

It is often a trade-off whether it is simpler and more efficient to perform the calculation when the data changes, or perform the calculation every time the record is retrieved. If I could easily retrieve the record i wanted from the message table, without having to manually scroll through the records under script control, i would prefer to calculate on retrieval - though the reporting issues may well make this less efficient than updating when a new status message arrives.

... all statistics should IMHO be a genuine reporting matter but you could cheat a little by using this while entering new records to your solution provided the initial entry is under script control.

http://www.kevinfrank.com/download/kf-fast-summary.zip

This will land the entire summary in a field you can make a part of the reply mail.

Nice plugin - but not what I am looking for in this case. -) The mail is about the action we have taken in response to the alert - and while all the alerts are used to make that decision, they are not communicated to the recipient.

Way down in this article is an example on how to tie an applescript to a mail rule:

http://www.mactech.com/articles/mactech/Vol.21/21.09/ScriptingMail/index.html

--sd

Ok, well i outsourced this part of the application since I was likely to spin my wheels for some time and i have a deadline!

I was intending to keep the calculations related to a table in that table. In this case, the equipment table is the best place to decide what to do about the status messages for each piece of equipment - since the approach will vary depending on the parameters stored for the piece of equipment or at a higher level.

Filemaker seems very focused on what is happening at the screen - but in this day of web-based transactions many database updates will occur "behind the scenes" without user intervention. Perhaps i chose the wrong product?

Perhaps i chose the wrong product?

Read here what Ernest Koe says about filemaker as database:

http://www.proofgroup.com/articles/2006/jun/filemakery_part_i

In spite of the popular misnomer, FileMaker Pro is not a "database"

But why not study a filemaker'ish way to deal with resource allocations/bookings??

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

Edited February 22, 200817 yr by Guest

I guess i was expecting it to be easier to find the last record in a selection of records.

No it isn't - it's just: GetNthRecord(myField;Get(FoundCount)) when observing the present sort order.

--sd

I have no idea what you intend to do, but let me warn you about this: it's a common beginner's mistake to think they need scripts and event-triggered scripting for just about everything. The truth is you should have as few scripts as possible, and limit their use to things like user interface, automated data creation, data import/export, and other auxiliary tasks like that. Using scripts for actual data manipulation is not good practice. That's what calculations and relationships are for. Event-triggered scripts can be nice here and there, but they are almost never REQUIRED, and I would avoid using them for anything critical.

I can vividly imagine LaRetta rushing down to her local mall to buy a new frame to put this in, very likely to hang on the already densely packed wall of golden Michael Horak quotations

--sd

I found your 6-points specification above too vague to allow any specific comment. It could describe almost anything and tells almost nothing about the true nature of the beast. I believe Fenton identified correctly the major problem in your approach: you think of your solution as a series of PROCESSES, instead of analyzing the STRUCTURE first. A database, or a database-driven application, needs to be described first and foremost by its data model. That means tables, fields, relationships, calculations - and only then scripts.

Despite the general vagueness, there is one point in your subsequent descriptions that caught my eye:

It would be very easy if calculations allowed data in the parent table to be updated.

If calculations were to allow data in ANOTHER table to be modified, you would have a redundancy. The correct way to handle this is to have the parent record look at the most recent data in the child table.

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.

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.

I need a method to update the parent record with the ID of the new messages record so that the calculation fields in the parent record are activated, and it can retrieve the data from the new messages record easily.

You have it all backwards. 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.

FileMaker is not that difficult; and it is probably more than enough tool for your needs. But you must (many times) change perspectives and attempt to grasp things which seem to go against nature. For instance, being told to turn into a skid ...

And yep, Soren, elegance excites the heck out of me and I'd plaster my whole house with it if I could!! :laugh2:

Edited February 22, 200817 yr by Guest

Here there is a bit missing... I need a method to update the parent record with the ID of the new messages record so that the calculation fields in the parent record are activated, and it can retrieve the data from the new messages record easily.

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

I suppose I am just thinking logically from a real-world process point of view. Not knowing filemaker i am unable to look at my application from a filemaker perspective - which is why I am having problems I think...

Well, FileMaker's just like any other application development environment, in that the goals are the same - you're trying to model user and system processes in an application. I don't think it's a bad thing that you're looking at your application from a process model first - but I would call your 6 points a rough requirements or feature list, just to step back from development-specific terminology. Mainly because how you implement this will be an integrated mix of data modeling and scripted processes.

Implementing those requirements DOES require some understanding of how the FileMaker data and process model works, however, and I think that's where you're getting hung up. Fenton pointed earlier to the best method of building this out, and it sounds like you're on that track anyway.

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

I have been pointed to some plugins which will allow me to create the event-driven scripts I think, tho filemaker seems to have an odd create-record philosophy of create empty record first and then update the record following user input. So a record-creation event occurs before there is any data... I am not sure how this will work when the record is created by a script. Perhaps it will be created with all the data and my event-driven script will have something to work with -)

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.

Here there is a bit missing... I need a method to update the parent record with the ID of the new messages record so that the calculation fields in the parent record are activated, and it can retrieve the data from the new messages record easily.

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.

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.

Edited February 22, 200817 yr by Guest
Updated to mention sorts in relationships.

Just a few points, the first being a repetition of what I said earlier:

1. You are reversing the meaning of "calculation" and "look up". A calculation will always re-evaluate when the fields within it are changed. This is not "historical" data, in the same sense that a lookup is.

As comment pointed out, it may be that you do not need so much data redundancy. You do not always need to set the same data in different tables, in a proper relational design; though there are also reasons why you might want to, for reports. But there's no way for us to advise on this until we see the structure and the uses you want to put it to.

2. I've seen, and participated in several threads on MacScripter.net where AppleScript gurus were running through all kinds of convoluted steps to run FileMaker, when all they needed to do was pass FileMaker the data, then call a FileMaker script. It was because they knew AppleScript better than FileMaker. I see no reason for AppleScript to be involved after initiating the process.

3. After the FileMaker script has been called, there is no reason why it could not do everything you ask, as long as you've given it sufficient data to do so. There is no need for event-triggers within a FileMaker script; it's already running and it can do anything (within reason).

After the FileMaker script has been called, there is no reason why it could not do everything you ask, as long as you've given it sufficient data to do so. There is no need for event-triggers within a FileMaker script; it's already running and it can do anything (within reason)

True, and when you think about it could the applescript tied to the rule in mail, actually act on stuff in the mail such as finding the end date of an month or a week, and call a different native filemaker scripts accordngly, unless the flow into the inbox is irregular or seldom. I mean you could always just for the hell of ot tie a rule to a mailinglist subscription covering Britney Speers fate... plenty of triggers there!

--sd

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.

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

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.

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.

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.

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.

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.

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.

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, 200817 yr by Guest

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".

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.

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.

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.

[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

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