5 Replies Latest reply on May 20, 2014 9:51 AM by philmodjunk

    Best scheme when you need a calculated value to be indexed?

    challengeday

      Title

      Best scheme when you need a calculated value to be indexed?

      Post

           As you may know, if you try to use a calculated field (which references a related field itself) in the right hand side of a relationship, the relationship won't work correctly because the values on the right hand side need to be indexed.

           What is the best workaround for when you need to do exactly this?

           Here is my particular example. There are three tables: Organization (Org for short), Event, and EventDay

           Orgs can have multiple Events. An Event can have multiple EventDays. A date is stored in EventDay.

           An Event has a field called "MaxDay" that calculates the latest EventDay for that Event.

           I want to have a portal on the Org that shows Events at that Org as of a certain date. 

           I created a global field in Org that is "AsOfDate" and a relationship "org_EventDayAsOfDate" that matches the Org::AsOfDate to Event::MaxDay (with a greater than or equal to operator).

           As you can guess, the portal wasn't showing the events as expected because MaxDay is a calculated field referencing a related field and can't be stored. So as a workaround I created a "MaxDayIndexed" field which is just a straight up Date field, and copied the MaxDay calculation into MaxDayIndexed. That got the portal working.

           Now... obviously MaxDayIndexed will get stale and need to be updated regularly. Ug. What's the best way to do this? An auto enter calculation that gets triggered? A script that runs nightly?

           Am I missing something? Or am I just destined to some hacky workaround?

        • 1. Re: Best scheme when you need a calculated value to be indexed?
          philmodjunk
               

                    want to have a portal on the Org that shows Events at that Org as of a certain date.

               As long as you are using FileMaker 11 or newer, you do not need to use a match field to limit the portal records to "all events as of a certain date".

               You can set up  a date field in Org and use a portal filter on the layout that omits all Event records where the date is less than ( or equal to, or greater than...) the specified date.

               That expression might look like:

               Org::CertainDate > Events::MaxDate

               To show all events where MaxDate is on or before the date in CertainDate.

          • 2. Re: Best scheme when you need a calculated value to be indexed?
            challengeday

                 Thank you Phil :)

                 Yes, we have FM 12.

                 One thing I did not mention though, I also need to export out the data for a report (so it's not just a display thing). I haven't used Portal Filters yet. Are they display only?

            • 3. Re: Best scheme when you need a calculated value to be indexed?
              philmodjunk

                   Yes, they are display only.

                   But you may be able to export your data from a layout based on the Event table with fields included from the org table. Then you need only perform a find on the MaxDate field.

                   If you truly need an indexed MaxDate field, and there are cases where this is so, you will probably end up with a simple date field that is updated via script every time data in the related table is changed in a way that requires a new date in that field for one of your Event records. Much of this can be driven by script triggers, but you'll also need to control how records in Event Days are deleted and perhaps how they can be added to be sure that a script kicks in at that time to update the field. How you do that can depend on your layout design and whether or not you have FileMaker Advanced--where a custom menu can replace standard menu options with developer created scripts which then create new records or delete them while also updating the MaxDate field.

              • 4. Re: Best scheme when you need a calculated value to be indexed?
                challengeday

                     Phil, you are awesome!

                     I will explore exporting out of the Events table and post back.

                     Do I truly need an indexed MaxDate field? Hmm...  Any advice on figuring this out? I'm assuming that if I run into a structural issue that doesn't allow me to do the export out of the Events table then I truly need it.

                • 5. Re: Best scheme when you need a calculated value to be indexed?
                  philmodjunk

                       That's basically it. From here, I see no reason why you can't export from Events instead of Org, but the devil is in the details and if one of those details trip you up--such as not being able to structure your export into the format you need when exporting from Events, then you have a plan B to fall back on.