Help with auto change of value in list when calculation total changes

[FooGirl]

I'm stuck in completing my database and am in need of some instructional guidance.

I have a value list field,"Status Code" that contains two values: "AP" and "NAP". Upon record creation "NAP" is auto-entered. (This part I have working properly.)

I set up permissions to restrict access only to the "Status Code" field. There are two privilege sets; one for "User" and one for "Admin". The "Status Code" can be changed to "AP" by an "Admin" only, but still allows a "User" to create new records and have access to the field via find, sort, print, etc. (As far as I can tell, this part is also working fine.)

What I need help in understanding how to accomplish is this:

How do I get the "Status Code" field to automatically revert to "NAP" any time the dollar amount changes within the calculation field called "Total_AllFundTypeEst"?

I also need to be sure if a "User" is logged in and makes a change to one of the fields calculated by "Total_AllFundTypeEst" that the automated "Status Code" change won't be restricted.

Hopefully that makes sense...I'm using v10 Adv. Thanks in advance for any assistance offered.

[comment]

What exactly is the calculation in Total_AllFundTypeEst?

[FooGirl]

Total_AllFundTypeEst = Total_InitialEst + Total_Change1 + Total_Change2 + Total_Change3 + Total_Change4

Each "Total" field being added together in the above calculation are also the same type of calculation; simple addition of several numeric fields.

Thanks again

[comment]

Well, you could add a calculated status field that would be based on the other two fields.

However, I suspect there is something fundamentally wrong with your approach: as a general rule, you should not have numbered fields like Total_Change1. Total_Change2. etc. - instead, these should be individual records in a related table.

I am also not sure what you mean by "any time the dollar amount changes". Changes relatively to what?

A better description of what you are trying to achieve would be useful.

[FooGirl]

Here is a screen shot of my layout with sample data. If you look at the third section, "Est Obligations", you'll see that these are data entry fields that will be sporadically edited by the "User", each of which is a numeric field. Then there is one column and one row that contain only calculation fields. I don't think at any time will all of the "Change" fields be populated, and the "After Grant End" column isn't taken into consideration with regard to changing the "Status Code". The column which shows the total as $10,550 is the "Total_AllFundTypeEst" calculation field.

By "any time the dollar amount changes" I mean any time a "User" enters an amount into one or more of the "Initial Est" or "Change" fields *after* initially creating the record. Once it recalculates the total in "Total_AllFundTypeEst" I'm then trying to figure out a way to change the status back to "NAP" if, in the interim, it has been changed to "AP" by an "Admin".

Thanks again for your help.

[comment]

Presumably, all the "Initial Est" and "Change" fields will be empty after creating a new record. So any value in those fields constitutes a "change". Unless you mean a change of the initial values, entered immediately after the record was created.

Perhaps you could attach a OnObjectModify script trigger to all those number fields, and have the script run with full privileges. I am not sure that's a good solution - because I am not sure what's your purpose here.

[FooGirl]

"Presumably, all the "Initial Est" and "Change" fields will be empty after creating a new record."

Yes, they will. And "Status Code" is auto-filled with "NAP"

So any value in those fields constitutes a "change".

Yes, it does. So, any "change" at this stage is already at the desired "Status Code" of "NAP"

Every time a "User" enters an amount into any of the "Initial Est" or "Change" fields, that client record is considered "NAP" (not approved) until it is reviewed by an "Admin", who will then change it to "AP" if approved. After which, a "User" may enter new data into additional "Initial Est" fields that weren't previously completed upon record creation, or into one or more of the "Change" fields. At that time, the client record should revert back to "NAP" so that it can again be reviewed by an "Admin" and changed to "AP" if approved.

I hope I'm making this clearer and truly appreciate your patience and help.

[comment]

I would do it this way: define two fields, LastModified and LastApproved. Set LastModified to auto-enter modification date (or timestamp). Have the Admins approve the record by entering the date (or timestamp) into LastApproved. Calculate the status by comparing the two fields.

Note: if you wish, you can have LastModified respond only to changes in selected fields by auto entering a calculated value (replacing existing value), e.g.:

