mkohler28 Posted February 22, 2008 Share Posted February 22, 2008 I have a Calculation field that I do a search from to find a major amount of records and because the field conatins a list of data it takes forever to query the data, so I am taking the calc field data and setting into an indexed text field. The calc data can change frequently and when this happens I want the text field to always contain the same data. I tried making the text field an auto-calc or lookup so when the calc field changes that field will also change but it doesn't change the data. I also tried zipp script but could not get that to work either...Any suggestions? Link to comment Share on other sites More sharing options...
David Head Posted February 23, 2008 Share Posted February 23, 2008 I am assuming the search on the calculation takes a long time because the calculation is unstored (not because it 'contains a list of data'). So you have created a second text field which contains the results of the calculation, stored and indexed. To get the second text field to update each time the calculation updates, you can use an auto-entered calculation that replaces any existing result: Evaluate ( "CalcField"; CalcField ) What this does it to re-evaluate every time the CalcField changes. Notes that the expression to be re-evaluated must be in quotes. And it could be the calculation you are using for the CalcField. Link to comment Share on other sites More sharing options...
mkohler28 Posted February 23, 2008 Author Share Posted February 23, 2008 I tried doing this exactly how you have it and it still does not update the text field....I also tried adding the calc from the cal field in the "_", and it still doesn't change. Do I need to do something else to set the field? Link to comment Share on other sites More sharing options...
David Head Posted February 23, 2008 Share Posted February 23, 2008 Did you see when I said "...replaces any existing result"? This means that you must UNCHECK the checkbox in the auto-enter field options that says "Do not replace existing value of field (if any)". If this remains checked, the auto-entered value will never update. I think that is what you are seeing. Link to comment Share on other sites More sharing options...
mkohler28 Posted February 23, 2008 Author Share Posted February 23, 2008 I've always had that unchecked, and it does not update. Link to comment Share on other sites More sharing options...
David Head Posted February 23, 2008 Share Posted February 23, 2008 OK, let's not be so secretive. Can you tell me what fields you have (names and types), what calculations you are using and which fields you have set the validation for. Link to comment Share on other sites More sharing options...
mkohler28 Posted February 23, 2008 Author Share Posted February 23, 2008 So I am working out of four db's, db 1 is called (Tariff) everything else is viewed via portal from this screen. To start I have a calc field (OriginValue) which is in db 4 that looks up data from one of two fields from two separate db's. If a foreign key (kf_OriginValueID) is populated then I lookup the record (ZoneRecordsValue) from db 2 which is calc field itself listing multiple related records from field (ZoneValue) which is in db 3. If (kf_OriginValueID) is not populated then I lookup the value directly from (ZoneValue) which is just a regular text field. The purpose of this is to allow a user to either select an entire zone or just one zone value to apply to the main record in db 1, I only allow one or the other. [in db 4] Calc Field OriginValue - If ( IsEmpty ( kf_OrginValueID ) ; Lookup ( ZoneRecords | Origin::ZoneValue ) ; Lookup (Zone | Origin::ZoneRecordsValue ) ) [in db 2] Calc Field ZoneRecordsValue - List ( ZoneRecords::ZoneValue ) [in db 3] Text Field ZoneValue - Text Field So in the current db 4, I have (OriginValue), if a user was to change the zone value (kf_OriginValueID) this field changes based upon the entry. Next I have my field that I want to search from (OriginValueIndex), this is the stored/indexed field I am having the issue with. So this field is a text field using auto-enter Calculated value, "Do not replace exisiting value for field (if any)" is unchecked. [in db 4] Text Field OriginValueIndex - Evaluate ( "OriginValue" ; OriginValue ) So now when a user changes the parameter for OriginValue I want OriginValueIndex to contain the same value. One thing I did notice is that when the user makes the change on db 1, I haVe the refresh the portal field in order to see the (OriginValue) to change, then also refresh the field in the original db to see the field change. Thanks. Link to comment Share on other sites More sharing options...
David Head Posted February 23, 2008 Share Posted February 23, 2008 OK, so this structure is a quite complex in where you are trying to get values to feed through to. Also, you have noted an issue even with the calculation field - that you have to force an update of the portals to get it to refresh. Given that, you can't expect anything else to work 'normally'. Because the data you are updating needs to then force an update of a series of calculations (which itself includes Lookups), it just won't work by itself. BTW, am I correct in assuming from your profile that you are doing this in FileMaker Pro 8? Actually that can't be right because you are using a List function - so you must be using at least version 8.5. I have tried to set up a sample database but I don't have enough information about your relationships and key fields. But also, just a question about your calculations: Why are you using Lookups for the OriginValue calculation in DB4? Is there a requirement that the result be stored? My advice would be that when you update the source field, that you do it in a scripted way that also forces the refresh of the portals and updates the value in the text field. Link to comment Share on other sites More sharing options...
mkohler28 Posted February 23, 2008 Author Share Posted February 23, 2008 Yes I am using 8.5 Adv. I will try to use scripts to refresh the portals, the data in OriginValue must be stored, but I have not thought of any other way to set that field other than using a calc Lookup, I suppose I could just use a script with zippScript to set the field and then I could leave it as a stored/index field. If you suggest anything else let me know, thanks for your help. Link to comment Share on other sites More sharing options...
Jack Rodgers Posted February 23, 2008 Share Posted February 23, 2008 Simply using a calculated field to grab the contents of another calculated field that is quite complicated will not speed up your searches, I would guess, since the same calculations would have to occur in any case. You can speed them up, literally, but setting the field to the contents of the calc field for the moment of the setting. Thus the text field will not change no matter what happens to the calculations. After the first search it will be completely indexed and quite speedy. However, you will have to reset the field should the data change otherwise you will be using out of date data. This isn't as bad as it sounds, maybe... Don't feel bad, you have plenty of competition for the most complex calculation field and the test for the slowest search has been running on one database for about two weeks so it is probably going to win the title. Link to comment Share on other sites More sharing options...
David Head Posted February 23, 2008 Share Posted February 23, 2008 Simply using a calculated field to grab the contents of another calculated field that is quite complicated will not speed up your searches, I would guess, since the same calculations would have to occur in any case. Jack, the OP is not "using a calculated field to grab the contents of another calculated field". Specifically they said "I am taking the calc field data and setting into an indexed text field". That was the point of the post. They are using a stored text copy of the calculation result to perform searches. The question was how do they keep the stored text result up to date? Link to comment Share on other sites More sharing options...
Jack Rodgers Posted February 24, 2008 Share Posted February 24, 2008 Jack, the OP is not "using a calculated field to grab the contents of another calculated field". Specifically they said "I am taking the calc field data and setting into an indexed text field". That was the point of the post. They are using a stored text copy of the calculation result to perform searches. The question was how do they keep the stored text result up to date? You must have missed this paragraph in my post: ---- However, you will have to reset the field should the data change otherwise you will be using out of date data. ---- In other word, it is most likely an exercise in futility if 100% accurate info is required. For several reasons most having to do with long established procedures for locking in fixed results. Such a complex calculation can never be assumed to be accurate since there is a lag between a user committing a record and it making its way through the network. Data may remain in the users RAM only until the record is committed. Transaction processing is seldom if ever a part of a Filemaker database which invariably uses calculated fields rather than 'concrete data'. This is part of the problem of this thread. If non-calculated fields were used and instead data set by script into the fields, this a many other problems would disappear. This however would require effort on the part of the developer and maintenance of a chain of set fields. Normal for other databases but not needed due to calc fields in Filemaker. The price, slow searches. One solution would be to create a long winded script that would be triggered with each user change to all of these records. It would result as its final step the setting of these data into one text field (non-calculated). Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts