13 Replies Latest reply on Sep 30, 2010 11:05 AM by philmodjunk

    Calculation for Date

    mgxdigital

      Title

      Calculation for Date

      Post

      Hello,

      I'm wondering how to write a calculation for a calc field.

      I'm looking for it to:

      Look at the first portal line in a relating portal and if the date field on that line is older than 30 days from the current day....then put the word "call" in additional text or calculation field.

        • 1. Re: Calculation for Date
          philmodjunk

          Assuming your portal does not have a different sort order specified from the relationship used to link the portal records to your Layout's table occurrence:

          If ( ( RelatedPortal::DateField + 30 ) < Get ( CurrentDate ) ; "Call" ; "" )

          Use your table and field names in place of RelatedPortal::DateField

          • 2. Re: Calculation for Date
            mgxdigital

            Thanks for answering so quickly!

            For some of the dates, this works and for some, it doesn't. I'm not seeing much of a pattern either.

            For instance, one record the date shows 12/4/09 and the field does not show "call" but then the ver ynext record, the date is 7/8/10 and it shows "call". Both are over 30 days old, but only one works correctly.

            Any ideas?

            • 3. Re: Calculation for Date
              philmodjunk

              But are these dates from the "first line in the portal"? Is there a filter expression set on the portal?

              The calculation refers to the "first" related record--which will be the first record in the portal if the portal does not have a filter expression and does not have different sort order specified from the relationship.

              • 4. Re: Calculation for Date
                hbrendel

                Is the datefield a real 'Date' field? In other words: is it of the type 'Date'?

                • 5. Re: Calculation for Date
                  LaRetta_1

                  If ( ( RelatedPortal::DateField + 30 ) < Get ( CurrentDate ) ; "Call" ; "" )

                  but you said originally only evaluate the first related record and then you said, "For some of the dates, this works and for some, it doesn't."

                  The calculation above will always only evaluate the first related record.  The calculation should be in the portal table and it should be set to 'do not store calcululation results' in the Storage Options for the calculation.  In this way, it will evaluate, since it has nothing to do with the parent record.

                  • 6. Re: Calculation for Date
                    mgxdigital

                    Thanks everyone!

                    I did not have the calculation in the portal layout, but now i do.

                    Now, the calcuation is showing "call" on any portal that has a date at all. If the portal is empty, it does not show "call".

                    The Date Field is not a "Date" Type. It is a Calculation showing a result of a Date. 

                    • 7. Re: Calculation for Date
                      philmodjunk

                      You'll need to provide a few more details. Is the calculation field defined in the Portal's table or the table referred to in Layout setup... of this Layout?

                      Is it stored or unstored?

                      • 8. Re: Calculation for Date
                        mgxdigital

                        The portal is in a layout called Sales Manager

                        The portal shows job information that pulls from teh Job Ticket table.

                        This job ticket table has all the job info except the ship date.

                        The ship date is in a table called packing slips.

                        I made a calculation date field in the job tickets in order to pull the ship date from the packing slips so that the date was in the job ticket table so that i could display it in the portal.

                        It is unstored.

                        • 9. Re: Calculation for Date
                          philmodjunk

                          Please post the exact expression you're using here. And to repeat: Is the calculation stored or unstored?

                          • 10. Re: Calculation for Date
                            mgxdigital

                            The Date Field is:

                            Unstored, from Job Ticket, = Packing Slip::Ship Date

                            Calculation result is Date.

                            The portal is showing records to "Job Ticket" 

                            Ship Date (above field) and other job ticket fields....

                            • 11. Re: Calculation for Date
                              philmodjunk

                              I'm looking for your calculation that returns "Call" if the date is past due... ;-) and we need to know if it is Unstored.

                              • 12. Re: Calculation for Date
                                mgxdigital

                                Oh....it's the calculation you posted and yes, it's unstored.

                                Calculation         Unstored, from Job Ticket = If ((Ship Date + 30) < Get (CurrentDate) ; "Call" ; "" )

                                • 13. Re: Calculation for Date
                                  philmodjunk

                                  I'd place the ship date field next to the above field in your portal to confirm it is really accessing the date you think it is accessing. I suspect the relsationships involved are causing the ship date calculation field to return a date different from what you are expecting.

                                  If that doesn't work, you may need to upload a copy of your file to a share site so we can download a copy and take a look at it.