10 Replies Latest reply on Feb 2, 2012 9:15 AM by philmodjunk

    Date Calculation for Locking Records



      Date Calculation for Locking Records


      I'm trying to tell my schedule table that it should lock records if a year prior to the current one is present in the date field.

      I'm using the method described here: http://fmforums.com/forum/topic/67811-locking-records-against-changes/ (TL;DR for the article: Using a field "Lock" on the table, you can use priviledge sets to lock the field based on wether it shows a 1 or a 0. If it displays 0, the record locks)

      The problem is, I have a lot of records and would like to automate the process. I'm trying to make the "Lock" field a calculation with a number 0 or 1 result. 

      What hasn't worked:

      If (Year (Get(CurrentYear)) -1; 0) ...and a number of variations with paranthesis in different places. I've also tried this with If (Year (2012) -1; 0) just to try and get anything to work

      It seems that however I edit this, I get either a 0 or a 1 on every record and there's no distinction between 2011 and 2012. All 2012 should be showing 1 and 2011 should be showing 0. Ideally, this calculation would lock records as the year changes. (In 2013, it will lock 2012's days).

      Any ideas? This is driving me crazy!

        • 1. Re: Date Calculation for Locking Records

          Where is the date field to which you are comparing the year of the current date?

          If it is called ScheduleDate, your lock expression can be Year ( ScheduleDate ) > Year ( Get (Currentdate ) )

          Note that 1 = True and 0 = False so when the year in the scheduleDate field is the same or larger than the current year, it evaluates as true (1).

          Edit note: reversed the logic as you will want a result of 1 to unlock your record...

          • 2. Re: Date Calculation for Locking Records

            CurrentDate isn't the current year according to my computer?  I thought that would just plug in whatever the current. Do I need to create a field to reference that constantly displays the current date? 


            The field on the schedule that displays the date is called Show_Date and has date records spanning 2011 and 2012.


            Also-- I thought this tutorial has it set up in reverse. The records that don't display a 1 are locked. (The lock field has a data auto enter of 1) That's why I set it up that way. Is that incorrect?

            • 3. Re: Date Calculation for Locking Records

              Yes it is the date for today, which includes the year. But none of the examples you posted compared that value to the value in Show_Date.

              Year ( Show_Date ) > Year ( Get (Currentdate ) )

              Should lock all records where Show_Date is from last year or earlier. (I'm trying to make this more flexible so that records from 2011 don't unlock when we reach 2013...)

              This assumes that Show_Date is a field of type date.

              • 4. Re: Date Calculation for Locking Records

                That works, but changes everything until today. I want to have everything in 2011 be 0 and 2012 and beyond be 1 so I can lock the 0s. This makes it so that January 31st, 2012 is a 0 and today is also a 0. Unless I input a date in 2013, it doesn't display as 1.

                • 5. Re: Date Calculation for Locking Records

                  There's no need to put this in a calculation field. Use it as your lock expression in manage security.

                  January 31, 2012 in Show_date should not lock that record. Only records where the year is less than this year.

                  Keep in mind that the expression uses greater than or equal, but due to how I posted that text, it won't copy and paste correctly and will paste as >. If you pasted my example, you'll have to edit the inequality operator to get greater than or equal.

                  • 6. Re: Date Calculation for Locking Records

                    That worked. I modified to lock my portal records as well. As to be expected, it does not prevent me from adding a new portal row, just locks the existing ones from editing.  In a previous post you suggested:

                    "This is a known limitation. You can set up field validation checks on each portal field that check's your lock field and denies changes if the lock is true. You can also use a script trigger to check the lock status when this is the bottom blank row (Portal's key field will be empty) and denies entry if the lock condition is true."

                    So, I did the following:


                    On object Modify (for the invoice ID entry field that generates the lookups):

                    If (Showday::Lock="0" and Workorders::Lock="0")

                    Show Custom Dialog ["ACHTUNG!"; "This day is locked."]

                    Revert Record/Request [] 

                    End If


                    Seems to work pretty well. The only issue is that it works whether or not the access privileges allow it to... I'm running on full access privileges and I can no longer edit the portal rows in 2011 (which sometimes I'd need to do from time to time for maintenance).

                    • 7. Re: Date Calculation for Locking Records

                      Use OnObjectEnter set on the portal, not the fields placed inside it.

                      Use Commit Record to bump the cursor back out of the field you entered to trip the trigger.

                      • 8. Re: Date Calculation for Locking Records

                        And check just the first Lock field, the one from the parent record. The related tables lock will be zero as it's empty at this point. As I've said earlier, you don't need lock fields, you can just use the calculation you've defined in them as your lock expression in manage Security.

                        • 9. Re: Date Calculation for Locking Records

                          Ok, I switched it to the portal. That works a lot better, but it still executes regardless of security settings. Not the biggest deal.

                          The custom dialog bumps the cursor out of the box after I click Ok. Is this acceptable or do Is till need Commit Record in the script?

                          • 10. Re: Date Calculation for Locking Records

                            Our posts crossed, read the one just before yours.