I should also state I am using this within a script attached to a specific field. When you go into Manage and then Database you get to the script by selecting the field and then selecting Options. Below is the full script the Set Variable function is being inserted into (3rd line down):
/* Community College Partnerships */
/* Hours of operations reported are 8:00 AM to 5:00 PM Mon - Fri */
Set Variable [$BUStart; Value: Time(8:00:00)];
/* Start Case */
/* 1st Check - Check if the Outage Start and End dates are the same. */
GetAsDate(Outage Start) = GetAsDate(Outage End);
/* Check if the outage end time falls within the impact window*/
/* as reported by the department. */
/* Start of Let */
/* If outage end time falls within the reported impact window */
/* then set tmp end time variable equal to the actual outage end time. */
$tmp_outage_end = If(GetAsTime(Outage End) ≥ Time(8;0;0) and
GetAsTime(Outage End) ≤ Time(17;0;0); Outage End;
/* If the Outage End time is later than the reported impact window */
/* then set the tmp end time variable equal to the impact window */
/* end time as reported by the department, not the actual outage end time. */
If(GetAsTime(Outage End) > Time(17;0;0);
/* If outage start time falls within the reported impact window */
/* then set tmp start time variable equal to the actual outage start time. */
$tmp_outage_start = If(GetAsTime(Outage Start) ≥ Time( 8;0;0 ) and
GetAsTime(Outage Start) ≤ Time( 17;0;0 ); Outage Start;
/* If the outage start time is earlier than the reported impact window */
/* then set the tmp start time variable equal to the impact window */
/* start time as reported by the department, not the actual outage start time. */
If(GetAsTime(Outage Start) < Time( 8;0;0 ); Timestamp(GetAsDate(Outage Start);
/* Calcualate the actual outage impact for this department */
$tmp_outage_end - $tmp_outage_start);
/* End of Let */
/* End First Check */
/* 2nd Check - Check if the outage start and end dates are different days but */
/* 24 hours or less in duration. */
/* End Case */
I am using FM Pro Advanced 14 and am trying to use the set variable function but am receiving an error which states "The specified filed cannot be found." and then highlights the Set Variable words.
Below is the statement. My goal is to set a constant variable equal to a certain time.
Set Variable [$BUStart; Value: Time(8:00:00)]
How are you entering in the Time function?
The Time function has three parameters separated by semicolons and not colons AND when entered as you have it would look something like "Time (8 ; 0 ; 0)" as the function has no concept of a double 0 (00) and would simply treat it as a "0"
So I suspect perhaps this has been entered in a way that the calculation engine believed your value is actually a field and not a function. If I type in "Time(8:00:00)" directly into the "Value" field I get the same error that you are seeing.
That is a good catch Steve. I have it entered correctly when testing it. I just made a typo inserting it back into my script for this post. Just to make sure I did retype it with the semicolons and still receive the same error.
Set Variable [$BUStart; Value: Time(8;0;0)]
you're likely to stumble on this, too:
and the best thing to do is anyway to define $timeBegin = Time(8;0;0) and $timeEnd = Time(17;0;0) at the beginning of the script, then just use them everywhere, instead of hardcoding. (Even better, have them as script parameters - just the 8 and the 17, maybe ?)
last but not least, CASE() is not a script step, so what you pasted is something else. A good example of overcommenting, for sure.
$tmp_outage_end = min (GetAsTime(Outage End); $timeEnd)
$tmp_outage_start = max (GetAsTime(Outage Begin); $timeBegin)
is probably almost all you need.
siplus, that is actually my main issue. i can't set any variables or constants at the beginning. It is erring when trying to set them. I am beginning to think that is not a valid function where I am trying to use it. That is not on the function list when I go into Specify Calculation for each field.
If I go and create a script from the Scripts Menu it is there.
What I am trying to solve is this. I have a table which is logging our outage times for our systems. We have multiple departments at our University with varying hours of operations. I am creating an outage impact field for each department which will automatically populate each departments impact time for each outage record. Because the departments have varying start and stop times I have created the script within each of their department impact fields within the table.
For example: We have an outage lasting from 6:00 AM to 10:00 AM. Dept A opens up at 7:00 AM and Dept B opens at 8:00 AM. Outage impact for Dept A would be 3 hours and the impact for Dept 8 would be 2 hours.
I will need to use the same outage start and outage end times which are input as the outage is worked and resolved. But the Department Times needs to be constant variables for each department's outage impact fields.
On one side you have departments, each with an ID and a open and close times.
On the other side you have outages, each with its starting and ending time.
Every time you create a new record in outages, you can create n records in a "Impact" table, with n being how many departments you have (many ways to get that, from Count() to ValueCount(ExecuteSQL())...
loop on department ID's and for each impact record, you calculate its relative time by considering that very department's opening hours and the outage window's begin and end times. As posted earlier, use min and max.
Thanks, siplus. I am looking at the min and max and re-thinking my approach for this. I do have to consider outages which occur over multiple days but only the working hours are counted. That is easy enough with an accumulator variable within the loop.
But, for the future, when I try to use the Set Variable function, the original question still is unanswered as to why it is not working, even when I try it by itself in the script. I would like my thinking validated that it cannot be used inside a script within field options.
Aaaaand can't create the accumulator variable because the Set Variable function doesn't work. I am going to create a separate table holding each departments hours as field values and read that in instead of trying to set them within the script. I will just use a hidden accumulator field within the table. This will get me away from trying to set variables within the script.
"I should also state I am using this within a script attached to a specific field. When you go into Manage and then Database you get to the script by selecting the field and then selecting Options"
The above is impossible. You cannot "attach a script to a specific field".
It would be helpful to make a clear distinction between scripts; and field definition calculations.
They are very different things and it is not at all clear what you are doing.
Are you in fact writing scripts?
Or are you creating a field calculation? And if it is a field calc, is it a calculated field or an auto-enter field?
It is generally considered poor practice to include $variables or $$variables in field calculations.
Sorry for the lengthy reply. I appreciate your questions.
It is a field calculation with the intent of calculating the impact based whether the impact occurred during their business hours.
The intent was to have a calculated field for each department which would determine that department's impact time based on the original start and end of the outage. Originally we were using the total outage time but the department responsible for the systems successfully, and correctly, argued the business units were not impacted when they were closed. Because of this we have to account for weekends and overnight hours. Of course some departments are opened on weekends and some are not. Some open at 8:00 am while others open at 7:00. We have departments which close from 5:00 PM to 8:00 PM and various times in between.
The case statement within the calculation is used to determine if it is a same day outage, an outage which is less than 24 hours but is across two calendar days or greater than 24 hours. So, if it is a 3 day outage then did it occur Mon, Tues, Wed or Fri, Sat, Sun. Did it start overnight so now the start time has to be adjusted to each Department's "Open" time. If an outage spans four days and one department works weekends but another doesn't then their impacts will be different. Same for those department opened different hours.
I am looking at what siplus said and setting up a small "departments" table as a test. The outage table will read through the departments table and use the open/close fields (and workday fields) to update a child table attached to the outage table with the outage impact for each department. This will be done using an actual script and not a calculated field.