How do I reset a value daily?

[raisputin]

I have a table that has the following fields:

Stored Date = storing the datae when the record was created

loadNumber = the number of the call that was taken, e.g. the first call of the day is "1" the last call of the day is 'n'

What i am trying to do is make it so that the loadNumber resets itself and starts over at "1" at the beginning of each day.

I tried the following without success

Auto-Enter Serial Number and a calculation on that same section and then without the auto-enter number. The calculation was tried 3 different ways that made sense to me, but sadly didn't work. They are as follows:

Let([stDate=StoredDate; nowDate=Get(CurrentDate)];

If (nowDate ≠ stDate; LoadNumber=1;LoadNumber=LoadNumber+1))

Case(

StoredDate ≠ Get(CurrentDate);LoadNumber=1;LoadNumber=LoadNumber+1

)

Let(

[nowDate=Get(CurrentDate);loadnun=LoadNumber; stoDate=StoredDate];

Case (

nowDate ≠ StoredDate;LoadNumber="1";LoadNumber=LoadNumber+1

)

)

I also tried different variations of these and was unable to get it to work. When I changed the date it either A) just kept building on the previous number (I believe that was because of the Auto-Enter number) or it would tell me I had to revert the field because the data was invalid.

I also tried the above three things in the validation field, with and without a auto-enter number and again it didn't work.

So my idea was that I would look at the stored date, compare it to the current date and if it was different reset the loadNumber field to 1, if it was the same date, then the next entry should be loadNumber+1

Quite obviously I am doing this incorrectly though it made perfect sense to me so any help appreciated

Regards,

Greg

[Fenton]

I think it's a little simpler than what you had, though still a little tricky. This seems to work:

Count ( self_Date::Date_created ) + 1

You need a self-relationship on Date_created, which is an auto-enter of creation date. The tricky part is that it's plus 1, because when you create the first record for the date, the relationship count is 0, because of the internal timing of the calculation. It's always 1 behind. (I wish FileMaker would document the internal timing better.)

Uncheck the [ ] Do not evaluate if fields are empty, bottom left of the calculation dialog, or else you won't get the 1st one. Do not uncheck [x] Do not replace existing value, or else editing the date will retrigger the calculation. Though it's not reasonable to edit a creation date. But you don't need it anyway. It would screw up imports for one thing.

[raisputin]

I must have done it wrong, because it didn't work, same problem as the calcs that I tried.

Here is what I did...

I had the field "StoredDate" already, so I created tempDate and then made the self-relationship like you said and entered your calculation as shown. when I created a new record after changing the date it says "LoadNumber is defined to contain only specific Values. You must enter a valid value"

I took off the "Strict Numeric" requirement and changed the date and that let it start working again, but alas, when I cahgned the date, it just continued to count from where it left off and did not reset Load NUmber field to 1 as I wished.

Like I said, I probably did it incorrectly as I am a newbie to FM, but well, I am just not sure what I did wrong.

Regards,

Greg

[Vaughan]

I'd be tempted to make a table, where each record has a field with the date in it. One record per date.

When a new loadNumber is being made, the script checks the current date and sees whether a date record exists for it. If it does not exist, the script creates a date record for the current date and resets the loadnumber serial number to 1. If the date record exists it increments the serial number.

[Fenton]

Well, you didn't do it the way I did it, and it is kind of touchy to set up. I have only 1 date field, 1 self-relationship, and the count field; no temporary field. It is an auto-enter by calculation, in the Options.

It certainly cannot be validated as Unique, not by itself (and it's kind of a separate issue). It could if you included BOTH the Date and the Count in a self-relationship (separate relationship from the one I had; this new one would be validation).

Vaughn's method would be good also. You would need a button to create a new record. It's much the same relational logic, but using a separate table. The 1 record per date table may be useful for viewing also, if you're really interested in seeing counts/records per date in a browse mode portal.

Count_by_Date.fp7.zip

[LaRetta]

Why don't you just count the number of loads per day? Why does it have to be a static 'serial-type' number? Because I don't believe it can be guaranteed to work reliably in multi-user anyway. Does the load number need meaning? Doesn't it just mean the order (and number) of calls?

If you view only one day (either as a found set or a relationship), then the first record (sorted by natural sort state of creation order) would be Load #1 and so forth - using only record number!

It seems like a lot to go through to produce an unguaranteed result ... and adding meaning to a serial ... this is just my 'quite-frequently-wrong' opinion.

[Fenton]

I would agree with LaRetta. Just use a dynamic relational count (same self-date relationship). These stored "counting" fields are simply not 100% reliable, and very seldom even necessary (though sometimes they are). In any case, for serious work during scripts, when it really matters, use the dynamic relational count. It will always be correct, even if somehow the fixed numbers are not. Yeah, it'll take a tiny bit longer (if you create thousands of records per day -); but it's worth it.

[raisputin]

I described it weird, I am sorry, yes the load number means stuff, I am just loathe to give away too much info, because this *could* if it is working for me in my (hopefully) upcoming business, I may want to sell it.

LoadNumber is a number that specifies the loadNumber LOL

It must be unique for the day that it is done, and there can be other criteria, hard to explain without knowing the business. Once it is more complete I will upload it so people can take a look....I just don't know how to explain it.

[Vaughan]

Fenton wrote: "Vaughn's method would be good also. You would need a button to create a new record."

I'd use custom menus to make the new record creation completely seamless to the user. All existing menus, toolbars and keyboard shortuts would all work. I love custom menus.

Raisputin wrote: "I am just loathe to give away too much info, because this *could* if it is working for me in my (hopefully) upcoming business, I may want to sell it."

Let me see if I've got the picture: you're happy for others to help you out, but not happy to give the helpers too much information in case we steal your opportunity. Is that right?