4 Replies Latest reply on Apr 21, 2013 9:14 AM by RalphLearmont

    Best way to accomplish...


      FM Server 12 Adv

      FM Pro 12 Adv


      I have a table where all of the data is pulled in via an ODBC import script nightly. Every time the script is run it updates existing records and adds new records. The users do not create any records and they cannot modify any of the imported fields. There are 12 fields that need to be modifiable for the users. For those 12 fields I created duplicate fields and named them v_DuplicateName. On the layout I stacked the two fields and using conditional formatting hide or show the modifiable field.


      Now that I have that, I created another field called Verified (calculation) and on the layout I use a checkbox. The calculation is a Case function that if one of the fields above is modified it adds a check in the checkbox. So far everything works.


      I need to make the field 'Verified' manually modifiable. So that if I look at the record and everything is ok I can check the box. Or if I change one of the 12 fields it automatically adds a check. Looking for ideas on how to acomplish this. Thanks!

        • 1. Re: Best way to accomplish...

          The key to this is how do you define that it has been modified? The problem is of course that you cannot control any auto-entered modification dates or times so somehow that process needs to have some manual components to it... especially if those dates are in the tables concerned. It is therefore something I would do from the perspective of another table where you can sit the two tables for comparison side by side. After you have verified the mod date on the local info should be pasted to the imported mod date so they match and disappear from your view.


          Hope that makes sense


          - Lyndsay

          • 2. Re: Best way to accomplish...

            You lost me Lyndsay. I uploaded a demo of what I am doing to my original post. Not sure if I was to wordy the first time. Basically what I want is if the user looks at the info and all is ok then they can manually check the verified checkbox. Or if something needs to change they can select it from the drop down and once they do it auto checks the checkbox. I thought about using two verified fields stacked and conditionally formatted as the others are but then in list view it would make it difficult to sort by those verified.

            • 3. Re: Best way to accomplish...

              One way I know that works is a bit clumsy, the other two ways you should test.


              A bit clumsy but working

              1. Make an extra number field called something like 'verifiedByHand';

              2. Expand your verifying Case statement so that it is true when verifiedByHand is true. This must be the first part of the case-statement:   Case( verifiedByHand = 1 ; 1 ; (follows the original calculation))

              3. Stack this field underneath your calculated field and make the calculated field explicitly non-enterable.


              From your description I do not know at what time the check-box should change - during import, or during day-time, when users modify the database.


              Other ways are:

              1. Make the verification field a normal number field, and run a script after the import has been finished, that sets the field according to your calculation. Now you can change the field in the normal way by clicking on it. This will work, but it's slower.

              2. Change the verification field into a normal number field and put your calculation in its auto-enter calculation. You have to run a test if this calculation is triggered by the import. It probably is. If so, this is the cleanest solution.


              3. If users modify the fields during day-time, and you want the check-box to be set whenever that happens, you can add a 'onObjectValidate' script-trigger to the user-modifiable fields. This trigger triggers only when the field has been modified, not when a user enters and exits without doing something. It should trigger a simple script that sets the verification-field.


              Hope this helps.




              Martin Spanjaard

              Trias Digitaal


              • 4. Re: Best way to accomplish...

                I had a look at your demo file.


                You are almost there...   All you need do is alter the definition of your field.


                Redefine the "Verified" field making it a Text field; then go to Options > Auto-Enter, and tick the box where it says "Calculated value".  You can use the calculation you have already defined.  It works OK.


                The last thing is to have the option: " Do not relace existing value...." NOT ticked.