1 2 Previous Next 16 Replies Latest reply on Jul 15, 2016 8:47 AM by tomperr

    Calculation for Dates within portal conflict

    tomperr

      I have field dependencies set up where dates trigger calculations of other dates based on the relationships.  In an effort to keep this simple, I have an if statement calculation similar and saying:

       

      if (Sched::Identity = "Blue" ; ProjX::StartDate (this line works flawlessly)

      ;  SchedD::EndDate + Phase::Days)

       

      Thru a dropdown/script trigger, the portal will show 1 "Blue", "Green", "Red", all under 1 project (1 common fk).

      "Green" uses dates from the "Blue" record and "Red" uses dates from the "Green" record in the calculation.

      The problem is when another project is created, there will be another "Blue", "Green", "Red".

      In the 2nd project added, the "Green" is now using dates from the "Red" in project 1 rather than project 2 as it should. 

      The same with "Red" from project 1 using dates from project 2.

       

      How could I write a calculation that keeps the colors looking for dates only within the same project?  All color fields have a common fk that ties to the project table pk.

        • 1. Re: Calculation for Dates within portal conflict
          ninja

          Not sure what you mean by "Thru a dropdown/script trigger, the portal will show 1 "Blue", "Green", "Red", all under 1 project"...but I'm not sure it matters either.

           

          Filter your relationship by project color AND by project...that way you are only associated with the related child records you want.

          filtering your relationship will avoid getting crosstalk between your colors.

          • 2. Re: Calculation for Dates within portal conflict
            keywords

            By the look of your calculation, you have four TOs involved: Sched; ProjX; SchedD; Phase. That's a fairly complex network. Check to make sure the relationships are all properly set up. Also, you mention using a portal; you will have to make sure the record on each row is able to independently relate to its correct context—it sounds like this may not be happening.

            • 3. Re: Calculation for Dates within portal conflict
              BillisSaved

              Good afternoon tomperr,

               

              I hope your day is going well. I'm not sure I fully understand your example, so this idea may not work, but I would try using ExecuteSQL to populate a variable and than use GetAsDate (variable) to set the appropriate field. For example:

               

              Let ([

               

                   ~fkID = Your foreign key field

                   ~Green = ExecuteSQL (

                                            "SELECT \"date information field\"

                                            FROM \"Sched\"

                                            WHERE \"Identity\"=? AND \"Your foreign key field name\" = ?";

                                            ""; ""; “Blue"; ~fkID)

                   ];

               

                   GetAsDate (~Green)

               

              )

               

              Hopefully this is helpful. If it doesn't accomplish your goal, you may want to post a more detailed example or sample file. Good luck. Have a great day!

               

              God bless,

               

               

              Bill

              • 4. Re: Calculation for Dates within portal conflict
                tomperr

                Thanks for the feedback.  Maybe there is an easier way to ask the question.

                How can I compose a calculation for records in a portal based on another record within the same portal.

                ex:

                3 records with 3 fields.  (colors, date, calculation).

                Color     Date          calc

                "Red"     1/1/16         

                "White"     2/1/16

                "Blue"     3/1/16

                 

                The calc field would say, if color is red, then "", if color is white return red's date of 1/1/16, if color is blue return white's date of 2/1/16,

                • 5. Re: Calculation for Dates within portal conflict
                  keywords

                  I must confess i don't follow your logic, but that doesn't matter. What I suspect, though, is that you are confusing yourself by thinking through a portal. To simplify the issue, disregard the fact that you are looking at a portal. All the portal rows are separate records in the same table, so look at the issue from the point of view of that table. This means that what you want to do is calculate the value of a field based upon the value in another field—easy, except that you want FM to refer to that other field IN A DIFFERENT RECORD. That can also be done, but you have to provide FM with a way to decide which other record to refer to. To do that, you need to set up a self job relationship—where the TOs on each side of the relationship are from the same table: you want this record in this table to relate to this other record in this same table.

                  • 6. Re: Calculation for Dates within portal conflict
                    BillisSaved

                    Good evening tomperr,

                     

                    I'd like to try to restate your goal, as I understand it. It appears to me after re-reading your initial post and the clarification you just provided, that the colors reflect some sort of project status (e.g., blue = the project start date). Is that correct? If that is correct, would it also be accurate to say that the the other colors - red and green - use that project start date in a calculation to determine another project related date (e.g. green = (project start date + or - some amount))? If the answer to my second question is also yes, then I still believe that a possible solution may lie in the ExecuteSQL() function. The difference would be in the reference you use to select the appropriate project date. For example:

                     

                    Let ([

                     

                         ~Start = ExecuteSQL (

                                                    "SELECT \"StartDate\"

                                                      FROM \"ProjX\"

                                                      WHERE \"_pkID\" = ?";

                                                       ""; ""; PortalTableName::_fkProjXID);

                         ~ProjStartDate = GetAsDate( ~Start)

                    ];

                     

                         Case (

                                   Sched::Identity = "Blue"; ~ProjStartDate;

                                   Sched::Identity = "Red"; Your calculation;

                                   Sched::Identity = "Green"; ~Your calculation;

                                   Your calculation when there is no match

                         )

                    )

                     

                    This is just a sample calculation, which you will have to tweak to make work for your specific application. Of course, I may have totally misrepresented your goal, in which case I apologize for wasting your time. Good luck And have a great night!

                     

                    God bless,

                     

                    Bill

                    • 7. Re: Calculation for Dates within portal conflict
                      BillisSaved

                      Good morning tomperr,

                       

                      I woke up this morning an realized that in the calculation example I provided I failed to account for the format in which the ExecuteSQL() function will return the date information, which I believe is Unicode/SQL (I.e. YYYY-MM-DD). If you choose to implement ExecuteSQL() to retrieve date information via a Let() function, as I have described, you will need to replace the second variable declaration with something like the following:

                       

                      ~ProjStartDate = Date (

                                                          Middle ( ~Start; 5; 2);

                                                          Right (~Start; 2);

                                                          Left ( ~Start; 4)

                                                          )

                       

                      Hopefully this is helpful. Have a great day!

                       

                      God bless,

                       

                      Bill

                      • 8. Re: Calculation for Dates within portal conflict
                        jameshea

                        I agree with Eric. Since you are using relationships in a portal, you simply need to add a global field to filter the portal based on a matching projectID. Your calculation should then work as is.

                         

                        Hope this helps.

                         

                        James

                        • 9. Re: Calculation for Dates within portal conflict
                          tomperr

                          Hi Eric-

                          Your assumptions are correct.  I fear the problem is in the relationship. 

                          The portal records are based on the Schedule table.  The phases table provides the phase (color) dependencies.  The Sched_depend tables is the schedule table with dates to dependency which are used in calculation.  This is where the problem exists.  The calculation works if there is only 1 project schedule.

                          The SQL function is completely foreign to me.

                          • 10. Re: Calculation for Dates within portal conflict
                            tomperr

                            Hi James-

                            See note I just posted.How would you suggest revising the relationship?

                            • 11. Re: Calculation for Dates within portal conflict
                              ninja

                              Howdy,

                               

                              I think we need more perspective on the whole situation.

                               

                              Do all projects go through the same color stages?

                              What is the order of color stages?  Does it ever change order?

                              Is there a reason you use color instead of "Stage 1", "Stage 2"  (this isn't really important...just curious)

                               

                              How many color stages are there?

                               

                              From what I'm seeing, it might be best to simply keep it all in one table and jettison the portal altogether.

                               

                              Four stages, always the same colors in the same order...put stages 1-4 in your project table and have done with it...your math/logic will be a lot easier.

                              • 12. Re: Calculation for Dates within portal conflict
                                jameshea

                                if (Sched::Identity = "Blue" ; ProjX::StartDate (this line works flawlessly)

                                ;  SchedD::EndDate + Phase::Days)

                                 

                                Tom,

                                 

                                Do you have more relationships? (i.e. something called "Sched" and "SchedD") Can you show these on your screenshot as well?

                                 

                                Thanks,

                                 

                                James

                                • 13. Re: Calculation for Dates within portal conflict
                                  tomperr

                                  Hi Eric-

                                  For this exercise, all projects will go through the same color stages.  Dates change per project. 

                                  After this is working, I will provide an option to insert custom colors (stages) without dependencies.

                                  Stage 1, 2,.. etc can be substituted for colors.  I was trying to simplify question using colors.

                                  There are 45 color stages.

                                  Can you provide an example of how "jettison the portal altogether" would work?

                                  The stage order may change based on an alternate input field.

                                  Thanks for input.

                                  • 14. Re: Calculation for Dates within portal conflict
                                    tomperr

                                    View of relationship w/ ProjX as parent is:

                                    Other relationship not shown have other functions withing the various layouts.

                                    Thanks for suggestions.

                                    1 2 Previous Next