7 Replies Latest reply on Jul 18, 2010 12:59 PM by LaRetta_1

    30, 60 day warnings



      30, 60 day warnings


      one table in my database deals with machines offered to us by salespeople. it has a offer date field which allows for the sorting of offers by date, but what i need to do is create a report that shows me all the offers older than 30 days, as these offers may not still be valid and will need to be checked out with the salespeople who made them. i would then like the option of either selecting "Checked" or "Not Checked" on each offer, if I select the former, then in 30 more days i will get teh same warning, but if i select not checked, it will go onto a new report which lists all machines 60 days or older with "Not Checked" selected in the 30-day warning. please advise, thanks. 

        • 1. Re: 30, 60 day warnings

          You've got several issues here. To find a record with a date 30 days older than today in a script do this:

          Enter find mode[]
          Set Field [Yourtable::datefield ; "< " & Get ( CurrentDate ) - 30 ]

          To handle the "checked/not checked" issue, I'd define two fields. A date field and a Checked field (text). Format the Checked field as a radio button or popup menu. Set up the date field to auto-enter the current date when the record is created. Use an OnObjectChange script trigger to run a script that uses Set Field to enter the current date in this field if the value of the field is "Checked".

          • 2. Re: 30, 60 day warnings

            For example if after 30 days I select "Checked" for a machine, is there a way that after another 30 days this field automatically empties (so that I will need to select Checked/Not-Checked again)?

            • 3. Re: 30, 60 day warnings

              I misread your original post. The script should update the date field with the current date if it is marked "unchecked". That date update is intended to change which records are found the next time you do an "older than 30 days" find.

              • 4. Re: 30, 60 day warnings

                Why? If it is "Unchecked", shouldn't the date remain the same so that it is found in the 30+ list? If it is "checked", then I wouldn't want to see it again for another 30 days so that's when the date should be changed. Speaking of which, how do I create the script that changes the date to the current date? I cannot find a Current Date function, and also there is no OnObjectChange trigger as far as I can tell.

                • 5. Re: 30, 60 day warnings

                  actually that script is not an issue, it works fine, but there is a problem in that if i select "checked" and the date field changes to current date, the "checked" field remains "checked", so in 30 days time, it will show up still as "checked". same goes for the 60-day list. is there a way for the "checked" field to clear after if the date field is 30 days ago or older? so that if i select "checked" today, it will remain "checked" until 30 days from now?

                  • 6. Re: 30, 60 day warnings

                    You could create an unstored calculation field that uses the contents of the "Checked" and the current date to "flag" such records and you could search for such flagged records. But the checked field would still show "checked".

                    • 7. Re: 30, 60 day warnings

                      If an offer has been checked verified within 30 days or if the offer was made within 30 days then it is OK.  It would work best to eliminate these records from the thinking first.  Then if an offer is older than 30 days (even if 60 days old) then it should be checked verified again.  You also want a report if an offer is 60 days old or greater.

                      This can be easily accomplished by using one additional date field called ExtendDate.  When you verify that an offer is still good, insert the current date into ExtendDate.  You then test the above rules against this:

                      Max ( OfferDate ; ExtendDate )

                      I've provided a sample file which shows how this would work in the real world from User perspective.  The c_Offer_Status field isn't required for the functionality; it is simply to make the demo easier to view.  Both black buttons run the same script.  The only difference is that the first has 30 attached as a script parameter and the second has 60 attached.

                      You may download the sample file here:  http://www.4shared.com/file/PWPfNf6u/Warnings.html

                      NB: Keep in mind that this process will not provide an audit trail of when or how often the Offer was checked.  If you need a trail then you should create records in a related table each time you run a check.  But I did not see it necessary for this process since you only want to keep up to date on machine offers.

                      UPDATE: One problem is using the term 'checked.'  Checked implies that a record receives a check mark and that is not what we want td do nor need to do.  Instead, we need to VERIFY the offer again.  When re-reading this entire thread, I see a potential confusion between terms and even if my demo, I used a button to 'check' the offer when it should be re-verify instead.  You could also just use the SAME original field and keep changing the date to the current date when it has been re-verified but I prefer to leave the original offer intact.