14 Replies Latest reply on Apr 10, 2012 4:16 PM by johnhorner

    Calculation Field Giving Different Result For Remote Users

    johnhorner

      Title

      Calculation Field Giving Different Result For Remote Users

      Post

      hello...

      i have a calculation field that essentially adds a couple summary fields from a related table together.  It works fine when using the database on the host machine.  However, if the database is opened remotely it shows a very different result.  In fact, for some of the records, it does not seem to even resolve one of the basic relationships.  one of the related fields on the "main" layout is empty and un-enterable on some records, while on other records it shows the correct related information and the field can be edited (on all the "parent" records the field points to the same related record).  a simple refresh window step on any parent record partially fixes this problem and displays the correct related information and makes the field enterable again in the records where it was absent. however, the calculation field does not change and continues to display an "incorrect" value.  however. if i cut and paste the information in any of the parent records key fields, it fixes the calculation problem for that individual parent record.  but if i re-enter (cut and paste) the data in the related field (on any of the parent records), then everything gets resolved in all the parent records (related data is visible in all records and the calculation fields "refresh" themselves).

      Any ideas why this is happening for the remote users and how i might be able to fix it...?

        • 1. Re: Calculation Field Giving Different Result For Remote Users
          philmodjunk

          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.

          http://help.filemaker.com/app/answers/detail/a_id/3604/kw/global%20field

          • 2. Re: Calculation Field Giving Different Result For Remote Users
            johnhorner

            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).

            • 3. Re: Calculation Field Giving Different Result For Remote Users
              philmodjunk

              Is this a calculation field that is "global"? if so, remove that setting and make it Unstored.

              • 4. Re: Calculation Field Giving Different Result For Remote Users
                johnhorner

                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 (

                    //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??

                • 5. Re: Calculation Field Giving Different Result For Remote Users
                  philmodjunk

                  But is uuid a global calculation field or a global data field?

                  • 6. Re: Calculation Field Giving Different Result For Remote Users
                    johnhorner

                    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.

                    • 7. Re: Calculation Field Giving Different Result For Remote Users
                      philmodjunk

                      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?

                      • 8. Re: Calculation Field Giving Different Result For Remote Users
                        johnhorner

                        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?

                        • 9. Re: Calculation Field Giving Different Result For Remote Users
                          philmodjunk

                          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.

                          • 10. Re: Calculation Field Giving Different Result For Remote Users
                            johnhorner

                            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.

                            • 11. Re: Calculation Field Giving Different Result For Remote Users
                              philmodjunk

                              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.

                              • 12. Re: Calculation Field Giving Different Result For Remote Users
                                johnhorner

                                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!

                                • 13. Re: Calculation Field Giving Different Result For Remote Users
                                  philmodjunk

                                  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.

                                  • 14. Re: Calculation Field Giving Different Result For Remote Users
                                    johnhorner

                                    ok... got it.  as always, thanks so much phil... i really appreciate all the help!