Evaluate()

[bleapy]

Hi, I'm trying to update a Calculated Field in a Child Table, that is getting a Calculated Field using the Auto-Enter. The Auto entry is working fine, but I want the calculated field to change in all the related records in the Child table, when I make changes to the Field in the Related parent Table. I was thinking about using the Evaluate(), but I'm having trouble getting that to work.  

The goal is to use the Calculated Auto-Enter field for a Relationship for a TO. I can not make a relationship with a Unstirred Calculated value.  I've attached a sample file.

Thanks.

Evaluate.fmp12

[comment]

Evaluate() will not help you here. A stored calculation field in one table will never re-evaluate as the result of a field being modified in another table. Look for another way - for example, trigger a script on modifying the parent field, or rethink the relationship.

[bruceR]

No, that isn't a calculated field, it is a text field. It is a text field set up with an auto-enter calculation. 

But you're making it more difficult than necessary, in a couple of way. First of all, the calc should just be Employee::Type.

Secondly, it isn't clear why you want this field to be a lookup; especially, if the field needs to "keep up" with changes in the parent record. In that case, better to just display the value from the parent record, and eliminate this field altogether.

EvaluateMOD.fmp12

[bleapy]

Thank you. I was thinking about triggers, but wanted to avoid it if possible. But the Trigger defiantly worked. 

 

Thanks.

[bruceR]

Back to the question though. Why do you want to capture the TYPE field in the first place?

Just display the related field.

See employee method 2 layout.

EvaluateMOD.fmp12

[comment]

Back to the question though. Why do you want to capture the TYPE field in the first place?

The goal is to use the Calculated Auto-Enter field for a Relationship for a TO. I can not make a relationship with a Unstirred Calculated value.

@bleapy My own preference would be to redesign the contemplated relationship instead of using a script trigger. Perhaps you should ask another question about that.

Edited September 4, 2015 by comment

[bleapy]

Bruce, this is just a mock of a file that I'm working on.  Here's the relationship setup that I have set up:

Company -< Dept -< DeptYear -< EmplRcd -< EmplPayAdjust

DeptYear -< Fund -<FundLine

EmplRcd -< EmplAssignment >- (TO of Fund - using the TYPE field and pk_FundID; In the Fund Table, I have a Global Value for the Type; i.e. Staff, Teacher, Administrator, etc this will link up with the Calculated Type Value; so I can get the Sum of the Value who are Staff.)

The TYPE is referencing the type Employee Record (Staff, Teacher, Administrator, etc).  I need to sum the adjustments based on the type of employee, like a Cross-Tab table.  When I use a calculation field, it will not allow me to establish a relationship, however, when I create it as a Text field w/Calculation value, the relationship can be established and I can get a Sum of the Amount for each Employee Type.

Any Suggestions?

Another issue I had with DB design, is that I have a lot of Summary/Calculation Value, and when I tried running this on an FMGO (FMGO14 on FMServer 14) it's slow and REALLY slow when I'm connecting on remotely off the local network.  So, I was reading and should create a "dummy table" to duplicate the table and create a report off the dummy table, so all the calculation are values and not be calculated on the fly. So duplicate a table and its record every night. However,r I'm stumped on how to do this using a Script. Suggestions on this would be appreciated too.  

 

Thanks.

 

[David Jondreau]

There are other, better ways to sum subgroups of records aside from multiple relationships. If this is for display only, filtered portals and summary fields work very well if the number of total related records isn't large (more than a few hundred).

[bleapy]

Thanks David for the response. I'm trying to create a Summary Report that is sorted by Year, Fund, Department, and the body of it will be the Type.  And I've used Calculation fields and then Summary fields to generate this report. Another big report I've made is like Cross-Tab monthly pay for each employee, sorted by Funds.  So there are a lot of Sub-Summary calculation, and dynamic calculation based on a Global Field on the Fund number.  Everything is calculating correctly, but it's just slow when I'm trying to access it off from the LAN.

A performance/efficiency question; does the calculation get processed faster with an Execute SQL or through a TO relationship?  Or no difference? TO creates a Havoc on the Relationship map :), but I was looking into using SQL more.

thanks.