1 2 Previous Next 15 Replies Latest reply on Oct 2, 2015 1:34 PM by Nehme

    Refresh value of Unsorted Calculation

    Nehme

      Hello,

       

      I am using Filemaker Pro Advanced 13.

       

      I have a calculation field, Unstored, that needs to refresh on Re-Login as another user (to display another value).

      I will be using this field to filter the values of a ValueList (among different users in a network connection) => this is why I must use an Unstored calculation.

       

      I can't find the proper way to refresh the values.

      I can't use a simple text field (because the same value will be changed on all the network users)

       

      I tried the following:

      - Refresh

      - Sort

      - Looping through records and refreshing

       

      What could work is loop through records and SetField in this record.

       

      Is there a more elegant way (and less time consuming) of doing this?

        • 1. Re: Refresh value of Unsorted Calculation
          dtcgnet

          If you set the value of one of the fields in your calculation to itself, the calculation should "recalculate" itself.

          • 2. Re: Refresh value of Unsorted Calculation
            Nehme

            This is what I have been doing

            SetField ( X , X )

             

            But I am searching for a better solution, if there's any

            • 3. Re: Refresh value of Unsorted Calculation
              dtcgnet

              This is a link to an awesome blog post on refreshing calculations, conditional formatting, portals, and so on. I re-study it often.

               

              Shaking the Dependency Tree | FileMakerHacks

              • 4. Re: Refresh value of Unsorted Calculation
                Nehme

                Great link.

                Explains a lot.

                 

                But it was written:

                "If an unstored calc field references a related field ..."

                 

                It is possible to have an unstored calculation with a related field?

                It will directly shift to a stored calculation. Isn't it?

                • 5. Re: Refresh value of Unsorted Calculation
                  dtcgnet

                  No. Any calculation that refers to a related field will automatically become "unstored". If all fields that are referenced in a calculation are "local", FileMaker will make that a stored calculation by default. But any reference to a global field, global variable, or related field will change the calculation to "unstored".

                   

                  Sent from my iPhone

                  • 6. Re: Refresh value of Unsorted Calculation
                    Nehme

                    In fact, I totally turned my question the other way around.

                    In fact, my calculation was STORED (and not Unstored)

                     

                    And what I want to do is refresh a stored calculation.

                    Sorry for this inconvenience.

                     

                    And this is what I got from the article:

                     

                    Stored calculation fields only ever evaluate or re-evaluate under three circumstances, as far as I can tell:

                    1. When they are defined or redefined, all existing records in the table will immediately have the calculated value stored in the field.
                    2. When a new record is created, it will immediately calculate the stored calc field values.
                    3. Whenever local referenced data is changed. This happens whether the referenced field is on the layout or not.

                    So I suppose there is not other way then recalculating fields

                    • 7. Re: Refresh value of Unsorted Calculation
                      Nehme

                      I finally used the Replace Field Content script step

                      This can refresh values of stored calculations

                      • 8. Re: Refresh value of Unsorted Calculation
                        coherentkris

                        You should know that replace field contents replaces the value in the field for every record in the found set.

                        Replace field contents on 10 records is fast.

                        Replace field contents on 100000 records not so fast.

                        A better solutions is to define a global field in the table to use as a trigger and wrap your calc in a let...

                        Let (

                        [

                        _trigger = trigger::field

                        ];

                        the calculation

                        )

                         

                        If you set the value for trigger::field to any value via script it will fire the calc.

                         

                        Hmm I wonder if this would also work....

                        Let (

                        [

                        _trigger = $$some_global

                        ];

                        the calculation

                        )

                        • 9. Re: Refresh value of Unsorted Calculation
                          Nehme

                          Unfortunately, you are right.

                          And I don't know which will be faster, using Loop , or using ReplaceFieldContent (for large amount of records).

                           

                          Unfortunately also, using a global field (even in the Let function), converts the file to an Unstored Calculation (which won't be able to be used in a relationship dedicated for filtering ValueList

                          => won't work (I tried it).

                           

                          The global variable is an attractive idea.

                          But didn't work either

                          • 10. Re: Refresh value of Unsorted Calculation
                            DavidJondreau

                            Loop/Set Field and Replace are probably the same amount of time. The majority of time is spent loading each record and both methods require that.

                             

                            There may be another way to do what you want though. Generally speaking you should shy away from large user-entered value lists with lots of choices (>50 in my opinion is pointless).

                             

                            Could you explain some more about what the tables and records represent and what the values are for each different value list and what you want the user to experience? Maybe there's an entirely different way to accomplish the same result.

                            • 11. Re: Refresh value of Unsorted Calculation
                              Nehme

                              It won't be a large list for each user.

                              But if we have many users, the number of records might grow.

                               

                              In fact, there is a table "Letters".

                              Each user will create his own letters.

                              Then he must be able to see the list of letters he created (to be able to choose one)

                              => it is not important to see all the letters in the list, but only his letters

                              • 12. Re: Refresh value of Unsorted Calculation
                                DavidJondreau

                                It's not clear where the value list comes into play here.

                                 

                                My gut is telling me you don't need to update child records on the fly. It's unclear why you think you need to.

                                 

                                • 13. Re: Refresh value of Unsorted Calculation
                                  Nehme

                                  Sorry for the delay.

                                  And sorry for missing some info.

                                  In fact user can see his own letters, and the letters Admin gives him privilege to view

                                  => Each letter might be viewed by some users but not by others.

                                  And this is why a calculation is needed to assess the access privilege for that letter.

                                  • 14. Re: Refresh value of Unsorted Calculation
                                    dtcgnet

                                    Nehme, let's say that each user has his own FileMaker account. Let's say that they all have been assigned the same privilege set, "Users". The most secure, and the most easily maintained method, is to use FileMaker's built in Security privileges.

                                     

                                    Let's say my account name is "dtcgnet". You can go into Security. Into the "Users" privilege set, and then in the "Records" area of when you're editing the Users privilege set, you click on the Letters table. In the View column, you select "Limited...". In the formula area, you enter Get ( AccountName ) = FieldThatControlsAccess.

                                     

                                    Something like that. In the "FieldThatControlsAccess", you don't use a calculation. You make the field a Text field, and you auto-enter "Get (AccountName)". The user will automatically NOT be able to see any letters that have a different AccountName in that field. Don't let the users SEE the field or edit it.

                                     

                                    I think if you explore that, you'll find it works great and is easy to maintain and set up. See the screenshot below.

                                    Record Level Access.tiff

                                    1 2 Previous Next