How to refresh a fieldvalue

[MacFM]

I've come across a function that must be in FM but I can't seem to find it.

In a layout I have three fields for the creation of an unique order number an dthe fields are;

1 the year field 2 digits

2 the week field 2 digits

3 the order number

What I need to get working is that the year field gets filled in automatically only with the last two digits of the year. The week field has a brother with which I want to reset the number field (3) after every week, so I made a check field with the 'WeekofYear' function but this field needs to be refreshed every now and then so that when the week changes the number field gets set to zero and starts counting again from 01. Can anyone point me in the right direction because I can't find anything like refresh ore something like that.

23 views and no idea's, is the message text not clear?

I need the help desperately so any hints ore referrals are welcome.

FileMaker Version: 6

Platform: Mac OS X Panther

[Damocles]

MacFM,

Do you want the unique order number to be automatically created and stored in its own field? Will this happen when you create a new record or when you run a script? Is there a reason you want to have the year, the week, and the order number in separate fields, or are they just being stored in order to create the unique order number?

Paul

[Damocles]

This works fine, although it is hard to check for weeks other than today. I am horrible at detailed explanations, but here goes:

UniqueID is an aggregation of year&week&order#

Year and week were easy enough. I added an If statement to make sure that week has the appropriate number of digits. (if it is week 2, the field is set to "02")

The counter to tell how many orders happened each week is set by a self-join relationship yearandweek::yearandweek. If there are no other records with the same yearandweek, order# resets to "1". If there are other records with the yearandweek the same(if this is not the first order this week), order# counts those records and automatically fills in the next higher number.

All the fields automatically fill in, and UniqueID is not modifiable once you create the record.

Hope that's what you were after....

Paul

Order Number.zip

[MacFM]

First of I'm not a FM crack, I did some simple DB's with FM 5 pro a few years back. So I'm not a novice but an expert is also far from the truth.

Damocles said:

Do you want the unique order number to be automatically created and stored in its own field?

yes that's my goal

Damocles said:

Will this happen when you create a new record or when you run a script?

When a new record is created

Damocles said:

Is there a reason you want to have the year, the week, and the order number in separate fields, or are they just being stored in order to create the unique order number?

I have no special reason but my main reason is my personal logicthoughts.

If I want to generate a number containing three different parts it seemed easier to me to seperate them in order to make calculations etc.

The number generated is just made up of elements to easily track the creation dates of the orders.

SO the first two digits represent the year of creation the second set the week and the last digits count how many orders were created that week.

The simplest way in my eyes is to refresh the weekofyear valeu each time you create an new record ore to let FM check it with the systemcalendar every hour ore so. If I get this to work I will only have to make a script that checks some background fieldvaleus in which I duplicate the current week and one field that has a deduction of 1 than the IF statement is easy, and than reset the fields after the week turnaround.

I must confess that for the most part I don't understand your explanation but I'll study it to try and figure it out.

I would like to thank you for the help you have provided so far

[Damocles]

I was playing with a "solution" and discovered a big hole in this attachment. If you change the yearandweek field on any records, or if you delete any records from the current week, the "Count related records" no longer works. So, this works great (see attachment for more explanation) as long as you don't delete any records.

I'll look for a more solid solution if nobody else has one. Sorry for the inconvenience.

Paul

Order# with more explanation.zip

[Himitsu]

I kind of have a similar problem. I have a field that contains a check box for homework my students have bought. I have tied that in with their brothers/sisters relationship (a field for thier bro/sis that looks up student ID # and adds the name ect) so if student01 has a brother called student02, when I click on the broID field, it pulls up a pop-up list with all the sudents listed. I select the ID of student02 and it auto enters name, ect... but lets say student01 buys homeworkA and I check the box. I want it to show in student02 checkbox too. So I made the homework field a look up with the relationship with the bro/sis relationship and I set the validation to lookup always. I do understand that it will update when I re-enter the bro/sis id in the field, but I was wondering how could I make it to show/update after I click off the homework field?

[MacFM]

Damocles said:

I was playing with a "solution" and discovered a big hole in this attachment. If you change the yearandweek field on any records, or if you delete any records from the current week, the "Count related records" no longer works. So, this works great (see attachment for more explanation) as long as you don't delete any records.

I'll look for a more solid solution if nobody else has one. Sorry for the inconvenience.

Paul

Thanks a million Paul!

I'll start testing it this evening and report back

[Damocles]

Forgot to mention the consequences: Once the "Count related records" gets messed up during a week, the "UniqueID" calculation fails, causing a UniqueID to be generated more than once and corrupting the nature of any relationships that rely on that no-longer-unique number. Like I said, it's a BIG hole.

You're welcome. FM "problems" are fun to work with, just to stay mentally challenged.

Paul

[Damocles]

Do you turn your computer off every day (or at least, on weekends?) I've come to a mental block trying to define the field to auto-enter the appropriate value, and would like to script part of the process. I prefer not to be required to remember to run the script at any give time. So, if you periodically turn off your computer (or at least close FileMaker), I will try using a solution that relies on a startup script.

Let me know... There is a simple solution that I just don't see. This is driving me nuts!

Paul

[Damocles]

Okay, nevermind. There is a solution that was posted recently by [color:"green"]-Queue-. Search the forum for "secuencial field" (include the quotes)in the subject line. He describes a solution that works well. The only thing I could find is that if you delete the most recent record, it re-issues that number, so if you are interested in how many orders/requests you actually handle during the week, this won't capture that.

I've attached a modification of his solution, updated with your particular Year/Week request. Wish this were my idea, but hey, a working solution is good, regardless of the source.

Good luck!

Paul

YearWeekOrderNum.zip

[Damocles]

Can you send a screenshot? I'm not sure I understand the question.....

paul