6 Replies Latest reply on Sep 6, 2012 7:15 AM by thong127

    Relationship

    thong127

      Title

      Relationship

      Post

       Hello All,

      Kindly Help me figure out what is the right relationship with my database. in my GUI_Product Table I have a calculation field Expiry_30_Days. I would like this field to warn me the number of Products that will be expiring in the next month. I tried to put some Products that will be expiring in the next month but its now working. Kindly help me to figure out how this will gonna work. attached is the link of my file to help you figure out. thank you very much. http://www.4shared.com/file/KFgAXaaX/Supplies_Copy.html

        • 1. Re: Relationship
          Sorbsbuster

          I would set the search criterion to be:

          "<" & ( Get ( CurrentDate )  + 30 )

          You were looking for any items expiring in the next calendar month, that is 1/10/2012...31/10/2012

          (I never thought that tecnhique would work, so I've learned something!)

          • 2. Re: Relationship
            thong127

             Hi Sorbsbuster,

            Pardon me because I didnt get your point, where Im gonna put this "<" & ( Get ( CurrentDate )  + 30 in my dbase?

            • 3. Re: Relationship
              philmodjunk

              In a script:

              Enter Find Mode[]
              Set Field [YourTable::YourDateFIeld ; "<" & Get ( CurrentDate )  + 30 ]
              Set Error Capture [on]
              Perform Find[]

              • 4. Re: Relationship
                thong127

                 Thanks Phil,

                How about my Expiry_30_Days field in my GUI_Product Table...what will be  relationship to give me a correct number of expiring product for the next month? thank you.

                • 5. Re: Relationship
                  Sorbsbuster

                  I couldn't understand the calculation you had:

                  If( IsEmpty (Expiry_Date) and not IsEmpty (Expiry_Date) ; 0 ;1 )


                  ...so I assumed it was the script 'Next Month Expiring Product' that you were referring to.  When I looked at it it is searching for records where the expiry date is during the next calendar month.  I was suggesting you changed the search criterion in it.

                  • 6. Re: Relationship
                    thong127

                     Hi Sorbsbuster,

                     I tried to put "<" & ( Get ( CurrentDate )  + 30 ) in my search criteria it gives me all the products that was expired from 2006 - to 2012 (past months.) I used this as my basis "if( IsEmpty (Expiry_Date) and not IsEmpty (Expiry_Date) ; 0 ;1 )" that if the expiry date is empty in my field (Expiry_30_Days) in my GUI_Product table.