Triggering an Auto-Enter in Parent Table when field in child table is edited

[Toby B]

I imagine this is an age old problem that every FM developer has hit their heads against at some point since the beginning ... but I thought maybe some of the more "brainy" amongst us might have discovered a workaround to the limitations filemaker has provided us with its "out-of-the-box" set of tools.

Let me preface this discussion by saying I am aware that there are a range of Plug-ins out there that would solve this problem with little effort, but I am a purist and abstain from any temptation of an easy path that leads me to develop a solution that requires a third-party add-on, forcing me to ensure every client has the required software installed before for my DB functions properly, and in some situations you have no control over what the clients have installed.

So can we agree not to suggest a plugin approach?

My problem is this:

I want a field in the current record (Parent Table) to evaluate it's auto-enter calculation whenever certain fields from one of the items in its portal (child table) is edited.

To give you my example:

I have a Timesheet (Parent) and its Timesheet Items (Child Table). I have a boolean field "_kaln__Flag_Complete" in the timesheet table. This field's purpose is to signify whether the sum of all the child items' time add up to or exceed a weekly required total. If so it's value is 1 otherwise the timesheet is incomplete and therefore gets a value of 0 (zero).

I can do this with a calculated field, no problem, but I need this boolean field to be used as a right-side key in a relationship (the many side of a 1 to Many) that will allow managers to quickly filter a list of all timesheets to just those that are incomplete (ie. boolean filed = false).

I have avoided trying to update the field via a script due to the fact that the user would then have to click a button for this script to execute and I can not assume the user will do this. So I have resorted to trying to utilize the Auto-Enter calculation.

My Auto-Enter calc utilizes a Let statement combined with references to the related child fields and the Get ( ActiveFieldName ) function ... a trick learned from examples provided by Mikhail Edoshin. But I can not get the Auto-Enter to evaluate when the portal is edited.

Does anyone have some tricks on getting the parent record to trigger the Auto-Enter calculation whenever one of the child records are altered?

[Delta Tango]

You are on the right track with Get(ActiveFieldName).

Try using the evaluate function. You can add a parameter for fields that will trigger the calculation.

So for example:

Evaluate(

If(

myTriggerField > 5; 1;

0;

)

;

MyTriggerField

)

I don't know if this will work but it should. I know that if the trigger field is a calculation and not a regular field, it might not work. Let me know.

[comment]

I don't know if this will work but it should.

I'm afraid not. Filemaker does not monitor related records, and that's it. "Evaluate" is not a magic word. The calculation must be unstored, or it will not update.

However, it may be still possible to establish a relationship based on the unstored calculation: search the forums for "Ugo's method".

[Toby B]

Delta Tango,

Thanks for your reply. I was under the impression that a Let statement offers the same "triggering" functionality that the "Evaluate" function does. So I think that would be doubling up or swapping one orange for another.

Comment,

I thought this topic might have raised a response from you. I have seen some of your other posts regarding this very issue and thought that some clever developer might of stumbled onto a method that was unintentional.

I saw that Matt Petrowsky published an article regarding using the web viewer to constantly evaluate a statement ... but he was still having to use zippScript to fire off a script.

I'll look into "Ugo's Method" thanks for the tip.

Toby

[Søren Dyhr]

Filemaker does not monitor related records

Indeed! And if you think a little further is it wrong from a relational discourse to attempt syncronization, the data stored somewhere shouldn't behave as stored elsewhere.

This should perhaps be a scripted approch instead?

First make a selection of Timesheets say from a range of dates with a request. 2) GTRR(FS) will make a set of timesheet items belonging to the desired timespan. 3) Constrain the found set to show only the records where boolean field is 0 ...4) then finally make yet another GTRR(FS) backward over the relation, and the found set is the uncompleted timesheets.

Ugo's method is fine too, except it deals with a portal showing first occurrences of each foreignkey, and uses the knowledge of the rest having the same foreignkey to render an unstored field in the portalrow with the summary/statistics.

But this will push the the nice to know vs need to know threshold, which is what you perhaps are willing to accept to avoid scripting ...but are your users just as willing to digest needless data.

The aim should in my humble opinion not to feel occupied existentially seen, but instead to accomplish something meaningful take a paradoxical look at this and have a laugh!

http://www.smallco.net/RestrainYourself.pdf

--sd

[comment]

Ugo's method is fine too, except it deals with a portal showing first occurrences of each foreignkey, and uses the knowledge of the rest having the same foreignkey to render an unstored field in the portalrow with the summary/statistics.

I think you are confusing this with another technique. Ugo's method - at least as I understand it - calculates an unstored PrimaryKey of the record when a condition is met. This is then matched to the real, stored PrimaryKey in another occurrence of the same table.

This way only records that satisfy the condition are made related (in the added TO), even though testing the condition itself requires an unstored calculation.

Other variants are possible, but I believe this is the basic implementation.

[Søren Dyhr]

