6 Replies Latest reply on Feb 28, 2017 11:58 AM by user17152

    Need help with calculated repeating field


      I'm sure this is easy, but I can't seem to figure it out!  When setting up a calculated repeating field, how do I specify the calculation for each repetition?  I'll spare you all the details, but this field will be used for filtering purposes with SoSIMPLE Calendar.  It needs to be repeating.  A multiline key field will not work, nor will related records.  I need to gather several bit of information stored in other fields within the table and store them in a single repeating field for the filter to work.


      Thanks for the help!

        • 1. Re: Need help with calculated repeating field

          In a repeating calculated field, the same basic calculation has to be used in every repetition. The "inputs" can differ as you can refer to the values of another repeating field or use Get ( CalculationRepetitionNumber ) to either reference different data or, inside a case function, do different calculations in different repetitions, but it's all one expression.


          An alternate approach might be to use a script with set field steps that assign values to a repeating field that is not a calculation field. The calculations would be performed in the calculated result parameter of the set field steps.


          I'll spare you all the details

          And those details are key. Without them, our ability to help is also limited.


          It needs to be repeating.  A multiline key field will not work, nor will related records.

          You may be correct, there still are cases where a repeating field is useful, but I've seen many people insist on a repeating field when their reasons don't actually hold up. Most of the time, a repeating field is not the best option.

          1 of 1 people found this helpful
          • 2. Re: Need help with calculated repeating field

            Trust me, soSIMPLE requires a repeating field to do what I want.  I've spoken with the developer about it.  Honestly, I never use repeating fields because I find them kind of strange, but this is an instance where I have to use them to do what I want.


            Basically I need to insert several values into the repeating field.


            Rep 1 = A

            Rep 2 = B

            Rep 3 = C


            There will always be three repetitions and each repetition will always contain data.  I'll be happy to further explain what I'm doing and why, but it doesn't really effect my question.  I just need to understand how to set up the calculation.  I've considered using a script as you suggest.  I actually do that for another repeating field (also for soSIMPLE) because a calculation simply will not work in that scenario.


            So, if I have a repeating field with 3 reps specified, how do I get 1 = A, 2 = B, and 3 = C?


            Thanks much for the reply!

            • 3. Re: Need help with calculated repeating field

              Set Field (myrep[1] ; "A")

              Set Field (myrep[2] ; "B")


              does that help? the Set Field dialog allows you to specify the repetition (my example may not be quite accurate).



              • 4. Re: Need help with calculated repeating field

                I gave you two options in my original post. Beverly has explained one in more detail--using a script with set field steps.


                As a calculation, you could use:


                Let ( R = Get ( CalculationRepetitionNumber ) ;

                        Case ( R = 1 ; "A" ;

                                    R = 2 ; "B" ;

                                    R = 3 ; "C"

                                  ) // Case

                        ) // let


                This is the other option that I mentioned in my original post. It would actually be pretty silly to do it this way if you just need simple data in your repetitions, but I'm guessing that you actually need something more sophisticated than "A, B, C" here in your repetitions.


                And the extra details that I was referring to was not whether or not a repeating field should be used but rather what calculations you might need in your repeating field as you told us nothing about them in your original post.

                1 of 1 people found this helpful
                • 5. Re: Need help with calculated repeating field

                  Thanks for the reply beverly.  That would work fine in a script, but I'm hoping to accomplish this via an indexed calculation field.  Ok, a little further information.  As philmodjunk said, details are key, so I'll share a few more.


                  I'm integrating a fantastic calendar solution called soSIMPLE Calendar.  My need for a repeating field stems from my use of this product.  It's a PHP calendaring solution that offers a really nice calendar in Filemaker via a web viewer, plus allows Filemaker calendars to be shared as standard Internet calendars.


                  soSIMPLE has a really powerful URL filtering feature.  Once the calendar is configured, a URL is generated.  This URL is used by the web viewer to display the soSIMPLE calendar in Filemaker and is also the URL used to subscribe as an Internet calendar in apps like Apple Calendar and Outlook.


                  What makes soSIMPLE really awesome is that this URL can now be amended in order to filter the found set.  Whereas other calendar solutions I've looked at require a new calendar setup for each set of filtering criteria, soSIMPLE lets you filter in the URL.  I have one calendar set up.  Its source is my entire Events table, every event, regardless of user, project, etc.


                  Now I want to display a calendar of only my active projects.  I take the base URL and add &user=rob&status=active.  Boom.  Filtered.  I've built an interface for my users to create their own custom calendars that can be saved and/or subscribed to.


                  So why do I need repeating fields?  soSIMPLE's filtering is absolute, the equivalent of a Filemaker == find and filtering on related records is not supported.  The field being filtered must be a field within the table and either IS the value or is NOT.  There's no "contains."  This makes using a multiline field impossible.  If the field is repeating, however, with each value in its own repetition, filtering works!  Furthermore, you can filter on multiple values (OR, not and) within the field by separating values with "|", ie: &user=rob&status=active|complete.


                  Events in our system can be assigned to a project, a project area, a project phase, and/or a project manufacturer.  Using phase as an example, I'm trying to create a calculated repeating field for the purpose of filtering.  There's a UI that uses global match fields to show the filtered list of event records in both list and calendar view.  If the user is viewing "all projects" or "my projects", the phase filter match field on the UI side of the relationship is set to "X" to match all phases.


                  The way I see it (and this is a work in progress), I need three filter values in the repeating field on the Event side in order to cover all possible scenarios.  The first value is the Project ID prefixed with the letter "P", ie: P123.  The second value is the phase ID, prefixed with "PH", ie: PH789.  The third value is "X" for "show all".


                  If the user is viewing "selected projects", he or she may add specific projects to the project source filter field.  In order to limit the phases being viewed to only the specific projects, when viewing "selected projects", the "X" for show all is removed from the match field.  Each time a project is added to the "selected projects" list, the phase filter match field will be updated with "P" and the project ID.


                  I'm giving the users a "power user" feature here and allowing them to select only the phases they wish to view within a specific project.  Let's say the user only wants to view Phase 1 in Project A.  The user clicks "Phase 1" in my "focus" pop-over that lists all phases within the project.  The "P" and project ID value is removed from the phase filter source field and replaced with "PH" and the specific phase number.  Now I continue to show all phases for the other projects and only the selected phase for Project A.


                  I really hope that made sense without some visual aids!


                  Based on how the filter works, I need those three values in my repeating field.  The project ID and phase ID are already stored in the Event record.  So I need a calculated repeating field that takes those values and adds the prefixes:


                  Rep 1 = "P" & Project ID

                  Rep 2 = "PH" & Phase ID

                  Rep 3 = X


                  I don't want to have to do this via a script if possible.  I hope all of that made sense!  Thanks again for the help.

                  • 6. Re: Need help with calculated repeating field

                    Thanks.  That's what I was looking for.  Sorry, I didn't really understand how to use the Get ( CalculationRepetitionNumber ) function and just needed some further clarification there as I've never worked with repeating fields.  I tried to construct a Let statement as you describe on my own but obviously didn't do it correctly and was looking for someone here to explain the proper syntax.