Sounds like you have one or more fields with global storage defined--either used in the relationship, in the calculations (check any portal filters as well as calc fields and scripts) or both.
Global fields will behave differently on the host than on guest machines so this is why I suspect a global field.
ahhh... ok. there is indeed a global field used in one of the relationships required by the calculation field. in this case, the global field contains the uuid which identifies the contact record belonging to the owner of the database. after it is initially setup for the first time, it never changes again, for the user or the host.
what is strange is that if i place this related global uuid field on the layout for the parent record, it displays correctly on all parent records, but it is almost as if it is not really there because any other fields i place on the layout that are farther "downstream" in the relationship chain, do not show any information. it's as if the uuid was empty (even though i can clearly see that it is not empty). a "refresh" step will reveal the downstream related fields, but still does not update the calculation.
it does resolve everything if i cut and paste the global uuid back into itself, but this seems very strange... i suppose i can just add a set field step to set it equal to itself, but this seems kind of "hokey" for lack of a better term. is there a better way to "activate" the global uuid field?
is this "normal" or expected behavior for a global field to behave in this fashion?
as far as i can tell, all the other global fields and the relationships that depend on them seem to be working as i would expect. i did a consistency check on both the user interface and the datafile and no errors were found (and, as mentioned, it works totally fine on the host machine).
Is this a calculation field that is "global"? if so, remove that setting and make it Unstored.
the calculation field itself is not global but is an unstored calculation as you suggest. however it does rely on the global field to evaluate the case statement (calculation copied below):
//CASE 1: RETURN IS FOR HOME STATE
__kp_SalesTaxState = salestax_preferences_all_ADDRESSES_MyCompany::Address_State ;
salestax_INVOICES_PeriodYearState::zs_GrossSales + salestax_INVOICES_PeriodYearInterstate::zs_GrossSales - z_GiftCertificates_Sold_PeriodYear_Actual ;
//CASE 2: RETURN IS FOR NON-HOME STATE
__kp_SalesTaxState ≠ salestax_preferences_all_ADDRESSES_MyCompany::Address_State ;
salestax_INVOICES_PeriodYearState::zs_GrossSales - z_GiftCertificates_Sold_PeriodYearState_Actual
the global uuid field is used in the relationship between "preferences_all" and "ADDRESSES_MyCompany" to get the "home" state stored in the addresses table.
hope that helps explain this somewhat convoluted setup. happy to provide any other details as needed. thanks!
update: ...it does in fact resolve the problem if i add the set field to itself script step in the opening script... but i would still like to figure out why i am having this issue??
But is uuid a global calculation field or a global data field?
oh... sorry. the uuid field is a global data (text) field. it previously had an auto-enter (replace existing value) calculation but i disabled that (to see if that helped... it didn't) so it is just a straight old text field now.
How does the uuid field get a value? This is where clients can get different results from what you see directly on the host machine.
If it's assigned a value on the host machine only and you can see the correct/expected value when you view the field from a client, it's not, by itself, the issue here.
BTW, what version of FileMaker are you using here?
hmmmm... well, the uuid field originally was set using a set-up wizard (script) of sorts when the user opened the file for the first time. they had to create a contact record for themselves (including a related address record where the "state" information resides... a pivotal piece of data for the calculation). as part of the set-up script the uuid primary key field for the new contact record was written to the global uuid field stored in preferences. for all practical purposes, this global uuid field never changes again for the life of the database. however, i did previously have the global uuid field set up with an auto-enter calculation so that if the user ever did change their global contact code in preferences, it would get the uuid for the newly entered contact code. i had tested that and it worked as expected to change the global uuid. but in trying to fix this issue i have disabled the auto-enter calculation just in case. so for all practical purposes the global uuid field is static (not ever set or re-set manually or by scripts). if it makes any difference the value for the uuid (both the global preference field and the one stored in the database owner's contact record happens to be "000001" as they were the first contact record created... it was set up long ago using serial numbers and has since been converted over to uuid's).
does any of that help?
If you put this field on a layout where its value can be seen, do you see 000001 in it when you access the database from a client machine? Is it a text or number field?
Note: if your "registration" script is set to assign a value to the global when the user accesses the database from a client machine (a very likely scenario if you host it from FileMaker Server), the change made to this value will not persist, the value will revert each time the file is closed on the client machine. To set persistent values into a global field from a client machine, I'd store the values in a single record "preferences" table of non-global fields. Then you can either refer to the fields in this record via relationship based on the cartesian join operator (x) or use a start up script that loads the global fields from matching fields in the preferences table each time the record is opened.
yes... i can see "000001" on all records when i access from a client machine... and on most (but not all!) records i can see information that is related further downstream from this global uuid field (but i don't see any pattern as to why one or two records don't show the downstream data?).
about the "registration" script, i think i might have stated that it was for each user but i didn't actually mean quite that. it just runs once in the lifetime of the file (and on the host machine) for the person or company that own's the database. the "registration" script never runs again on either the host or the client machines. i do understand what you are saying about the use of non-global preference fields though. i currently have a preference table with a single record. most of the fields though are global (i think i did that so that i wouldn't have to create relationships to access the information?) although some preference fields, for example ones that i use to generate serial numbers, are non-global so that it generates serialized values regardless of the user.
please let me know if you need any more information or have any other thoughts. thanks.
Then it doesn't sound like network differences with global fields are directly the source of the trouble here.
I do recall a bug in fileMaker that may or may not be a factor...
For More Information see: Finds on calculation fields computing values from global field based relationship fail
This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.
It can also be downloaded as a database file from: http://www.4shared.com/file/8orL8apk/FMP_Bugs.html
My comments on the registration script are equally relevant whether it runs once per install (as I understood it) or each time a new user accesses the database. My point is that if you host the database from FileMaker server, you cannot run this script "from the host" in that case--only from a client machine.
ok... apologies again... i didn't fully understand what you were saying. i am very new to "server". when this file was created and put into service it was just opened on the "host" machine with fmp advanced and then accessed by others in the lan using "open remote". but now it is actually being hosted by fm server... so i see your point... which makes me wonder... if i continue to use global fields and want to make persistent changes, do i have to take the file down, open it in fmp, make my changes, and then put it back up on the server? or can i run a scripts from the server somehow (the way i would run a script in the data file, for example) to change global fields in a persistent way?
as far as the known bug issue, it does seem as though my problem may be related or caused by the same thing. thanks for all the help!
There are two ways to manage the default values in global fields:
1) as mentioned earlier, a script can set an initial value in each such global field when the file opens. You can then store the value to be used for this in a separate "preferences" table in fields that are not global.
2) If you use a disabled server schedule, you can set it to run a script that sets your global values and you can make the server schedule run the script on command from the admin console. The changes will not affect any currently connected clients, but the changes will appear for any users that connect after the schedule performs the script. You can edit the script from a client machine to have it set the desired values and then switch to the admin console to make it run from a "host" perspective.
ok... got it. as always, thanks so much phil... i really appreciate all the help!