8 Replies Latest reply on Sep 23, 2014 1:34 PM by themactech.

    Trigger Field After Set Date

    themactech.

      Title

      Trigger Field After Set Date

      Post

      Hi everyone

      Trying to figure out the best way to do this. 

      I have two fields on a layout. One is from a related table. 

      Field 1. is a simple radio button.. ON.. or.. OFF. It's from a related table.

      Field 2. Is a simple date field on the current layout.

      Depending on the date.. Field 1 should change from ON or OFF. For example If the date is not greater than today's date.. then it's ON.. once the date passes.. the radio should switch to OFF.

      Right now it works, in the moment when you change the date. The radio button field changes happily.

      But I need a way for the radio button to change "on it's own" if the date is set in the future and the user isn't viewing the record.

      Is this best set with a Trigger Script when the user comes back to the record? RecordOnLoad?

      But what if the user wants to print a report to see the current status of the radio button on all records?

      Is it better to run a script when the database is open to go through ALL the records and change the status based on the date?

      I appreciate any advice on this.

      THANKS

      Clay

        • 1. Re: Trigger Field After Set Date
          philmodjunk

          Make your "on/off" field an unstored calculation field that compares the date field to Get ( CurrentDate ) in an If or case function to determine whether or not to return the value on or off. Do not use a stored calculation or an auto-entered calculation to do this or the field will fail to update when the current date changes.

          • 2. Re: Trigger Field After Set Date
            themactech.

            As always Phil.. thanks for the quick response. That seems to be the most elegant way to do it... but for some reason I can't get it to update based on the date I choose..

            Here is my field and calculation.. pretty simple

            Off-Site Status (this is a calc field from my Cylinders table)

            If ( Inspections::Planned Ship Date  ≤ Get ( CurrentDate ) ; "Yes"; "No")

            On the Inspections layout I have the Planned Ship Date field and the the Off-Site Status is a related field from the Cylinders table.

            This should still work right?

             

            • 3. Re: Trigger Field After Set Date
              philmodjunk

              It should work if the underlying relationships and match fields values are valid.

              Inspections is based on what table occurrence? and how is that occurrence linked to the others?

              • 4. Re: Trigger Field After Set Date
                themactech.

                yeah.. that's the thing. I've inherited this database so it's not quite how I would have set it up. But it seems to work for the most part.

                There are two tables: Cylinders and Inspections.

                There are only two TOs.  Cylinders -->Inspections

                Cylinders has a pk called Cylinder Numbers related to an fk in the Inspections called Cylinder Numbers. The numbers are unique.

                 

                 

                • 5. Re: Trigger Field After Set Date
                  philmodjunk

                  Then this should work as long as this is a field of type calculation (not a text field with an auto-enter calculation) and your current Inspections record is linked to the correct record(s) in Cylinders (the cylinder number fields must have matching values)

                  But this calculation field would show the same value in every related record in cylinders so I'm not sure why it would need to be defined in the Cylinders table as you describe:

                  Off-Site Status (this is a calc field from my Cylinders table)

                  If ( Inspections::Planned Ship Date  ≤ Get ( CurrentDate ) ; "Yes"; "No")

                  If you defined it in Inspections as a calculation field, this would make more sense to me, but then you'll need to click the "storage options" button and specify the "do not store..." option in order for it to update automatically. (If defined in Cylinders, the reference to a field in Inspections automatically turns it into an unstored calculation provided that it's not an auto-enter calculation.

                   

                   

                  • 6. Re: Trigger Field After Set Date
                    themactech.

                    i guess the best way to describe it is this.

                    There are individual cylinders.. each with a unique number. We have to track whether or not each one is off site or on site based on a record on the Inspections layout. A cylinder can have many Inspections... they come and go from the storage area. They would like to see at a glance.. on the cylinders layout and on the inspections layout (when they create a new inspection record) whether or not a cylinder is Off-site or not.. based on a ship date on the Inspections record

                    if that makes sense. :)

                    • 7. Re: Trigger Field After Set Date
                      philmodjunk

                      Ok, missed that detail, that Cylinders to Inspections is a one to many relationship.

                      The problem here is that:

                      If ( Inspections::Planned Ship Date  ≤ Get ( CurrentDate ) ; "Yes"; "No")

                      Will only refer to the date in Planned Ship Date of the first related record in Inspections. All dates in subsequent inspection records will be ignored.

                      This should be an unstored calculation field defined in inspections so that you will see a different result of "yes" or "no" for each inspection record.

                      • 8. Re: Trigger Field After Set Date
                        themactech.

                        Thanks for your help Phil. I got it straightened out with your help. :)