Altering a data field when the user specifies a specific date range

[BugsyOhar]

We are currently running Filemaker Server vs. 11 with about 20 clients. The server is currently hosting a Purchasing Database where we are trying to determine the best method for altering a data field when the user specifies a specific date range, creates a new record or duplicates a specific record. For an example, if the user selects a date in the range between 2/1 - 2/28, a corresponding fiscal period number would appear in the PERIOD field. This would also be true if the user changed the PERIOD number, the date field would then change to indicate the beginning date of each corresponding month. Any and all suggestions would be greatly appreciated. 

 

[comment]

There are two ways you could handle this: auto-entered calculated values, or script triggers. The latter would be probably easier to set up, but it will work only on the layout/s where you did set it up.

[BugsyOhar]

Thanks for the quick reply. Would you by any chance have any examples of script triggers you are referring too?

[comment]

For example, attach an OnObjectModify script trigger to the Period field, and use the trigerred script to:

Set Field [ YourTable::SomeDateField ; <<calculation here>> ]

Do the same and opposite thing for the date field. It's difficult to be specific about the calculations, since you have given us no details regarding how to translate between the two.

[BugsyOhar]

We created a simple purchasing database that contains a DATE field and a PERIOD field. Whenever a new record has been generated, we would like a specified period number to appear in the PERIOD field. For instance if today's date is 11/3/14, the PERIOD field would show the number 10. If however the date field was changed to a date in December, this number would change to Period 11 and so on. This would also hold true if the number in the period field was ever changed. In this case the date would then change to correspond to a specific period number. For instance, if the period number was changed to the number 9, the date field would then change to show the beginning of that month. Which in this case would be 10/1/14. If the period number was changed to the number 10, the date would then change to 11/1/14. Hope that helps!

[comment]

Well, then set the PERIOD field to =

Mod ( Month ( DATE ) - 2 ; 12 ) + 1

and the DATE field to =

Date ( PERIOD + 1 ; 1 ; Year ( Get ( CurrentDate ) ) )

Note that this not the most robust arrangement: The PERIOD field has no year, and the result of converting it to a date depends on the current year at the time of conversion.

[BugsyOhar]

Hello again!

 

Thank you for your help in getting this project off the ground. I wanted to report that a good portion of the database is up and running with a few additional tweaks still needed. The calculations you supplied me are working out fine, however we are trying to automate the process? If after a new record has been generated the user was to change the contents of a field, is it possible to change the contents of another field automatically? For instance, if the date changes from 12/2/14 to 2/2/14 is there a way to change the PERIOD field to a 1 by simply tabbing out of the field?

 

Thanks in advance!

[comment]

If after a new record has been generated the user was to change the contents of a field, is it possible to change the contents of another field automatically? For instance, if the date changes from 12/2/14 to 2/2/14 is there a way to change the PERIOD field to a 1 by simply tabbing out of the field?

 

I think I already answered this in my first post above.

[BugsyOhar]

In order to automatically update and display the date and period fields, I created a simple script that when selected makes the necessary changes. It's a workaround, but will work for the moment. One last question, is it possible to show a "0" zero in front of period numbers 0 through 9 in the period field?

[comment]

One last question, is it possible to show a "0" zero in front of period numbers 0 through 9 in the period field?

 

Yes (provided the Period field is a Text field). Use =

SerialIncrement ( "00" ; Mod ( Month ( DATE ) - 2 ; 12 ) + 1 )