2 Replies Latest reply on Jan 23, 2015 11:57 AM by thong127

    Expired this month

    thong127

      Title

      Expired this month

      Post

      Hello All,

      Please help me with my problem below

      I have a 2 tables

      1. Dashboard (Fields below):

      Field Name                                           Type                                       Comments

      a) Constant today                                      Calculation                      Get ( CurrentDate )

      b) Expiring this month                                Calculation                     Let (

      [AlertCount = Count ( Dashboard::id_constant ) ] ;

      Case (

      AlertCount = 0 ; TextColor ( "0" & " Material expiring today! " ; RGB ( 120 ; 120 ; 120 ) ) ;

      AlertCount = 1 ; TextColor ( AlertCount &  " Material expiring this month! " ; RGB ( 0 ; 136 ; 0 ) ) ;

      AlertCount >1 ; TextColor ( AlertCount &  " Materials expiring this month! " ; RGB ( 220 ; 0 ; 0 ) ) ;

      )

       )

      c) id Constant                                             Calculation                            1

      2. Materials 

      Field Name                                                    Type                                    Comments

      a) Expiry Date                                                Date

      b) ID Constant                                                 Calculation                             1

       

      The two tables are connected by a relationship id Constant = ID Constant and Constant today = Expiry Date

      Now, my problem is I want to get the number of materials that expiring this month using the field Expiring this Month from my Dashboard Table.

      Any help is highly appreciated

        • 1. Re: Expired this month
          philmodjunk

          I would expect Alertcount to always have a value of 1 as I would expect  Count ( Dashboard::id_constant ) to always return the value 1 since it is defined in Dashboard.

          Count ( Materials::ID_Constant)

          On the other hand, will count all records in Materials linked to the current record in Dashboard.

          Dashboard must have at least one record in its table.

          Constant Today needs to be an unstored calculation field with Date as the return type. (A stored calculation won't update with each new day)

          And the Dashboard layout cannot be left open past midnight unless you do something to refresh the calcualtion or it will still show the previous day's date.

          I don't see any purpose for the constant id fields as you are actually matching records by date.

          • 2. Re: Expired this month
            thong127

            Hi Phil,

            I don't see any purpose for the constant id fields as you are actually matching records by date.

            Do I need to change my relationship?