Jump to content

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


This topic is 2916 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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. 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This topic is 2916 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.