10 Replies Latest reply on Sep 16, 2016 3:43 PM by cmayerle

    Repeating field calculation problem

    cmayerle

      I'm having problem with the following repeating field that is supposed to get a text field (MSN_full_related::msn_mission_number) from a cartesian related record based on a calendar start date:

       

      Let(eval_date=Extend(cal_start_date)+Get(CalculationRepetitionNumber);


      If(

      eval_date > Extend (MSN_full_related::msn_dep_day)

      and

      eval_date < Extend(MSN_full_related::msn_return_day) + 2;


      Extend(MSN_full_related::msn_mission_number)))


      Precisely, the calculation will only return the value on the first record that was created, which is usually not the first repetition.  If the date range of the first created record goes for several repetitions, it will fill in each repetition.  It just won't evaluate records other than the first created one.  None of the date ranges overlap.

       

      Any help would be appreciated.

       

      Thanks,

      Chris

        • 1. Re: Repeating field calculation problem
          bigtom

          I sounds like you are using the wrong relationship operator. Seeing the first related record repeated is one sign of this. Why is  the relationship cartesian? Maybe I am not understanding the situation you are explaining.

          • 2. Re: Repeating field calculation problem
            cmayerle

            bigtom,

             

            Thanks for the reply and I think you're right.  I have 3 tables (actually more, but the other ones are simple relationships).FM Database.jpg

            CAL is just a calendar where you can choose a goto_day and it will start the calendar from the previous Sunday.  cal_dates is a repeating field with 7 repetitions.

             

            It is related to the LEG table through a Cartesian product relationship.

             

            LEG is related to MSN table through an id_number (auto-generated).

             

            Chris

            • 3. Re: Repeating field calculation problem
              bigtom

              What are trying to accomplish with the cartesian relationship? Why is this not "=" ?

              • 4. Re: Repeating field calculation problem
                cmayerle

                It was an "X" rather than "=" because I can have multiple legs on different days related to single msn_id_number. 

                 

                cal_dates is a repeating field (7 reps) and I have portals assigned to each day that displays LEG table info.  In a separate repeating field beneath the portals, I'd like to have a gantt-like chart that shows which msn_mission_number (through the original calculation above) is associated with the legs from a start date (msn_dep_date) to an end date (msn_return_date).  These dates are not necessarily contiguous, but I'd like it to fill in gantt-like.

                 

                Thanks again.

                FM Database 2.tiff

                • 5. Re: Repeating field calculation problem
                  bigtom

                  It is starting to sound like you might need a join table between CAL and LEG rather than a Cartesian relationship.

                   

                  Multiple days for the same leg and multiple legs on the same day sounds like a join table.

                  • 6. Re: Repeating field calculation problem
                    erolst

                    If you're in doubt what related set a given relationship will return, put a portal using the related TO onto the layout, or use the data Viewer with

                     

                    List ( TOInQuestion::primaryKey )

                     

                    and see what (and how many) you get.

                    • 7. Re: Repeating field calculation problem
                      cmayerle

                      erolst,

                      I created a portal and the correct results were there.  Then, just to check, I created 7 separate portals side-by-side, each filtering with the original calculation but for one day only in the week.  And it works.

                       

                      As inelegant as this seems, I'm just going to run with it.

                       

                      Thanks to both you and bigtom.

                       

                      Chris

                      • 8. Re: Repeating field calculation problem
                        bigtom

                        I think the goal you would look for is the correct records showing up in the portal without filtering.

                         

                        If you use the "x" join you will get all records in the portal and need to filter.

                        • 9. Re: Repeating field calculation problem
                          cmayerle

                          I changed the relationship from a cartesian to a >= for the dates.  I get the same result with the repeating field (only the first created record displays) and with the portals workaround (they work correctly). 

                          • 10. Re: Repeating field calculation problem
                            cmayerle

                            Ok, I got it figured out.  To show it all on the same repeating record, you have to do a repeating summary field of the original repeating field (cal_aircraft_activity) with repetitions calculated individually as a list.  The first created record was the only one that showed up because each record after that was in it's own separate related record. 

                             

                            erolst, your suggestion of using a portal to check my results was what led me to this.  Thanks.