Let (

trigger = SomeField & AnotherField & ThirdField ...

;

Get (CurrentDate)

)

The fields listed in the trigger parameter must be the fields modified by the user - not calculation fields.

[FooGirl]

Thanks so much for your help. That makes perfect sense. I will give it a try tomorrow and will post if I get stuck. I'm only a novice with script writing and calculations, but I think I may be able to get there from here. Very appreciative of your much needed assistance.

[FooGirl]

Calculate the status by comparing the two fields.

Okay, I clearly don't have enough experience with calculations. If you have the patience for it, could you please help me with the steps I need to go through to accomplish this? Thanks again

[comment]

Try =

LastApproved > LastModified

Set the result type to Number. You can format the field to display as "AP" or "NAP" (under Format > Number… > Format as Boolean).

[FooGirl]

ok, so I have the comparison working properly now (I had was having a blonde moment and left the result type as text..duh!)

Note: if you wish, you can have LastModified respond only to changes in selected fields by auto entering a calculated value (replacing existing value), e.g.:

Let (trigger = SomeField & AnotherField & ThirdField ...;Get (CurrentDate))>

Now I *need* to limit the "Date_Modified" to specific fields because when I approve the record by selecting the "Date_Approved" it counts the entry as a "change" and flips back to "NAP"

My main experience with FM is in older versions that don't use script triggers. Where does the code above go? I is it applied to the "Date_Modified" field or to each of the fields I need the mod date to be affected by. I'm totally lost here and need some step by step help with this part

[comment]

The code goes into the "Calculated value:" window of Auto-Enter Options for the Date_Modified field.

BTW, there is no script trigger here. The "trigger" is just a variable name I used - it will work just as well with any other name, e.g.:

Let (

watchFields = FirstField & SecondField & ThirdField

;

Get (CurrentDate)

)

[FooGirl]

Okay, after some tweaking I finally got this to work properly. I was putting the code in the proper place, but the problem I was having was that because it was in Date format, changes and approvals couldn't be made on the same calendar day and when a new record was created it kept starting out with an approved status because there was no date to be compared in the "Date_Approved" field.

Since users aren't accustomed to entering date and time into a field, and the drop down calendar kept the timestamp at 12am on the current date, I created a scripted button that allows only an Admin to approve the record by inserting a timestamp into the "Date_Approved" field. It works perfectly now when limiting the "Date_Modified" to specific fields. ********THANK YOU!!!********

Now, the only issue I'm having is that the date displays truncated (due to field size limitation) with no leading zeros and 4-digit year due to the Date fields now being Timestamps. I know that I can force Text and Number fields to display in a particular format with a calculation, but what about Timestamps? Is there a way to force the Timestamp to display as "07/27/11" instead of "7/28/2011 12:00:00 AM"? My reports don't look very clean anymore and I'm out of room, print-wise, to extend the size of the date fields.

Again, incredibly grateful for all of your help and truly appreciate you taking the time to assist me.

[comment]

I created a scripted button that allows only an Admin to approve the record by inserting a timestamp into the "Date_Approved" field.

That's what you should have done. Sorry for not mentioning this earlier - it seemed obvious to me. BTW, you are - hopefully - setting the field, not inserting into it.

Is there a way to force the Timestamp to display as "07/27/11" instead of "7/28/2011 12:00:00 AM"?

LOL, you didn't even have this field before - now you want it to look pretty?

Anyway, there is no way for a timestamp field to display only the date; but you can format the date part as MM/DD/YY and the time part as say HH:MM (or anything else, except 'as entered'). Pick the 24-hour notation, type as many spaces as possible in the cell next to it and select [leading] from the pop-up. This will push the time portion to the right, so a narrow field won't show it.

[FooGirl]

Okay, that's what I thought. I only realized the date display was an issue in my report view because I also had to change the Date_Modified to a Timestamp which is a column on the report next to three other fields that are Date fields. And yes, I'm setting, not inserting:-)

If I could, I'd give you a giant plate of brownies. Thanks for bearing with me...I'd still be stuck if it weren't for your help, which I appreciate immensely!

[comment]

If I could, I'd give you a giant plate of brownies.

Mmmmm...