But the wish is here to see in a non modal steamed fashion, a strained portal of the ones with incomplete attendance. Since I hardly find it a justified desire, does it indeed blur my understanding of how you would use Ugo's method for something that would go this way algorithmically:

Go to Related Record [ From table: “TimesheetItems”;Using layout: “TimesheetItems” (TimesheetItems) ] [ Show only related records; Match found set ] 

Sort Records [ Specified Sort Order: TimesheetItems::ForeignKey; ascending ] [ Restore ] 

Go to Record/Request/Page [ First ] 

Loop 

     Set Variable [ $Howmany; Value:GetSummary( TimesheetItems::FKCount ; TimesheetItems::ForeignKey ) ] 

     If [ GetSummary ( TimesheetItems::CompletedCount ;TimesheetItems::ForeignKey ) / $Howmany <,8 ] 

          Go to Record/Request/Page [ Next ] 

          Omit Multiple Records [ $Howmany-1 ] [ No dialog ] 

     Else 

          Omit Multiple Records [ $Howmany ] [ No dialog ] 

     End If 

     Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) ] 

End Loop 

Go to Related Record [ From table: “Timesheets”; Using layout:“Timesheets” (Timesheets) ] [ Show only related records; Match found set ] 

Here operating with 80% attendance as threshold, hence the ",8" in the if-statements condition.

I know the marketing people wish to convey a sense of "No programming required" as if spreadsheet aficionados really shy away from macros? I do not know if they really are, but we expirience a lot of metaphoric stubbornness from spreadsheeters who wish this streaming behaviour:

http://www.filemaker.com/ltc_interview/09-22-2007

Instead of cutting off for algorithmic approaches should they embrace them to prevent:

http://www.apa.org/monitor/mar98/smog.html

Alright if you take Hernandez "...for mere mortals" is the reason for normalizing datastructures to prevent excess scripting. So the virtue is to balance between these two points to make the good solution.

But Michael I would very much like to see what you had in mind here, since you wish to show only parent records which meets the criteria can't cathesian relations be used with Ugo's method ...or am I overseeing the obvious here???

--sd

[comment]

Since I hardly find it a justified desire, does it indeed blur my understanding

That is indeed an obstacle. But the technique is valid, and can be used in a variety of situations. I cannot imagine what objection you might have, for example, to a portal showing only incomplete Jobs on a Customer layout (where 'incomplete' = not all Job's Tasks have been completed).

how you would use Ugo's method for something that would go this way

Exactly as I said in my previous post. I think you may be missing the fact that there are THREE tables involved here (both in the OP and in my example) Parent, Child and Grandchild. The unstored calculation is in the Child table, and the outcome is a new relationship showing a parent's children filtered by the unstored calculation. If this is still not clear, I'll post a demo.

Incidentally, I looked again at the thread where Ugo's method first originated. You were right, what he showed there really did collect the FIRST related IDs. So you might call this version "the extended Ugo method".

[Søren Dyhr]

Three tables HUH??? Michael watch this:

I have a Timesheet (Parent) and its Timesheet Items (Child Table).

that will allow managers to quickly filter a list of all timesheets to just those that are incomplete

What is the third?? As I understand the question is it not to list incomplete timesheets in every customer, but to show a list of ALL incomplete timesheets in say a portal ...so yes I would like to know more.

--sd

Edited October 9, 2007 by Guest

[Toby B]

Just to set the story straight ... Comment is right ... there are three tables ... my Filters table (which is the layout context) "Parent", looking at my Timesheets Table "Child" through a portal which in turn had related Timesheet Items "Grand-children".

The sum of the Items determined if the Timesheet was complete or not. Therefore was an unstored calc. If you wanted to see just those Timesheets that are not complete you need some way to filter the timesheets based on this calc.

Ugo's Method worked perfectly. Thanks Comment for pointing out another way of approaching it ... and thanks also to Ugo for coming up with it.

I now simply have a calc in the Timesheet Table that checks to see if the sum total of its Items equal or exceed the weekly quota, and if so, result in the primary key of the record.

I then in the Filters table have another calc that List all the values from the Timesheet calc and relate this back to the Primary Key of the Timesheet table to form my relationship. Quite simple in the end.

Thanks again ... Comment

Edited October 9, 2007 by Guest

[comment]

You don't really need the List() calc - you can connect the second occurence of Timesheets directly to the first, matching the calculated PK to the real PK.

Both approaches have the same refresh issue, so it's best to sneak in a Refresh Window [Flush cached join results] step when returning to the parent layout after editing.

[Søren Dyhr]

so it's best to sneak in a Refresh Window [Flush cached join results]

...Or if the from filter releation is on a cartesian relation, change the local key value. By the way isn't there really need for the Filter Table.... just a separate layout. But as such do I really support the modality introduced by flipping to a new layout

when returning to the parent layout after editing

Next thing is the Ugo Key, there is no need for summing, when bolean values are used comes this to mind:

Case ( not Min ( TimesheetItems::Completed ) ;timesheetID)

Requirering that each new record in TimesheetItems have autoentered "0" on creation, "-1" or "-15" would do as well.

--sd