auto appending calculation

[ittsa]

I've been working on this a while, and I'm completely stuck.

 

I work at a television station, and we're creating a new database. I'm trying to have the Episode Key (used as the filename and for unique identification purposes) be autogenerated by the database to eliminate errors.

 

Here's how it is supposed to work.

There is a single character that tells what the category is, then 2 characters that identify the show, then a 6 digit date of production, and if that all is the same, it is appended with alpha characters until it is unique. We use a b c at the end because it otherwise is immediately following a number.

A valid name would be sxl022613

and a second episode of the same show produced on the same date would be sxl022613a

 

I have been able to calculate the episode key, but not the appension if it is not unique. Here is what I have. Three fields that are referenced are CategoryCC, ShowCC, ProductionDate

 

CategoryCC&  
ShowCC&
Case ( Month(ProductionDate)<10 ; "0"&Month(ProductionDate); Month(ProductionDate) = 10; Month(ProductionDate) ) &  
Case ( Day(ProductionDate)<10 ; "0"&Day(ProductionDate); Day(ProductionDate) = 10; Day(ProductionDate) ) &
Right(GetAsText(Year(ProductionDate));2)

 

I also need to have this calculation be constantly updated, so if the date of something changes, the episode key changes, and could result in an appension. But if there were others on the original date that were appended, that needs to be kept. In other words, only re-evaluate the episode key if one of the fields referenced is changed.

 

I currently have this field set up as a text field with auto-enter calculation replaces existing value, always validate, unique. Is that the best way to do this, or should the field be a calculation field?

[doughemi]

I don't think it can be done as a calc because you must look at a number of other records to determine if a match exists.

 

However, it can be done with a script.  See the attached sample.

 

 

shows.fp7.zip

 

Edit:  I just noticed your requirement to change episode IDs.  You may want to rethink that because you will then have no way of tracking episode change history. I think you need a Shooting table with a record for each shooting of an episode, related to the Show table

[ittsa]

Yeah, I see your point about the changing ids. I will have to bring that up. They have always changed it in the past, but maybe we should reconsider that. We do have a production table related to the episode table that should track that. since we do have instances where a single production ends up with multiple episodes, and vice versa.

 

Your script does work great. When and how would it run though in order to have it automatically generate the id?

 

Edit: I finally was able to see your script when I opened it on my Mac in an older version, but it says there is a function missing. If...Else...Set Variable..Value:<function missing>  What did you have there?

[doughemi]

That was the Char() function, introduced in FM 10.  Your profile says you have FM 11-- you should have been able to open the sample file with that.

 

Set Variable($newSuffix; Value: Char(shows::lastChar + 1))

 

When and how would it run though in order to have it automatically generate the id?

 

The script as written is designed to create the new record and then populate the field.  It is launched with the New Show button. You could conceivably modify it to run with an OnObjectEnter script trigger attached to the ShowID field, but of course you would have to have the data from the other fields available.

[ittsa]

For whatever reason, since it was locked, I couldn't even see the script or field information for your sample in 11. I had to go to my older Mac running 9 to see the script itself. Then I figured out how to import it in 11.

 

I've been playing with it a bit to figure out running the script, and I'm running into a problem of adding it to the record currently being created, instead of a new one. Since it wouldn't be able to run until several fields are filled in on that record.

[doughemi]

The file isn't locked, unless it's an OS downloaded file security settings thing at your end.

 

What are you starting with?  A blank record?  A partially completed record?  Using the global fields for setting up the data required seems the easiest way to assemble the data in one operation (and the safest if it is a networked solution), and then dump them into a new record.  Explain how you want your work flow to happen.  (I didn't add the set field steps to enter the other data fields because it wasn't part of the original question.)

[ittsa]

Sorry for oversimplifying initially.

I'm not sure global fields would work in our situation. I haven't worked with them much, but I believe they would have the same content for the field regardless of record. The category, etc fields are used for other things, including scripts and names of programs. 

The general workflow is that a production or piece of external programming comes in, and information is entered into the database. I'm attaching a screenshot of the episode layout we have so far. The episode key is used mostly to name the file, so it's not needed until the rest of the information is filled out. But it requires the category, show title and production date to be filled in. I have now added a button to run the script, in part for testing, but my project leader has no problem with leaving it there for a while if need be.

The category also tells directors what graphics they are using, determines live statuses, when it should playback on the channel and what equipment is being used in the production (as opposed to the episode, which this is mostly about)

The show title not only is the title of the show (i.e. Glee or NCIS) but tells what hosts there are, where the production likely is being filmed, and becomes part of the lower third graphics in most cases.

The episode title is then the specific episode (i.e. The Wedding or The Finale).

 

I have altered the script trying to get it to work better in this exact application, and in our tables (there are many at this point) so I'll attach what that looks like now too. 

At this point, I do now have the a and b etc working (somehow it wasn't before for me, I might have screwed that up) but it always adds it to the last recorded committed. This is a networked database, that nearly everyone here is referencing constantly, there's no way to be sure that will always be the case. When I added the lastChar field to the layout just so I could see what it was writing where, I noticed that it's putting the code for which letter on the "original" one where there would be no letter. (I ended up switching to using the unicode values there in order to make the +1 work.) Since it's a hidden field, I don't think that matters at all. I also noticed that I need to make sure the record is committed before running the script, or it loses anything added and doesn't commit the record.

 

I had thought that it might be able to be auto-run on record commit, but that stalled, and failed. I figure that's because it needed the record committed first, but was trying to run it while committing the same record.

I'm pretty new to all this database stuff. I'm actually the graphic designer for the station. I have been learning and teaching myself as I've been working on this project, so please forgive me if I got something wrong. Thanks for your help!

 

calcepisodekey.pdf

[ittsa]

I have figured it out and it's now working. Thanks for the running start help!