Slection in value list to determine when a new record number happens

[Guest karend440]

Hi all,

I am wondering if this can be done. With the database I am working on, involves input from 3 branch locations, thus involving a lot of employees. The controllers of this want to be able to "Close" a record once it's been resolved which I have set via a drop down menu item. (Closed vs Open). Is there a way to have the record/serial number trigger to increase once the record is selected is closed? I currently have the record number which is in a format of NM-(Static)08(year)001(number), so the 001 would be the only one that changes, then of course the year when that happens.

 

I have the script working for the record number changing when a new record is committed, but they don't want to assign the number until it's reached the "Closed" state, selected by certain people. What they don't want are records using sequential numbers that are bogus and would be deleted, thus having gaps in the numbers. I am not sure if this can be done via a selection of a drop-down value item, or do I have to just make this a manual button to select at the time the "Closed" event happens.

 

Any ideas on the best way to handle this? Haven't had much luck finding anything for this, so I thought I'd ask!

Thanks!..

Karen

[touchMe]

Does your numbering restart from 001 in 09?

When a record is 'closed' can it be 'reopened' again at any stage, for any reason? - or would that be a new record?

Whatcha mean by 'closed'? no more interest in the record itself (no more data will be added/changed), only used for extract data for reports etc.,?

Who ultimately can 'close' a record? - is this closing based on a sufficiency of extant data, or simply a 'manual' decision coming from human review?

 

The record number bit is easy enough, some fiddlework to get the reset to zero and the year is a calc based on last-2-digits of then current date.

 

If you require xyz data entered, and upon its' entry a record is deemed to be resolved and closed, that can be done... in fact perhaps it might be easier to automate your open/closed indicator based upon such a specification

 

Or are you looking for a close record routine which does a whole bunch more that simply record the word... closed?

[Guest karend440]

HI,

 

No, the numbering can continue in 09, that's not a problem. The definition of closed, is just the word "Closed" currently it's on a drop down list of an "Open or "Closed" status. It does nothing else. The only ones able to access this value list is an Admin person. (Will not display on the employee layouts). The close part is just a simple word, and does nothing else. I just want to see if I can trigger the word it's self to inititate a new serial/record number.

Basically what it's for is a safety record for the employee, and once the record has been addressed, they want to put it into a close status. You make a good point, if it's closed once and reopened as "Open" the record number should not increase because it's already been assigned one. Hopefully that wouldn't happen, but with human error/keying, just want to be ready for it.

 

Hope that helps explain it? If not, please let me know!

Thanks so much!

Karen

[touchMe]

The problem with using the word itself to (calculate) assign/change etc., a serial record number is that this condition becomes a circular and would give FMP a hangover, so I am going to suggest that you think about following the script method (rather than a calculation)

 

Visit your FMP help file and read-up about the 'set next serial value' script step, combine this with a bit of calculation to produce your tag-on stuff for year and whatever else, and the end result will be a closedFile # (looking exactly the way you want). This should NOT be treated as a record number (it is a closed record number) since it is only applied upon satisfaction of conditions, in this case... file closed

 

Couple of suggestions... you might want to add a closed file date field as an element of your script, a date field showing when the file was actually closed, it is from this date that you can create your 08 or 09 or 10 year prefix. Or is that element based on year file opened? (in which case you would take the year calc element from creationDate)

[touchMe]

I meant to add... doing it the above way with a [button] script, your open closed popup thing becomes purely a status indication (not the trigger) and it might be an idea to dissallow entry to the field and get rid of the popup bit, after all it will be a calc; if date of completion is(empty) then... otherwise

 

You could build this calculation to provide a bit more function than displaying just open or closed. For instance... if you have say 10 fields of information you require entered before a file can be closed this could be indicated in the status field (nor just open or closed), if you are awaiting the results indicating an individual has received training or a been given an employee handout or whatever this too could be displayed within the status

 

Indeed, you could use [is if and or case] statements to build a truly comprehensive status on each and every record, this could be very helpful for those charged with collecting, entering and checking the records

 

Another suggestion could be to add conditional color or tone to a list line entry (text or background) to signify closed or status, quick visual reminders and prompts can be very powerful

[Guest karend440]

Thanks a lot for the help! The button suggestion triggered a sort of fix. Instead of trying to involve the Open and Closed options, I made a script to do a pattern count on "Closed"..and sum it. With that total number, I used it in a script for the button with the record number format I needed, and added 1 to it, so it increments. It works, even if one is re-Opened per say, it will remove the existing record number and replace it with a new one, not a big deal there.

 

Thanks again for your help, much appreciated! smiley-smile