What is equivalent to Trigger and Join in Fm7?

[J__]

Hello,

In Relational database systems like Oracle you have the ability to create a trigger. A trigger can be triggered to do something whenever the trigger is triggered. What is the equivalent of this in FM? My guess would be a Calculated field. If my guess is correct is there a down side to using a calculation in this way? I would guess this would also be a little limiting since a Calculated field only works on the same table - how would you triggger an update to be made to a different table?

Along the same lines is there anyway to do a JOIN on two table to create a View of the two? So in this way you can include fields from more than one database on the same layout? My guess is no, if no, then what is the recommended way to work around this?

thanks for the help in advance,

sincerely,

J__

[overrider]

i think what you are asking for does not really exist yet in filemaker, even though it is a feature that many people have asked for a long time. the only thing you can do is for example to make a field which turns to 1 or 0 based on certain conditions, lets say

Case(current_date > (last_backup + 30) ; "1" ; "0")

you then have a script which backs up your database if the field is 1. but you still have to trigger that script yourself.

then there is a auto enter calculation function, if any of the connected fields change in value, it auto-enters the new value. this could be called auto-triggering, but i dont think this is what you meant.

[BobWeaver]

The JOIN function is performed by the relationships between the tables. You can display related fields from many different tables on the same layout

As for triggers, as Overrider says, Filemaker is rather limited in this respect. Changing a field value will trigger the calculation formula for another field. If you want to do more than that, you need to look at one of the many event trigger plug-ins that are available.

[Søren Dyhr]

Case(current_date > (last_backup + 30) ; "1" ; "0")

Sorry for nitpicking, but this is overkill ....No need for the case-statement at all, could be done like this:

GetAsText(current_date >(last_backup +30)) ...but why deliver it as text in the first place??

Then to this threads question... If you need a script to create records in a jointable, is this often instead done with a multilinekey generated by a calc'field, and depending on the sofitication could this be done with the result put into a textfield or repeating fields. Custom functions come in here very handy, because they due to the ability to make recursive calls ...can make loop'ish behaviours happen.

As an example can you investigate how Ender makes recuring events in this template:

http://www.fmforums.com/threads/download.php?Number=158337

--sd

[J__]

Thanks to all for responses.

Let me elaborate a little.

First, when I said JOIN, I would like to do a select statement on two tables and yield a view - a result set of those 2 tables 'joined' together.

For instance, suppose I have 2 tables Customers and Products (for simplicity sake) there's a customer_pk and in the products table there is a customer_FK field.

in sql i could say

Select * from Customers, Products

Where ( Customers.Customer_PK = Products.customer_FK )

So i get a view with fields from both. Not a master detail view. ... I did n't think this could be done in FM, but I wondered if there was a trick you guys knew of, which I had not heard of <grin>

Next as for Triggers, I understand the only thing you can do (is this ok?) is to have a calculation on a related table, which would update some of the fields in another table. Is that right?

For example, I have a Customers -> Products joined on Customer::PK and Products::Customer_FK in a relation.... if I create a calculated field in Customers to get the count of Products the customer has bought that would be ok right? .. and I could also create a calculation in the Products table, which uses a field in the Customers table, say I wanted to display the name of the customer who purchased the product - so if the customer's name changes in the customer table, then when i look in the products, then it is changed - right, so that's a little like a trigger.

but for triggering on a new record or something, that is not possible - unless you have a 3rd party plug-in is that right?

I'm just trying to be sure I understand this correctly - and I really appreciate the input from you folks.

thanks,

sincerely,

J__

[Søren Dyhr]

Next as for Triggers, I understand the only thing you can do (is this ok?) is to have a calculation on a related table, which would update some of the fields in another table. Is that right?

Well, you do not update fields in the other table, but you change the view. If you have a list if id's that represents each record which have a datefield which is also is a monday, could you change the list by calc ...so it instead makes all tuesdays records shown...

So a list of id's in one table is set to relate to the id's in the other table, so a join table isn't nesserily needed to make a many 2many... where you probably are used to generate records in the join table containing the id from both tables. So a change in the relations is done via a script that either delete or creates records in the join. In filemaker do we have both choises, but the lack of triggers that connect to scripts ...makes the two approaches best for each thier own sort of task.

if I create a calculated field in Customers to get the count of Products the customer has bought that would be ok right?

Yes use either an agregate function over the relation, or show (tunnel) a summary field from the related table.

say I wanted to display the name of the customer who purchased the product - so if the customer's name changes in the customer table, then when i look in the products, then it is changed - right, so that's a little like a trigger.

This is a different issue, the key field trigs a thing we call lookups, which best a put inside a Evaluate( in an autoenter calc with the update option chosen... You can design it as a Lookup autoenter, but they're slow compared to evaluate, and can only be trigged by alteration of the lookup keyfield.

right, so that's a little like a trigger.

Again there is an aspect that can be a little confusing, calc'fields can be set either stored or unstored ...which means that functional dependencies first are backwards calculated when demanded, so they take up no or little place if not refernced.

--sd

[J__]

thanks for the information 'sd'

I appreciate it.

sincerely,

J__